Início / dba / Perguntas / 343005
Accepted
zabop
Asked: 2024-10-15 23:01:34 +0800 CST

Como prever se um plano de consulta envolverá o uso de um índice?

  • 772

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?

postgresql

2 respostas

  1. Best Answer

    O operador LIKE usa a coluna de array histogram_bounds de pg_stats para estimar a seletividade para esse operador com o padrão fixo. Ele usa esse array como se fosse uma amostra aleatória da tabela (o que não é totalmente correto tratá-lo dessa forma, mas mesmo assim é o que ele faz).

    Dado seu conjunto de dados de exemplo, é provável que essa matriz tenha 0 entradas que correspondem a '%abc%' ou 1, dependendo de como a amostra aleatória usada por ANALYZE cai. Se a matriz tiver uma correspondência, ela estimará uma seletividade de cerca de 1/101. Se a matriz tiver zero correspondências, ela estimará inicialmente a seletividade de 0, mas depois a fixará em 0,0001 antes de usá-la. (Alguns dias atrás, descrevi isso de forma diferente, mas desde então revisei o código-fonte para esclarecer o que estava acontecendo, veja a fonte ). Dependendo de qual dessas coisas acontecer, ele fará a varredura de sequência ou a varredura de índice porque acha que uma ou outra será mais rápida.

    Esta explicação ignora o efeito de most_common_vals e null_frac, que são usados ​​em geral, mas não acho que façam muita diferença no seu caso específico.

    Para prever o que acontecerá, você pode inspecionar a matriz e contar quantos de seus elementos correspondem a %abc%:

    select count(*), count(*) filter (where t like '%abc%')  
from pg_stats, unnest(histogram_bounds::text::text[]) t 
where attname='col0' and tablename='table0';

    A saída disso dependerá da amostra aleatória obtida no ANALYZE. Parece que a amostra conterá 1/101 de correspondência em cerca de um quarto do tempo e 0/101 de correspondência no restante do tempo.

    Não está claro o que de útil você pode fazer com isso, mas sua pergunta parece ser motivada mais pela curiosidade do que pela praticidade. (Transformar isso em uma previsão completa daria muito trabalho incorporando suas configurações do planejador, outras tabelas de catálogo, etc., o que daria muito trabalho para algo sem nenhuma aplicação prática óbvia.)

    • 3

  2. Jeff forneceu insights de primeira classe sobre como o planejador de consultas faz estimativas.

    Quanto à função que você está desejando: você já a tem: EXPLAIN.
    Qualquer função personalizada que possamos criar (após trabalho duro tentando emular muito da funcionalidade dos planejadores de consulta) só poderia chegar perto, EXPLAINna melhor das hipóteses. Porque EXPLAINé a fonte da verdade.

    Se você realmente quer uma resposta booleana para a pergunta "Essa consulta usará esse índice?" , você pode fazer um loop pela EXPLAINsaída em uma função. Basicamente:

    CREATE OR REPLACE FUNCTION f_query_uses_index(qry text, idx text)
  RETURNS bool
  LANGUAGE plpgsql AS
$func$
DECLARE
   line text;
BEGIN
   FOR line in
      EXECUTE 'EXPLAIN ' || qry
   LOOP
      IF line ~ idx THEN
         RETURN true;
      END IF;
   END LOOP;

   RETURN false;
END
$func$;

    Peça seu exemplo:

    SELECT f_query_uses_index($q$select * from table0 where col0 like '%bcd%' limit 200$q$
                        , 'col0_gin_trgm_idx')

    Esteja ciente de que passar código é amplamente aberto a injeção de SQL e outros problemas. Então isso é somente para usuários confiáveis.

    Dito isto, apenas observar o EXPLAINresultado será melhor na maioria das situações.

    • 3

relate perguntas