Início / dba / Perguntas / 333135
Accepted
laurent
Asked: 2023-11-16 00:32:36 +0800 CST

Por que o Postgres é tão lento para ordenar as 200 linhas que já estão ordenadas?

  • 772

Tenho duas consultas SQL relativamente complexas às quais associo usando um arquivo UNION ALL. Cada consulta individual é rápida e retorna instantaneamente. O problema é que, uma vez unidos, eles têm um desempenho terrivelmente ruim e muitas vezes expiram.

Esta é a consulta completa:

SELECT "id", "item_id", "item_name", "type", "updated_time", "counter" FROM (
    SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
    FROM "changes"
    WHERE counter > -1
    AND (type = 1 OR type = 3)
    AND user_id = 'USER_ID'
    ORDER BY "counter" ASC
    LIMIT 100
) as sub1
UNION ALL
SELECT "id", "item_id", "item_name", "type", "updated_time", "counter" FROM (
    SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
    FROM "changes"
    WHERE counter > -1
    AND type = 2
    AND item_id IN (SELECT item_id FROM user_items WHERE user_id = 'USER_ID')
    ORDER BY "counter" ASC
    LIMIT 100
) as sub2
ORDER BY counter ASC -- SLOW!!
LIMIT 100;

Depois de algumas pesquisas, descobri que o motivo da lentidão está no ORDER BY counter ASCfinal da declaração (não nas declarações internas - tudo bem). Se estiver lá, leva mais de um minuto e expira. Sem ele, ele retorna em 5ms.

Isso não faz sentido para mim, já que a essa altura temos um total de 200 linhas, então a ordenação deve ser muito rápida (especialmente porque as linhas já estão ordenadas!).

Tentei analisar as consultas, mas nada se destaca para mim:

  • Consulta completa (leva mais de 60 segundos)
 Limit  (cost=1.70..325537.14 rows=100 width=101)
   ->  Merge Append  (cost=1.70..651072.57 rows=200 width=101)
         Sort Key: changes.counter
         ->  Limit  (cost=0.56..106310.10 rows=100 width=101)
               ->  Index Scan using changes_pkey on changes  (cost=0.56..4162018.71 rows=3915 width=101)
                     Index Cond: (counter > '-1'::integer)
                     Filter: (((user_id)::text = 'USER_ID'::text) AND ((type = 1) OR (type = 3)))
         ->  Limit  (cost=1.12..544758.47 rows=100 width=101)
               ->  Nested Loop  (cost=1.12..11516171.34 rows=2114 width=101)
                     ->  Index Scan using changes_pkey on changes changes_1  (cost=0.56..3986383.73 rows=10843703 width=101)
                           Index Cond: (counter > '-1'::integer)
                           Filter: (type = 2)
                     ->  Index Only Scan using user_items_user_id_item_id_unique on user_items  (cost=0.56..0.69 rows=1 width=24)
                           Index Cond: ((user_id = 'USER_ID'::text) AND (item_id = (changes_1.item_id)::text))
  • Consulta completa sem último ORDER BY(leva 5 ms) :
 Limit  (cost=23934.97..180049.76 rows=100 width=101)
   ->  Append  (cost=23934.97..336164.56 rows=200 width=101)
         ->  Limit  (cost=23934.97..23935.22 rows=100 width=101)
               ->  Sort  (cost=23934.97..23944.76 rows=3915 width=101)
                     Sort Key: changes.counter
                     ->  Bitmap Heap Scan on changes  (cost=284.11..23785.34 rows=3915 width=101)
                           Recheck Cond: ((user_id)::text = 'USER_ID'::text)
                           Filter: ((counter > '-1'::integer) AND ((type = 1) OR (type = 3)))
                           ->  Bitmap Index Scan on changes_user_id_index  (cost=0.00..283.13 rows=6209 width=0)
                                 Index Cond: ((user_id)::text = 'USER_ID'::text)
         ->  Limit  (cost=312214.83..312226.33 rows=100 width=101)
               ->  Gather Merge  (cost=312214.83..312357.89 rows=1244 width=101)
                     Workers Planned: 1
                     ->  Sort  (cost=311214.82..311217.93 rows=1244 width=101)
                           Sort Key: changes_1.counter
                           ->  Nested Loop  (cost=148.64..311167.28 rows=1244 width=101)
                                 ->  Parallel Bitmap Heap Scan on user_items  (cost=148.07..11209.94 rows=1785 width=24)
                                       Recheck Cond: ((user_id)::text = 'USER_ID'::text)
                                       ->  Bitmap Index Scan on user_items_user_id_index  (cost=0.00..147.31 rows=3034 width=0)
                                             Index Cond: ((user_id)::text = 'USER_ID'::text)
                                 ->  Index Scan using changes_item_id_index on changes changes_1  (cost=0.56..167.91 rows=13 width=101)
                                       Index Cond: ((item_id)::text = (user_items.item_id)::text)
                                       Filter: ((counter > '-1'::integer) AND (type = 2))

Estou pensando que poderia simplesmente remover a instrução "ordenar por" e ordená-las eu mesmo no código, mas isso não parece muito claro.

Alguma ideia do que pode ser feito para melhorar isso?

postgresql

2 respostas

  1. Best Answer

    A classificação adicionada causa um plano de consulta completamente diferente, o que não deveria ocorrer e pode ser evitado com CTEs, se necessário.

    Faça o que fizer, simplifique a consulta primeiro. As subconsultas não servem para nada. Tudo que você precisa são parênteses em torno de cada um SELECTpara anexar ORDER BYe LIMITlocalmente:

    (  -- parentheses here
SELECT id, item_id, item_name, type, updated_time, counter
FROM   changes
WHERE  counter > -1
AND    type IN (1, 3)
AND    user_id = 'USER_ID'
ORDER  BY counter
LIMIT  100
)

UNION ALL
(  -- parentheses here
SELECT id, item_id, item_name, type, updated_time, counter
FROM   changes
WHERE  counter > -1
AND    type = 2
AND    item_id IN (SELECT item_id FROM user_items WHERE user_id = 'USER_ID')
ORDER  BY counter
LIMIT  100
)

ORDER  BY counter  -- SLOW!
LIMIT  100;

    Ver:

    Não tenho certeza se isso já ajuda.
    Se tudo mais falhar, materialize cada um SELECTem um CTE separado para impor planos de consulta separados para cada um:

    WITH cte1 AS MATERIALIZED (
   SELECT id, item_id, item_name, type, updated_time, counter
   FROM   changes
   WHERE  counter > -1
   AND    type IN (1, 3)
   AND    user_id = 'USER_ID'
   ORDER  BY counter
   LIMIT  100
   )
, cte2 AS MATERIALIZED (
   SELECT id, item_id, item_name, type, updated_time, counter
   FROM   changes
   WHERE  counter > -1
   AND    type = 2
   AND    item_id IN (SELECT item_id FROM user_items WHERE user_id = 'USER_ID')
   ORDER  BY counter
   LIMIT  100
   )
TABLE cte1
UNION ALL
TABLE cte2
ORDER  BY counter
LIMIT  100;

    Ou com o “hack OFFSET 0”, também se apresentando como barreira de otimização, mas sem materializar desnecessariamente resultados intermediários:

    (  -- parentheses here
SELECT id, item_id, item_name, type, updated_time, counter
FROM   changes
WHERE  counter > -1
AND    type IN (1, 3)
AND    user_id = 'USER_ID'
ORDER  BY counter
LIMIT  100
OFFSET 0  -- optimization barrier
)

UNION ALL
(  -- parentheses here
SELECT id, item_id, item_name, type, updated_time, counter
FROM   changes
WHERE  counter > -1
AND    type = 2
AND    item_id IN (SELECT item_id FROM user_items WHERE user_id = 'USER_ID')
ORDER  BY counter
LIMIT  100
OFFSET 0  -- optimization barrier
)

ORDER  BY counter  -- SLOW!
LIMIT  100;

    Mais trabalho forense pode ser feito com base no quadro completo (ainda faltante).

    • 1

  2. Esta parece ser apenas mais uma manifestação (mas peculiar) do clássico problema ORDER BY...LIMIT. Observe a falta de nós Sort no primeiro plano, então ele está contando com a varredura do índice para ler as linhas já na ordem desejada, e acha que isso vai ganhar porque consegue parar mais cedo, assim que o LIMIT for atingido. Mas, em vez disso, a maioria das linhas com valores baixos de "contador" falha nas condições FILTER ou talvez na condição de junção na segunda ramificação, o que significa que ela não para tão cedo quanto pensa.

    • 1

relate perguntas