Tenho a seguinte consulta:
select ro.*
from courier c1
join courier c2 on c2.real_physical_courier_1c_id = c1.real_physical_courier_1c_id
join restaurant_order ro on ro.courier_id = c2.id
left join jsonb_array_elements(items) jae on true
left join jsonb_array_elements(jae->'options') ji on true
inner join catalogue c on c.id in ((jae->'id')::int, (ji->'id')::int)
join restaurant r on r.id = ro.restaurant_id
where c1.id = '7b35cdab-b423-472a-bde1-d6699f6cefd3' and ro.status in (70, 73)
group by ro.order_id, r.id ;
Aqui está uma parte de um plano de consulta que leva cerca de 95% do tempo:
-> Parallel Bitmap Heap Scan on restaurant_order ro (cost=23.87..2357.58 rows=1244 width=1257) (actual time=11.931..38.163 rows=98 loops=2)"
Recheck Cond: (status = ANY ('{70,73}'::integer[]))"
Heap Blocks: exact=28755"
-> Bitmap Index Scan on ro__status (cost=0.00..23.34 rows=2115 width=0) (actual time=9.168..9.168 rows=51540 loops=1)"
Index Cond: (status = ANY ('{70,73}'::integer[]))"
Eu tenho algumas perguntas.
- Primeiro, uma seção de verificação de índice de bitmap. O Postgres percorre 51.540 registros de índice ro__status com base
Index Cond: (status = ANY ('{70,73}'::integer[]))"e cria um bitmap com 28.755 elementos. Suas chaves são a localização física das linhas da tabela correspondentes (indicadasexactnaHeap Blocksseção). Isso está correto? - Segundo, este mapa é passado para a fase Bitmap Heap Scan.
Recheck Condnão é realmente executado, pois os blocos de heap não são do estilo com perdas. Bitmap Heap Scan classifica um bitmap pela localização física das tuplas para permitir um acesso sequencial. Em seguida, ele lê os dados da tabela sequencialmente em duas etapas (loops=2) e não obtém mais de 196 linhas da tabela. Isso é correto? - O tamanho do bitmap refletido na
Heap Blocks: exact=28755linha varia muito ao longo do tempo. A diferença é de duas ordens de grandeza. Por exemplo, ontem eram cerca de 500. Por que isso acontece? - Agora, por que um bitmap criado durante a fase de verificação de índice de bitmap tem tantas chaves? Existe um índice ro__status que pode indicar que existem apenas cerca de 200 registros com status 70 e 73. Não consigo pensar em nenhuma razão que impeça o postgres de manter apenas as chaves que realmente satisfazem um
index cond. A sobrecarga parece ser enorme: em vez de ~ 200 chaves, existem 28755! - Por que o Bitmap Heap Scan demora tanto? Pelo que vi são duas leituras sequenciais (
loops=2), deve demorar bem menos, não? Ou, a classificação de bitmap pela localização física das tuplas é o principal culpado? - Devo me preocupar com estimativas ruins? Se sim, aumentar default_statistics_target deve ajudar, certo? Agora é o padrão 100.
Apenas no caso, aqui está um plano completo:
"Group (cost=51297.15..52767.65 rows=19998 width=1261) (actual time=42.555..42.555 rows=0 loops=1)"
" Group Key: ro.order_id, r.id"
" -> Gather Merge (cost=51297.15..52708.83 rows=11764 width=1261) (actual time=42.554..45.459 rows=0 loops=1)"
" Workers Planned: 1"
" Workers Launched: 1"
" -> Group (cost=50297.14..50385.37 rows=11764 width=1261) (actual time=38.850..38.850 rows=0 loops=2)"
" Group Key: ro.order_id, r.id"
" -> Sort (cost=50297.14..50326.55 rows=11764 width=1261) (actual time=38.850..38.850 rows=0 loops=2)"
" Sort Key: ro.order_id, r.id"
" Sort Method: quicksort Memory: 25kB"
" Worker 0: Sort Method: quicksort Memory: 25kB"
" -> Nested Loop (cost=31.84..45709.27 rows=11764 width=1261) (actual time=38.819..38.819 rows=0 loops=2)"
" -> Nested Loop Left Join (cost=27.21..5194.50 rows=5882 width=1325) (actual time=38.819..38.819 rows=0 loops=2)"
" -> Nested Loop Left Join (cost=27.20..5076.49 rows=59 width=1293) (actual time=38.818..38.818 rows=0 loops=2)"
" -> Nested Loop (cost=27.20..5074.49 rows=1 width=1261) (actual time=38.818..38.818 rows=0 loops=2)"
" -> Hash Join (cost=26.93..5073.59 rows=1 width=1257) (actual time=38.817..38.818 rows=0 loops=2)"
" Hash Cond: (c2.real_physical_courier_1c_id = c1.real_physical_courier_1c_id)"
" -> Nested Loop (cost=24.28..5068.22 rows=1038 width=1267) (actual time=11.960..38.732 rows=98 loops=2)"
" -> Parallel Bitmap Heap Scan on restaurant_order ro (cost=23.87..2357.58 rows=1244 width=1257) (actual time=11.931..38.163 rows=98 loops=2)"
" Recheck Cond: (status = ANY ('{70,73}'::integer[]))"
" Heap Blocks: exact=28755"
" -> Bitmap Index Scan on ro__status (cost=0.00..23.34 rows=2115 width=0) (actual time=9.168..9.168 rows=51540 loops=1)"
" Index Cond: (status = ANY ('{70,73}'::integer[]))"
" -> Index Scan using courier_pkey on courier c2 (cost=0.41..2.18 rows=1 width=26) (actual time=0.005..0.005 rows=1 loops=195)"
" Index Cond: (id = ro.courier_id)"
" -> Hash (cost=2.63..2.63 rows=1 width=10) (actual time=0.039..0.039 rows=1 loops=2)"
" Buckets: 1024 Batches: 1 Memory Usage: 9kB"
" -> Index Scan using courier_pkey on courier c1 (cost=0.41..2.63 rows=1 width=10) (actual time=0.034..0.034 rows=1 loops=2)"
" Index Cond: (id = '7b35cdab-b423-472a-bde1-d6699f6cefd3'::uuid)"
" -> Index Only Scan using restaurant_pkey on restaurant r (cost=0.27..0.89 rows=1 width=4) (never executed)"
" Index Cond: (id = ro.restaurant_id)"
" Heap Fetches: 0"
" -> Function Scan on jsonb_array_elements jae (cost=0.00..1.00 rows=100 width=32) (never executed)"
" -> Function Scan on jsonb_array_elements ji (cost=0.01..1.00 rows=100 width=32) (never executed)"
" -> Bitmap Heap Scan on catalogue c (cost=4.63..6.87 rows=2 width=4) (never executed)"
" Recheck Cond: ((id = ((jae.value -> 'id'::text))::integer) OR (id = ((ji.value -> 'id'::text))::integer))"
" -> BitmapOr (cost=4.63..4.63 rows=2 width=0) (never executed)"
" -> Bitmap Index Scan on catalogue_pkey (cost=0.00..0.97 rows=1 width=0) (never executed)"
" Index Cond: (id = ((jae.value -> 'id'::text))::integer)"
" -> Bitmap Index Scan on catalogue_pkey (cost=0.00..0.97 rows=1 width=0) (never executed)"
" Index Cond: (id = ((ji.value -> 'id'::text))::integer)"
"Planning Time: 1.113 ms"
"Execution Time: 45.588 ms"
Ele cria um bitmap de 51.540 itens. Este é então dividido (aproximadamente) ao meio, um para cada um dos dois processos paralelos. O relatório de
exact=28755é aparentemente para apenas um desses processos. (Se você desabilitar as consultas paralelas porset max_parallel_workers_per_gather TO 0, o plano resultante será mais fácil de entender. Geralmente, essa é a primeira coisa que faço ao analisar o desempenho de uma consulta, a menos que a paralelização seja exatamente o que estou tentando estudar. Quaisquer melhorias que eu faça serão em seguida, geralmente traduz de volta para execução paralela.)Um bitmap está inerentemente em ordem física. Classificá-lo não é uma etapa separada de apenas criá-lo. O PostgreSQL lê os blocos nessa ordem, um por um. Cabe ao sistema operacional/sistema de arquivos aglutinar essas leituras individuais em leituras sequenciais, se assim decidir. Na minha experiência, você precisa ler quase todos os blocos antes que isso tenha um bom efeito. Se você ler apenas a cada quinto bloco (estocásticamente), também poderá fazer leituras aleatórias. Não posso dizer pelos seus dados que fração da tabela 28755 blocos representa.
Índices no PostgreSQL não contêm inerentemente nenhuma informação de visibilidade. "ro__status" não pode saber quais desses ctid ainda estão visíveis, então todos eles precisam ser inseridos no bitmap. Em seguida, a maioria deles é rejeitada no estágio de varredura de heap como sendo invisível. (Isso não é relatado explicitamente, como são as rejeições de verificação e filtro. Você deve inferir pela diferença entre o tamanho do bitmap e a contagem final de linhas. No caso de BitmapAnd e BitmapOr, você não pode fazer isso facilmente, pois o tamanho do bitmap não é preciso).
Então esse é o cerne do problema, você está visitando mais de 50.000 tuplas apenas para encontrar 195 vivas. Limpar essas tuplas mortas do índice é uma das principais tarefas da aspiração. Portanto, é provável que sua mesa não esteja sendo aspirada o suficiente. Você pode testar isso de forma bem simples, limpar a mesa e ver se isso resolve o problema. Se isso não acontecer, então você provavelmente tem instantâneos de longa data que estão impedindo que o vácuo seja eficaz, então vá caçá-los.
Os índices Btree têm um recurso de "microvácuo" onde uma varredura de índice regular mata uma tupla de índice que descobriu apontar para uma tupla de heap morta para todos. Mas as varreduras de índice de bitmap não implementam isso, pois não revisitam o índice após a consulta inicial, portanto, não há uma boa oportunidade para eliminar a tupla do índice. As varreduras de bitmap se beneficiarão desse microvácuo, mas não realizarão o microvácuo por si mesmas. Isso pode levar a uma situação perversa em que quanto mais varreduras de bitmap são preferidas em relação a varreduras de índice regulares, mais inchada a parte relevante do índice se torna até que as varreduras de bitmap comecem a ter um desempenho pior. O aumento da aspiração pode corrigir isso, mas se você não quiser aumentá-lo ainda mais, poderá apenas desencorajar os bitmaps em geral.