Tenho tentado depurar uma consulta particularmente lenta que nunca é concluída (leva uma eternidade e eventualmente atinge o tempo limite) e descobri que tudo se resume à ORDER BY
afirmação: se estiver lá, nunca será concluída; se eu removê-la, ela retornará instantaneamente.
Minha suposição era que não havia índice nesse campo, mas descobri que existe um:
CREATE UNIQUE INDEX changes_pkey ON public.changes USING btree (counter)
No entanto, isso não parece fazer nenhuma diferença, então estou me perguntando qual poderia ser o motivo? Talvez seja porque é um "ÍNDICE ÚNICO" diferente dos outros índices desta tabela?
Confira abaixo as dúvidas:
Nunca complete:
SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
FROM "changes"
WHERE counter > -1
AND (type = 1 OR type = 3)
AND user_id = 'xxxxxxx'
ORDER BY "counter" ASC
LIMIT 200
Conclui instantaneamente:
SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
FROM "changes"
WHERE counter > -1
AND (type = 1 OR type = 3)
AND user_id = 'xxxxxxx'
LIMIT 200
Índices nessa tabela:
changes | changes_id_index | CREATE INDEX changes_id_index ON public.changes USING btree (id)
changes | changes_id_unique | CREATE UNIQUE INDEX changes_id_unique ON public.changes USING btree (id)
changes | changes_item_id_index | CREATE INDEX changes_item_id_index ON public.changes USING btree (item_id)
changes | changes_pkey | CREATE UNIQUE INDEX changes_pkey ON public.changes USING btree (counter)
changes | changes_user_id_index | CREATE INDEX changes_user_id_index ON public.changes USING btree (user_id)
postgres=> EXPLAIN SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
postgres-> FROM "changes"
postgres-> WHERE counter > -1
postgres-> AND (type = 1 OR type = 3)
postgres-> AND user_id = 'xxxxxxxx'
postgres-> ORDER BY "counter" ASC
postgres-> LIMIT 200;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Limit (cost=0.56..9206.44 rows=200 width=99)
-> Index Scan using changes_pkey on changes (cost=0.56..5746031.01 rows=124834 width=99)
Index Cond: (counter > '-1'::integer)
Filter: (((user_id)::text = 'xxxxxxxx'::text) AND ((type = 1) OR (type = 3)))
(4 rows)
* EXPLAIN para a consulta rápida:
postgres=> EXPLAIN SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
postgres-> FROM "changes"
postgres-> WHERE counter > -1
postgres-> AND (type = 1 OR type = 3)
postgres-> AND user_id = 'xxxxxxxx'
postgres-> LIMIT 200;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Limit (cost=0.56..1190.09 rows=200 width=99)
-> Index Scan using changes_user_id_index on changes (cost=0.56..742468.10 rows=124834 width=99)
Index Cond: ((user_id)::text = 'xxxxxxxx'::text)
Filter: ((counter > '-1'::integer) AND ((type = 1) OR (type = 3)))
(4 rows)
Alguma ideia de qual poderia ser o motivo dessa consulta lenta?
Presumivelmente, o user_id 'xxxxxxx' é deficiente em linhas com um valor baixo de "contador", portanto, quando ele percorre o índice do contador, ele precisa percorrer um longo caminho antes de encontrar 200 deles que satisfaçam 'xxxxxxx' (mais a outra condição). O sistema de estatísticas do PostgreSQL não tem como saber sobre tais interações.
Muito melhor seria um índice em
(user_id, counter)
. Dessa forma, ele pode ir direto para o user_id correto e depois escanear apenas eles, mas já em ordem.Melhor ainda seria um índice filtrado:
Mas isso está entrando na categoria "um índice por consulta", então eu preferiria a categoria mais geral, a menos que você realmente precise de desempenho extra.