Início / dba / Perguntas / 337646
Accepted
olleicua
Asked: 2024-03-11 22:49:14 +0800 CST

Otimize o desempenho da consulta para um filtro em col_a > col_b

  • 772

Eu tenho uma tabela grande em um banco de dados PostgreSQL (cerca de 100 colunas, milhões de linhas). Minha consulta não possui junções, mas possui várias cláusulas where. Todas as cláusulas where são cobertas por índices apropriados, exceto aquele que possui a forma WHERE table.col_a > table.col_b. Minha dúvida é: se eu fizer um índice de table(col_a, col_b)vontade que otimize a operação maior ou preciso fazer algo específico para tornar isso eficiente?

EDIT: Adicionando consulta/explicação/ddl anonimizada por comentário de @frank-heikens.

Percebi na revisão que col_fnão possui um índice e suspeito que adicioná-lo resolveria grande parte do meu problema. Dito isto, ainda gostaria de saber a melhor maneira de otimizar para table.col_b < table.col_a.

Se ajudar, col_bquase sempre é 0,0 e col_aé sempre positivo. Meu palpite é que isso reduz o benefício de desempenho porque relativamente poucas linhas são filtradas por esta cláusula. Qualquer conselho sobre o custo/benefício relativo de um índice de múltiplas colunas ou índice de expressão seria bem-vindo.

Consulta:

explain(analyze, verbose, buffers, settings)
SELECT id, col_a, col_b, col_c, col_d, col_e
FROM  public.table
WHERE col_f = 'VALUE' AND
      col_g != 'EXCLUDED_VALUE' AND
      col_b < col_a AND
      NOT ((col_h 'EXCLUDED_VALUE_1', 'EXCLUDED_VALUE_2') OR
           col_h IS NULL)) AND
      col_e IS NOT NULL
ORDER BY col_e DESC
LIMIT 5;

                                                     QUERY PLAN                                                                                                                                                                                              
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10.61..10.62 rows=1 width=73) (actual time=0.101..0.102 rows=1 loops=1)
   Output: id, col_a, col_b, col_c, col_d, col_e
   Buffers: shared hit=2
   ->  Sort  (cost=10.61..10.62 rows=1 width=73) (actual time=0.100..0.100 rows=1 loops=1)
         Output: id, col_a, col_b, col_c, col_d, col_e
         Sort Key: table.col_e DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=2
         ->  Seq Scan on public.table  (cost=0.00..10.60 rows=1 width=73) (actual time=0.046..0.083 rows=1 loops=1)
               Output: id, col_a, col_b, col_c, col_d, col_e
               Filter: ((table.col_h IS NOT NULL) AND (table.col_e IS NOT NULL) AND ((table.col_g)::text <> 'EXCLUDED_VALUE'::text) AND (table.col_b < table.col_a) AND ((table.col_h)::text <> ALL ('{EXCLUDED_VALUE_1,EXCLUDED_VALUE_2}'::text[])) AND ((table.col_f)::text = 'VALUE'::text))
               Rows Removed by Filter: 10
               Buffers: shared hit=2
 Planning Time: 0.467 ms
 Execution Time: 0.149 ms
(15 rows)

DDL:

-- Dumped from database version 14.10 (Homebrew)

CREATE TABLE public.table (
    id uuid DEFAULT gen_random_uuid() NOT NULL,
    col_a double precision,
    col_b double precision,
    col_c boolean,
    col_d character varying,
    col_e timestamp(6) without time zone,
    col_f character varying,
    col_g character varying,
    col_h character varying,
    -- columns not referenced in the query have been elided
);

ALTER TABLE ONLY public.table
    ADD CONSTRAINT table_pkey PRIMARY KEY (id);

CREATE INDEX index_table_on_col_e ON public.table USING btree (col_e);
CREATE INDEX index_table_on_col_g ON public.table USING btree (col_g);
CREATE INDEX index_table_on_col_h ON public.table USING btree (col_h);

-- indexes for columns not referenced in the query have been elided
postgresql

2 respostas

  1. Não, mas você pode adicionar uma WHEREcláusula ao índice:

    CREATE INDEX ... WHERE cola > colb;
    • 4
  2. Best Answer

    Se ajudar, col_bquase sempre é 0,0 e col_aé sempre positivo. Meu palpite é que isso reduz o benefício de desempenho porque relativamente poucas linhas são filtradas por esta cláusula.

    Nesse caso, o suporte ao índice nãoWHERE col_a > col_b ajudará em nada nas consultas . Um índice parcial como o sugerido é a melhor opção para um filtro seletivo como esse. Mas quase todas as linhas passam no seu filtro, esse índice parcial faria mais mal do que bem. Não crie um índice parcial ou o Postgres poderá usá-lo.

    CREATE INDEX ... WHERE cola > colb;

    O Postgres não deve usá-lo para sua consulta, mas ainda pode basear-se em estimativas enganosas e estatísticas de colunas inúteis. E outros tipos de índices não ajudarão em nada. O manual sobre estatísticas estendidas :

    As estatísticas regulares, devido à sua natureza de coluna por indivíduo, não conseguem captar qualquer conhecimento sobre a correlação entre colunas.

    Infelizmente, as estatísticas estendidas para dependências funcionais também não podem ajudar atualmente. O manual sobre limitações de dependências funcionais :

    Atualmente, as dependências funcionais são aplicadas apenas ao considerar condições de igualdade simples que comparam colunas com valores constantes e INcláusulas com valores constantes.

    Portanto, este é um beco sem saída.

    Solução

    Desconhecido.

    Certamente não adicione um índice para WHERE cola > colb. Os índices são para critérios seletivos .

    Provavelmente existem outras maneiras de otimizar. Mas é impossível dizer a partir do seu plano de explicação em uma tabela fictícia com quase nenhuma linha. Precisaríamos saber o que é realmente seletivo. Forneça informações conforme as instruções aqui:

    Comece com uma consulta válida. O que você mostra tem um erro de sintaxe. Provavelmente faltando IN.

    • 4

relate perguntas