Início / dba / Perguntas / 340023
Accepted
Istopopoki
Asked: 2024-06-05 20:06:44 +0800 CST

O planejador de consultas do Postgres mudou repentinamente de ideia

  • 772

Minha tabela Postgres possui uma coluna de intervalo contendo carimbos de data e hora com fuso horário. Eu criei um índice no limite inferior do intervalo, assim:

CREATE INDEX bdg_sys_period_start_idx ON building USING btree (lower(sys_period));

Agora estou tentando executar a seguinte consulta:

select * from building where lower(sys_period) > '2024-05-12 10:31:14.481545+01'::timestamptz;

Aí vem a parte interessante. Eu executo um ANALYZE na tabela e depois um EXPLAIN na consulta. Eu entendi isso:

insira a descrição da imagem aqui

Perfeito, o Postgres quer usar meu novo índice!

Então eu inicio a consulta e leva muito tempo. Paro a consulta e executo EXPLAIN novamente. E, surpresa, o planejador de consultas agora me diz que deseja usar uma varredura sequencial.

insira a descrição da imagem aqui

Vejo que o número planejado de linhas retornadas salta de 97 mil para 1,6 milhões. O número real é 30 linhas.

Tenho muitas perguntas sobre esta situação:

  • Por que o planejador de consultas mudou repentinamente de ideia?
  • As estatísticas devem ser coletadas para colunas de intervalo? Eu vi essa discussão , mas não tenho certeza se isso foi implementado.
  • Tentei criar uma estatística personalizada em lower(sys_period) diretamente:
CREATE STATISTICS IF NOT EXISTS sys_period_start_range ON ( lower(sys_period) ) FROM building;

É suposto ser útil?

  • Tentei aumentar o tamanho da estatística na coluna sys_period
ALTER TABLE building ALTER sys_period SET STATISTICS 1000;

É suposto ser útil?

Agradeço antecipadamente por sua ajuda.

postgresql

2 respostas

  1. Best Answer

    Finalmente entendi e me sinto estúpido. Vou descrever o que aconteceu caso alguém tenha o mesmo problema. Estou usando o DBeaver e a conexão que estou usando para este banco de dados tem o autocommit desabilitado. Quando executo analyze, ele inicia uma transação. Então, quando executo explain, ele fornece os resultados esperados do planejador de consulta (varredura de índice) e reverte a transação atual. Quando executo o planejador de consultas novamente, o anterior analyzefoi revertido e seq scanaparece novamente.

    • 2

  2. Tem certeza de que executou o primeiro ANALYZE depois de criar o índice e antes de executar a consulta? Se você executou ANALYZE antes de criar o índice, isso explicaria por que o planejador escolheu um plano diferente. O PostgreSQL começa a coletar estatísticas para a expressão indexada, mas isso não acontece antes das próximas ANALYZEexecuções.

    As estatísticas estendidas são desnecessárias – isso apenas duplica o que acontece automaticamente de qualquer maneira.

    Aumentar a meta estatística para aquela coluna pode fazer a diferença; você teria que tentar.

    Se tudo estiver como você descreveu, o salto nas estimativas é misterioso. Com os resultados de EXPLAIN (ANALYZE, BUFFERS, SETTINGS)ambos os casos, poderemos lhe contar mais.

    • 1

relate perguntas