Início / dba / Perguntas / 346064
Accepted
Jukurrpa
Asked: 2025-04-07 23:16:01 +0800 CST

Plano diferente e consulta mais lenta em tabela Postgres menor

  • 772

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 h3indextamanho 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.

postgresql

1 respostas

  1. Best Answer

    É difícil dizer com certeza com as informações fornecidas, mas talvez uma varredura somente de índice images_bnão pareça atraente o suficiente porque a tabela não foi limpa recentemente e a maioria dos blocos não são "totalmente visíveis" no mapa de visibilidade, de modo que a varredura de índice exigiria muitas "buscas de heap" para determinar a visibilidade da entrada de índice.

    Se VACUUMa tabela fizer com que o PostgreSQL mude para um plano de execução mais rápido, considere definir um valor mais baixo autovacuum_vacuum_scale_factore/ou um valor mais baixo autovacuum_vacuum_insert_scale_factorna tabela, para que o autovacuum mantenha o mapa de visibilidade em boa forma.

    • 1

relate perguntas