Executar a mesma consulta em duas tabelas que diferem apenas no número de linhas (~7,8M vs ~1,4M) resulta em dois planos diferentes, o que parece razoável. Mas a execução na tabela menor é de 4 a 5 vezes mais lenta e eu gostaria de entender o porquê.
As tabelas são definidas como estão:
Column | Type | Collation | Nullable | Default
------------+--------------------------+-----------+----------+---------
image_id | bigint | | not null |
h3_cell | h3index | | not null |
created_at | timestamp with time zone | | not null |
location | geometry(PointZ,4326) | | not null |
Indexes:
"images_a_pkey" PRIMARY KEY, btree (image_id)
"images_a_created_at_idx" btree (created_at)
"images_a_h3_cell_idx" btree (h3_cell)
A consulta é a seguinte
h3_cells AS (
SELECT UNNEST(h3_linestring_to_cells(:line_string, 13, 1)) AS cell
)
SELECT COUNT(*)
FROM images
JOIN h3_cells hc ON images.h3_cell = hc.cell
A h3_linestring_to_cells()
função retorna um array cujo h3index
tamanho pode, em alguns casos, chegar a dezenas de milhares de valores. Nos exemplos abaixo, ele retorna cerca de 50.000.
Na tabela com 7,8 milhões de linhas, as entradas de plano e execução são as seguintes (valores da matriz redigidos para brevidade):
Aggregate (cost=347404.47..347404.48 rows=1 width=8) (actual time=74.311..74.312 rows=1 loops=1)
Buffers: shared hit=154681 read=328
I/O Timings: shared read=1.362
-> Nested Loop (cost=0.43..346724.23 rows=272093 width=0) (actual time=0.051..74.246 rows=833 loops=1)
Buffers: shared hit=154681 read=328
I/O Timings: shared read=1.362
-> ProjectSet (cost=0.00..256.90 rows=51377 width=8) (actual time=0.002..4.113 rows=51377 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
-> Index Only Scan using images_a_h3_cell_idx on images_a (cost=0.43..6.68 rows=5 width=8) (actual time=0.001..0.001 rows=0 loops=51377)
Index Cond: (h3_cell = (unnest('{...}'::h3index[])))
Heap Fetches: 354
Buffers: shared hit=154681 read=328
I/O Timings: shared read=1.362
Planning Time: 139.421 ms
Execution Time: 74.345 ms
Enquanto na tabela menor de 1,4 milhões de linhas, o plano e a execução são estes:
Aggregate (cost=105040.78..105040.79 rows=1 width=8) (actual time=327.586..327.587 rows=1 loops=1)
Buffers: shared hit=148358 read=6315 written=41
I/O Timings: shared read=26.521 write=0.327
-> Merge Join (cost=4791.05..104802.14 rows=95455 width=0) (actual time=321.174..327.575 rows=118 loops=1)
Merge Cond: (ptilmi.h3_cell = (unnest('{...}'::h3index[])))
Buffers: shared hit=148358 read=6315 written=41
I/O Timings: shared read=26.521 write=0.327
-> Index Only Scan using images_b_h3_cell_idx on images_b ptilmi (cost=0.43..95041.10 rows=1415438 width=8) (actual time=0.026..245.897 rows=964987 loops=1)
Heap Fetches: 469832
Buffers: shared hit=148358 read=6315 written=41
I/O Timings: shared read=26.521 write=0.327
-> Sort (cost=4790.62..4919.07 rows=51377 width=8) (actual time=11.181..13.551 rows=51390 loops=1)
Sort Key: (unnest('{...}'::h3index[]))
Sort Method: quicksort Memory: 1537kB
-> ProjectSet (cost=0.00..256.90 rows=51377 width=8) (actual time=0.002..3.716 rows=51377 loops=1)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
Planning Time: 146.617 ms
Execution Time: 327.626 ms
No caso de uma matriz de origem menor, por exemplo, de tamanho 25.000, o plano na tabela menor muda para o primeiro (loop aninhado) e seu tempo de execução se torna mais alinhado com as expectativas (mais rápido do que na tabela maior).
Não consigo entender o que desencadeia essa mudança de plano para um menos eficiente.
Observe que estou usando CTE+JOIN em vez de eg WHERE h3_cell = ANY(h3_linestring_to_cells(:line_string, 13, 1))
, pois o array resultante costuma ser bem grande e descobri que o primeiro método costuma ser mais eficiente neste caso. Curiosamente, com um array de 50.000 entradas, a = ANY()
abordagem é mais rápida na tabela menor; com 25.000, é mais lenta.