Estou usando a imagem docker mais recente do PostgreSQL para criar um BD local (em uma máquina Apple M1 Pro - MacOS Sonoma 14.5). Crio uma tabela table0com uma única coluna col0dentro dela e a preencho com strings aleatórias de 2 a 16 caracteres. Crio um trigram-index em col0e vacuum (analyze). Passos exatos:
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 (analyze) table0;
Examino o plano de consulta para selecionar 200 linhas contendo a string abc:
explain analyze
select * from table0 where col0 like '%abc%' limit 200;
Saída, confirmando que o índice do trigrama não é, mas uma varredura sequencial é usada:
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..351.78 rows=200 width=10) (actual time=0.313..15.640 rows=200 loops=1)
-> Seq Scan on table0 (cost=0.00..216614.79 rows=123154 width=10) (actual time=0.312..15.620 rows=200 loops=1)
Filter: ((col0)::text ~~ '%abc%'::text)
Rows Removed by Filter: 115643
Planning Time: 4.401 ms
Execution Time: 15.841 ms
(6 rows)
Entretanto, se em vez de procurar por linhas contendo abceu procurar por linhas contendo bcd:
explain analyze
select * from table0 where col0 like '%bcd%' limit 200;
Então obtenho um plano de consulta diferente, que agora inclui uma varredura de índice:
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=52.34..764.83 rows=200 width=10) (actual time=7.032..7.230 rows=200 loops=1)
-> Bitmap Heap Scan on table0 (cost=52.34..4394.94 rows=1219 width=10) (actual time=7.031..7.220 rows=200 loops=1)
Recheck Cond: ((col0)::text ~~ '%bcd%'::text)
Heap Blocks: exact=169
-> Bitmap Index Scan on col0_gin_trgm_idx (cost=0.00..52.04 rows=1219 width=0) (actual time=5.100..5.100 rows=21264 loops=1)
Index Cond: ((col0)::text ~~ '%bcd%'::text)
Planning Time: 0.521 ms
Execution Time: 7.366 ms
(8 rows)
Esta configuração pode não ser totalmente reproduzível na primeira tentativa, mesmo que setseed(0.12343);seja usada, já que analyze"coleta estatísticas com base em sua própria seleção aleatória de linhas" (veja o segundo parágrafo aqui ). Recriei a situação acima várias vezes e nunca precisei tentar as etapas de configuração mais de 4 vezes, então espero que seja facilmente reproduzível, mesmo que o código que forneço não seja totalmente determinístico. (Removi e reiniciei o contêiner docker entre as tentativas.)
Esta resposta dá uma explicação básica do porquê uma vez o scan sequencial e por que às vezes o scan de índice é usado. Ela também tem 2 sugestões sobre como desencorajar scans sequenciais: modificando random_page_coste STATISTICSvalores.
Eu defino random_page_costpara 1.1 (via ALTER DATABASE postgres SET random_page_cost = 1.1;). Eu também "aumento a quantidade de estatísticas coletadas" por analyze(via ALTER TABLE table0 ALTER COLUMN col0 SET STATISTICS 1000;). Depois de outro vacuum (analyze) table0;eu reexecuto:
explain analyze
select * from table0 where col0 like '%abc%' limit 200;
e dessa vez o col0_gin_trgm_idxíndice trigrama é de fato usado. Depois disso, recriei o cenário acima, e sem modificar random_page_costou STATISTICSexecutar novamente vacuum (analyze) table0;- isso também modificou o comportamento e causou uma troca de varredura sequencial para varredura de índice. Acredito que isso se deva à natureza não determinística das estatísticas coletadas por analyze.
Desta vez, em vez de poder disparar o uso do índice (o que posso fazer agora, principalmente graças à resposta mencionada ), gostaria de entender os detalhes sobre como a decisão é tomada entre a varredura sequencial e a varredura de índice . Idealmente, gostaria de poder prever se a consulta:
explain analyze
select * from table0 where col0 like '%xyz%' limit 200;
irá disparar uma varredura de índice ou uma varredura sequencial, sabendo xyz, e qualquer coisa relacionada a estatísticas ou configurações do banco de dados. Anteriormente, no contexto de uma pergunta semelhante, fui aconselhado a verificar SELECT name, setting FROM pg_settings WHERE name = ANY ( '{shared_buffers, effective_cache_size, random_page_cost, effective_io_concurrency, work_mem}'::text[]);. Ele retorna (antes de modificar as configurações padrão):
name | setting
--------------------------+---------
effective_cache_size | 524288
effective_io_concurrency | 1
random_page_cost | 4
shared_buffers | 16384
work_mem | 4096
(5 rows)
Acho que esses valores têm um efeito na decisão de varredura sequencial vs varredura de índice. Espero que exista uma função f com duas saídas possíveis: varredura sequencial ou varredura de índice . Imagino que f pegue xyz, random_page_cost, estatísticas coletadas por analyze, etc como entradas. Gostaria de entender a lista de entradas (ou seja, o que é etc?) e o que f faz com elas.
Como posso prever se um plano de consulta envolverá o uso de um índice?
