Início / dba / Perguntas / 332186
Accepted
laurent
Asked: 2023-10-17 02:16:35 +0800 CST

Por que essa consulta do Postgres é tão lenta?

  • 772

Estou tentando depurar a consulta lenta abaixo, mas estou lutando para entender por que ela é lenta. Posso ver que tanto o plano quanto o subplano fazem uma varredura de índice, incluindo uma "varredura somente de índice" para o subplano, portanto ambos devem ser rápidos. No entanto, esta consulta específica leva 7 segundos.

Alguma ideia desta saída EXPLAIN onde pode estar o problema?

select "id", "item_id", "item_name", "type", "updated_time" from "changes"
where (
  ((type = 1 OR type = 3) AND user_id = 'USER_ID')
  or type = 2 AND item_id IN (SELECT item_id FROM user_items WHERE user_id = 'USER_ID')
) and "counter" > '35885954' order by "counter" asc limit 100;

 Limit  (cost=8409.70..8553.44 rows=100 width=101) (actual time=7514.730..7514.731 rows=0 loops=1)
   ->  Index Scan using changes_pkey on changes  (cost=8409.70..2387708.44 rows=1655325 width=101) (actual time=7514.728..7514.729 rows=0 loops=1)
         Index Cond: (counter > 35885954)
         Filter: ((((type = 1) OR (type = 3)) AND ((user_id)::text = 'USER_ID'::text)) OR ((type = 2) AND (hashed SubPlan 1)))
         Rows Removed by Filter: 11378536
         SubPlan 1
           ->  Index Only Scan using user_items_user_id_item_id_unique on user_items  (cost=0.56..8401.57 rows=3030 width=24) (actual time=0.085..3.011 rows=3589 loops=1)
                 Index Cond: (user_id = 'USER_ID'::text)
                 Heap Fetches: 2053
 Planning Time: 0.245 ms
 Execution Time: 7514.781 ms
(11 rows)
postgresql

2 respostas

  1. Ler 11.378.536 linhas não é magicamente rápido só porque você faz isso com um índice. E é aí que está o problema, ele tem que ler esse número de linhas.

    Não sabemos quais partes dessa complexa condição de filtro levam à falha da maioria das linhas, o que torna difícil propor otimizações com confiança. Talvez um índice (type, counter)ou (type, user_id)ajude. Ou você pode dividir a consulta em duas partes na condição OR e combiná-las com UNION.

    • 4
  2. Best Answer

    Divida a consulta ao longo do arquivo OR. As duas consultas resultantes são mutuamente exclusivas, então combine-as com UNION ALL:

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

    Apoie o primeiro SELECTcom este índice parcial de múltiplas colunas para torná-lo extremamente rápido:

    CREATE INDEX ON changes (user_id, counter) WHERE type IN (1, 3);

    Ver:

    A segunda SELECTé menos clara. Um índice com liderança counterdeve servir melhor enquanto LIMITfor pequeno e a participação item_iddo dado user_idnão for muito pequena.

    CREATE INDEX ON changes (counter, item_id) WHERE type = 2;

    Caso contrário, mudar as colunas de índice para (item_id, counter)pode ser melhor.

    Coloquei um separado LIMIT 100para o primeiro SELECT, que é logicamente redundante, mas pode produzir um plano de consulta melhor. O segundo LIMIT 100se aplica a toda a consulta. Pode até ajudar anexar um separado LIMIT 100ao segundo SELECTtambém (com um conjunto extra de parênteses). Não tenho certeza. Teste e relate o que funcionou melhor.

    • 1

relate perguntas