Início / dba / Perguntas / 342944
Accepted
zabop
Asked: 2024-10-11 17:17:17 +0800 CST

O que devo fazer para que retornar 200 linhas seja pelo menos tão rápido quanto retornar 1.000 linhas ao executar consultas de correspondência parcial em uma coluna indexada?

  • 772

Tenho uma tabela PostgreSQL 17 table0. Ela tem uma única coluna col0contendo strings aleatórias de 2 a 16 caracteres. Gostaria de executar consultas de correspondência parcial, portanto, criei um índice GIN comgin_trgm_ops . Para minha surpresa, descobri que posso selecionar até 1000 linhas contendo abcmuito mais rápido do que até 200 linhas contendo abc. Configuração reproduzível:

Inicie o DB usando o Docker :

docker run \
    --name postgres-db \
    -e POSTGRES_DB=postgres \
    -e POSTGRES_USER=postgres \
    -e POSTGRES_PASSWORD=mysecretpassword \
    -p 5432:5432\
    -d postgres

Eu executo consultas usando o DBeaver ou salvando-as em query.sqle então:

PGPASSWORD=mysecretpassword psql \
    -h localhost \
    -p 5432 \
    -U postgres \
    -d postgres \
    -v ON_ERROR_STOP=1 \
    -f query.sql

Montar a mesa:

create table public.table0 (
    col0 varchar(25)
);

select setseed(0.12343);

insert into table0 (col0)
select substring(md5(random()::text), 1, (2 + (random() * 14))::int)
from generate_series(1, 12345678);

create extension pg_trgm;
create index col0_gin_trgm_idx on table0 using gin (col0 gin_trgm_ops);

vacuum (full, analyze) table0;

Verifique o plano de execução e o tempo de execução da seleção de 200 linhas contendo abc:

explain analyze
select * from table0 where col0 like '%abc%' limit 200;

Saída:

                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..352.27 rows=200 width=9) (actual time=0.672..49.646 rows=200 loops=1)
   ->  Seq Scan on table0  (cost=0.00..216621.29 rows=122985 width=9) (actual time=0.671..49.599 rows=200 loops=1)
         Filter: ((col0)::text ~~ '%abc%'::text)
         Rows Removed by Filter: 114081
 Planning Time: 4.540 ms
 Execution Time: 50.960 ms
(6 rows)

Verifique o plano de execução e o tempo de execução da seleção de até 1000 linhas contendo abc:

explain analyze
select * from table0 where col0 like '%abc%' limit 1000;

Saída:

                                                                 QUERY PLAN                                                                  
---------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=848.36..1369.03 rows=1000 width=9) (actual time=17.373..26.987 rows=1000 loops=1)
   ->  Bitmap Heap Scan on table0  (cost=848.36..64883.21 rows=122985 width=9) (actual time=17.371..26.931 rows=1000 loops=1)
         Recheck Cond: ((col0)::text ~~ '%abc%'::text)
         Heap Blocks: exact=846
         ->  Bitmap Index Scan on col0_gin_trgm_idx  (cost=0.00..817.62 rows=122985 width=0) (actual time=14.689..14.690 rows=21318 loops=1)
               Index Cond: ((col0)::text ~~ '%abc%'::text)
 Planning Time: 2.165 ms
 Execution Time: 27.356 ms
(8 rows)

Como é visível, quando eu uso LIMIT 200, o mecanismo está fazendo um Seq Scan on table0, mas quando eu tenho LIMIT 1000, Bitmap Index Scan on col0_gin_trgm_idxé usado. Isso explica superficialmente por que a consulta usando LIMIT 200levou 4.540+50.960= 55.5 milissegundos, enquanto a LIMIT 1000consulta levou menos, 2.165+27.356=29.521 milissegundos.

Eu li (veja isto ou isto ) que idealmente eu não deveria tentar forçar o uso de índices em um ambiente de produção. Ingenuamente, parece que usar o índice para pesquisar por 200 linhas contendo abcseria mais rápido do que a varredura sequencial usada atualmente, assim como usar o índice para pesquisar por 1000 linhas é mais rápido do que pesquisar por 200 linhas usando varredura sequencial.

No meu cenário do mundo real ( instância do Aurora PostgreSQL em execução no AWS RDS ), essa diferença é limitante: quando preciso selecionar 25 linhas da minha tabela, é muito mais rápido selecionar 100 delas e então filtrar essas 100 por outros meios postgresql(ou posso simplesmente modificar o aplicativo para que selecionar 100 linhas em vez de 25 também seja aceitável).

Gostaria de saber se estou fazendo algo abaixo do ideal com a indexação ou se estou esquecendo de algo.

O que devo fazer para que a consulta com LIMIT 200seja pelo menos tão rápida quanto a consulta com LIMIT 1000?

Estou interessado principalmente em métodos aconselháveis ​​para uso em um ambiente de produção. É seguro assumir que table0não precisará ser modificado para editar seu conteúdo, nunca.

postgresql

1 respostas

  1. Best Answer

    Não consigo reproduzir isso com 100% de confiabilidade (o que é estranho porque eu pensaria que o setseed faria o mesmo toda vez, mas para mim não faz). Quando a replicação falha, é porque ela usa o escaneamento de bitmap para ambas as consultas.

    O problema parece estar na estimativa de linha (esperado 122985 real 21318, proporção de 5,8). A estimativa incorreta faz com que a varredura de sequência com limite 1 pareça mais rápida, pois ela espera encontrar as primeiras N linhas após varrer menos da tabela.

    Acredito que o problema com a estimativa é que o LIKE normalmente não produzirá estimativas de seletividade muito menores que 1/tamanho da estatística, e isso não é preciso o suficiente para a tarefa em questão.

    Você poderia consertar isso fazendo:

    alter table table0 alter column col0 set statistics 1000;

    antes que a tabela seja analisada, para melhorar as estatísticas. Você pode querer ir acima de 1000.

    Isso parece uma solução bem precária, no entanto. Eu contradiria aqueles outros posts aos quais você fez referência. Se essa pequena mudança no desempenho for o suficiente para se preocupar, acho que seria melhor lidar com isso dando uma dica direta na consulta para forçá-la a usar o plano que você quer (veja pg_hint_plan )

    • 1

relate perguntas