Início / dba / Perguntas / 333294
Accepted
Bunny Boss
Asked: 2023-11-20 23:15:28 +0800 CST

Outliers produzem um plano de consulta incorreto

  • 772

Tenho um documento de tabela com aproximadamente 2,5 milhões de linhas e mais de 100 colunas. Forneço usado apenas em colunas de consulta:

CREATE TABLE document
(
  id serial PRIMARY KEY,
  organizationid INTEGER,
  status_1 TEXT NOT NULL,
  status_2 INTEGER DEFAULT 0 NOT NULL
);

CREATE INDEX ON document (organizationid);
CREATE INDEX ON document (status_1);
CREATE INDEX ON document (status_2);

Precisa otimizar a próxima consulta:

SELECT *
FROM document
WHERE status_1 = '42' AND status_2 = 0 AND organizationid = 42 
ORDER BY id
LIMIT 25;

Essa consulta funciona muito bem para outras organizações, mas a organização 42 não possui documentos com esse filtro (ou tem menos de 20 de tempos em tempos) e produz um plano de consulta incorreto e uma consulta lenta. O problema está nas distribuições de valores na tabela. A porcentagem de documentos com status_1 = '42' é 95%, status_2 = 0 é 10% e Organizationid = 42 é 2%. Portanto, o DB espera que haja cerca de 4.750 documentos para este filtro e faça a varredura do índice usando documnet_pkey e verifique a tabela completa sem encontrar nenhum documento. Demora vários minutos. Mas se eu mudar de organização para outra, que tenha muitos documentos, a consulta demorará menos de um segundo. E se eu mudar o filtro para organização com pequena quantidade de documentos - o banco de dados apenas usa o índice por ID da organização e depois classifica os documentos resultantes. Demora menos de 50 ms.

Como posso acelerar a consulta da organização 42? Ou o que preciso pesquisar para isso?

Eu uso PostgreSQL 12

auto-aspiração e análise ativadas, e executei a análise novamente antes dos testes. Já aumentei GEQO_EFFORT para 10 (máximo), DEFAULT_STATISTICS_TARGET para 1000 (mais impacto de valor em outras consultas), estatística criada:

CREATE STATISTICS custom_1 ON organizationid, status_1, status_2 FROM document;

criou índice específico:

CREATE INDEX ON document ((status_1 = '42' AND status_2 = 0 AND organizationid = 42));

e execute novamente a análise após todas as alterações. Mas isso não ajuda. Eu verifiquei pg_stats e pg_stats_ext, ele contém estatísticas corretas conforme esperado. Para a estatística criada, ela possui no valor mais comum outra combinação dessas colunas e não possui essa combinação, portanto é possível assumir aquela combinação onde é incomum. Para índice na expressão, a estatística diz que existem apenas valores falsos no índice.

postgresql

1 respostas

  1. Best Answer

    O índice ideal para essas consultas seria:

    CREATE INDEX ON document (organizationid, status_1, status_2, id);

    Isso deve corrigir os parâmetros de consulta específicos com os quais você tem um grande problema e também deve melhorar todas as outras parametrizações.

    As estatísticas personalizadas nas colunas podem fazer um bom trabalho ao corrigir a estimativa quando uma combinação é mais comum do que o esperado, mas geralmente é um mau trabalho ao corrigi-la quando a combinação é menos comum do que o esperado, que é o caso aqui.

    Seu índice de expressão pode funcionar, mas apenas se a consulta for escrita de maneira estranha como:

    ...
WHERE (status_1 = '42' AND status_2 = 0 AND organizationid = 10) is true
...

    Uma estatística personalizada (em vez de índice) nessa expressão também poderia "funcionar", mas não foi implementada até a v14 e ainda exigiria a consulta escrita de maneira estranha. Mas, nesse ponto, isso corrigiria a estimativa, mas inibiria o uso do índice que você deseja usar (o bitmap nos índices de coluna única), de modo que "funcionaria", mas ainda assim não funcionaria.

    • 0

relate perguntas