Início / user-282180

Bunny Boss's questions

Martin Hope
Bunny Boss
Asked: 2023-11-20 23:15:28 +0800 CST
  • 5

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