Início / dba / Perguntas / 338582
Accepted
laurent
Asked: 2024-04-12 18:43:42 +0800 CST

Qual índice pode ser criado para otimizar esta consulta?

  • 772

Eu tenho a consulta SQL abaixo que é executada de forma extremamente lenta. Quanto a esta consulta , isso se deve à instrução "ORDER BY", pois o Postgres está varrendo a changestabela por "contador" que pode ter milhões de valores. A remoção da instrução "ORDER BY" torna a consulta mais rápida.

Para a outra consulta mencionada acima, otimizei-a criando um índice em dois campos. Para esta consulta, entretanto, não tenho certeza de qual índice seria o correto. Tentei com um índice ativado, (item_id, counter)mas não ajudou em nada e não sei o que mais poderia tentar. Alguma sugestão?

Consulta SQL lenta:

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 = 'xxxx')
ORDER BY "counter" ASC
LIMIT 200;

EXPLICAR (ANALISAR, BUFFERS, CONFIGURAÇÕES) resultado:

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1001.15..27628.99 rows=200 width=99) (actual time=98730.912..116273.818 rows=200 loops=1)
   Buffers: shared hit=78113369 read=3224064 dirtied=3
   I/O Timings: read=137436.119
   ->  Gather Merge  (cost=1001.15..10431526.45 rows=78343 width=99) (actual time=98730.911..116273.783 rows=200 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=78113369 read=3224064 dirtied=3
         I/O Timings: read=137436.119
         ->  Nested Loop  (cost=1.13..10421483.70 rows=32643 width=99) (actual time=98493.185..112919.559 rows=75 loops=3)
               Buffers: shared hit=78113369 read=3224064 dirtied=3
               I/O Timings: read=137436.119
               ->  Parallel Index Scan using changes_pkey on changes  (cost=0.56..5949383.56 rows=6197986 width=99) (actual time=1.076..42523.117 rows=4075591 loops=3)
                     Index Cond: (counter > '-1'::integer)
                     Filter: (type = 2)
                     Rows Removed by Filter: 10370914
                     Buffers: shared hit=18993521 read=2672415
                     I/O Timings: read=85551.814
               ->  Index Scan using user_items_item_id_index on user_items  (cost=0.56..0.72 rows=1 width=23) (actual time=0.017..0.017 rows=0 loops=12226772)
                     Index Cond: ((item_id)::text = (changes.item_id)::text)
                     Filter: ((user_id)::text = 'xxxx'::text)
                     Rows Removed by Filter: 1
                     Buffers: shared hit=59119848 read=551649 dirtied=3
                     I/O Timings: read=51884.305
 Settings: effective_cache_size = '16179496kB', jit = 'off', work_mem = '100000kB'
 Planning Time: 1.465 ms
 Execution Time: 116273.929 ms
(26 rows)

Índices:

"changes_pkey" PRIMARY KEY, btree (counter)
"changes_id_index" btree (id)
"changes_id_unique" UNIQUE CONSTRAINT, btree (id)
"changes_item_id_index" btree (item_id)
"changes_user_id_counter_index" btree (user_id, counter)
"changes_user_id_index" btree (user_id)
postgresql

3 respostas

  1. você deve reescrever sua consulta para

    SELECT "id", c."item_id", "item_name", "type", "updated_time", "counter"
FROM "changes" c JOIN (SELECT item_id FROM user_items WHERE user_id = 'xxxx') ui
ON c.item_id = ui.item_id
WHERE counter > -1
AND type = 2
ORDER BY "counter" ASC
LIMIT 200;

    Com os índices

      changes (type, item_id, counter) INCLUDE (id, item_name, updated_time)
  user_items (user_id)

    isso deve acelerar a consulta

    A junção geralmente é mais rápida, pois INa

    um índice combinado para alterações que inclua as três colunas na cláusula ON e WHERE deve aumentar apenas a velocidade.

    o mesmo vale para user_item onde o usuário também deve ter um índice se ainda não tiver um

    • 1
  2. Best Answer

    A causa do problema é a seguinte: O otimizador pensa que há linhas suficientes changesrelacionadas a uma user_itemslinha correta user_idpara que ele possa encontrar rapidamente 100 resultados verificando changesem counterordem e descartando linhas que não satisfazem a condição até que encontrou 100 resultados e pronto. No entanto, ele precisa verificar 1.037.1014 linhas até obter resultados suficientes, o que leva muito tempo. A causa pode muito bem ser que todas as correspondências changestenham valores bastante elevados counter.

    Há muito pouco que você pode fazer sobre isso:

    • Você pode acelerar a verificação do índice interno tanto quanto possível, como sugerem as outras respostas.

    • Você pode alterar ORDER BYpara que o PostgreSQL não possa usar sua estratégia preferida:

      ORDER BY counter + 0

      Talvez o plano de execução resultante seja mais rápido.

    • 1

  3. Parece que os índices a seguir funcionariam para você.

    A ideia é adicionar primeiro os predicados de igualdade, depois os predicados de junção/classificação/desigualdade e, em seguida, adicionar outras colunas como INCLUDE.

    changes (type, counter) INCLUDE (id, item_id, item_name, updated_time)
user_items (user_id, item_id)

    Outra opção, dependendo da cardinalidade da junção (quantas linhas)

    changes (type, item_id, counter) INCLUDE (id, item_name, updated_time)
    • 0

relate perguntas