Início / dba / Perguntas / 338536
Accepted
laurent
Asked: 2024-04-11 18:42:32 +0800 CST

Consulta extremamente lenta sempre que usar ORDER BY, mesmo quando um índice estiver presente

  • 772

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 BYafirmaçã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?

postgresql

1 respostas

  1. Best Answer

    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:

    create index on changes (user_id, counter) where type = 1 OR type = 3

    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.

    • 3

relate perguntas