Início / user-284120

St.Mauro's questions

Martin Hope
St.Mauro
Asked: 2023-12-14 17:17:47 +0800 CST
  • 6

Estou tentando otimizar uma consulta lenta no PostgreSQL 15. A consulta é bastante complexa com muitos JOINs, um UNION e subconsultas, mas 99% do tempo de execução está no nó mais interno que verifica sequencialmente uma tabela de registros de 700K com uma condição LIKE. O tempo total de execução é de aproximadamente 15 segundos.

Extraindo a tabela e a condição WHERE da consulta:

SELECT * FROM orders WHERE tag ILIKE '%EJ080EJ%';

Gather  (cost=1000.00..18961.33 rows=27 width=454) (actual time=60.552..69.402 rows=0 loops=1)
Workers Planned: 3
Workers Launched: 3
->  Parallel Seq Scan on orders  (cost=0.00..17958.63 rows=9 width=454) (actual time=47.991..47.992 rows=0 loops=4)
      Filter: ((tag)::text ~~* '%EJ080EJ%'::text)
      Rows Removed by Filter: 74044
Planning Time: 0.732 ms
Execution Time: 69.439 ms

Então adicionei um índice usando a extensão GIN e pg_trgm que na minha experiência é útil com condições LIKE:

CREATE INDEX tmp_dba2 ON orders USING GIN (tag gin_trgm_ops);

Bitmap Heap Scan on orders(cost=18.91..48.84 rows=27 width=454) (actual time=0.061..0.062 rows=0 loops=1)
  Recheck Cond: ((tag)::text ~~* '%EJ080EJ%'::text)
  ->  Bitmap Index Scan on tmp_dba2  (cost=0.00..18.90 rows=27 width=0) (actual time=0.060..0.060 rows=0 loops=1)
        Index Cond: ((tag)::text ~~* '%EJ080EJ%'::text)
Planning Time: 0.947 ms
Execution Time: 0.090 ms

Até agora tudo bem. Enfim, executando toda a consulta o índice é usado mas o tempo de execução não muda tanto:

->  Bitmap Heap Scan on orders  (cost=19.22..94.62 rows=68 width=225) (actual time=13495.733..13495.740 rows=2 loops=1)
      Recheck Cond: ((tag)::text ~~* '%EJ080EJ%'::text)
      Heap Blocks: exact=2
      Buffers: shared hit=21
      ->  Bitmap Index Scan on tmp_dba2  (cost=0.00..19.21 rows=68 width=0) (actual time=0.087..0.087 rows=2 loops=1)
            Index Cond: ((tag)::text ~~* '%EJ080EJ%'::text)
            Buffers: shared hit=19

No meu (pobre) entendimento dos componentes internos do Postgres, isso significa que a varredura de índice recupera um bitmap das páginas heap que atendem à condição (2 registros) e, em seguida, a varredura heap lê as páginas para recuperar os dados (2 registros).

Não consigo entender por que a leitura de 2 registros leva 13 segundos, assim como uma varredura sequencial, então adicionar o índice parece inútil.

Alguém pode me ajudar a entender esse comportamento?

postgresql