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 changes
tabela 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)
você deve reescrever sua consulta para
Com os índices
isso deve acelerar a consulta
A junção geralmente é mais rápida, pois
IN
aum í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
A causa do problema é a seguinte: O otimizador pensa que há linhas suficientes
changes
relacionadas a umauser_items
linha corretauser_id
para que ele possa encontrar rapidamente 100 resultados verificandochanges
emcounter
ordem 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ênciaschanges
tenham valores bastante elevadoscounter
.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 BY
para que o PostgreSQL não possa usar sua estratégia preferida:Talvez o plano de execução resultante seja mais rápido.
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
.Outra opção, dependendo da cardinalidade da junção (quantas linhas)