Início / dba / Perguntas / 342758
Accepted
Edigest2
Asked: 2024-10-03 17:14:58 +0800 CST

Uso incorreto de índice em LEFT JOINs (índice gist em vez de índice dedicado)

  • 772

No Postgres 16, notei o seguinte comportamento "errado" de uso de índice:

Tenho uma tabela (350000 registros) com uma chave primária composta:

CREATE TABLE IF NOT EXISTS public.mytable
(
    arflgart integer NOT NULL,
    arkeyart character varying(10) COLLATE pg_catalog."default" NOT NULL,
    ...
    CONSTRAINT pk_mytable PRIMARY KEY (arflgart, arkeyart),
    ...

idx_keyart_flgartHá também um índice BTREE (arkeyart,arflgart)e um índice gist

CREATE INDEX IF NOT EXISTS idx_arkeyart ON public.mytable 
USING gist (arkeyart COLLATE pg_catalog."default" gist_trgm_ops);

Quando fiz consultas em outra tabela com um LEFT JOINto mytablenas duas chaves primárias:

SELECT *
FROM another_table tab2
LEFT JOIN mytable ON tab2.field1=arflgart AND tab2.field2 = arkeyart
WHERE tab2.field3 > 0

A execução da consulta demora muito porque NÃO usa o índice da chave primária ou o idx_keyart_flgart, mas usa o índice gist idx_arkeyart?!?

...
Index Scan using idx_arkeyart on mytable
Index Cond: ((arkeyart)::text = (another_table.field2)::text)
Filter: (arflgart = another_table.field1)
Buffers: shared hit=14,224,232
...

Se eu remover o índice gist, a consulta será imediata.

*** Por que o Postgres não reconhece o índice correto a ser usado?

Este é o plano de execução completo:

"Gather  (cost=1000.71..36112.85 rows=59828 width=56) (actual time=6.490..34806.715 rows=60758 loops=1)"
"  Workers mytable: 3"
"  Workers Launched: 3"
"  Buffers: shared hit=14416778"
"  ->  Nested Loop Left Join  (cost=0.71..29130.05 rows=19299 width=56) (actual time=9.918..34723.521 rows=15190 loops=4)"
"        Buffers: shared hit=14416778"
"        ->  Nested Loop Left Join  (cost=0.42..22483.10 rows=19299 width=49) (actual time=0.408..236.928 rows=15190 loops=4)"
"              Buffers: shared hit=192546"
"              ->  Parallel Seq Scan on ba_salart001 ba_salart  (cost=0.00..12263.89 rows=19299 width=49) (actual time=0.029..80.870 rows=15190 loops=4)"
"                    Filter: (saqtagia <> '0'::numeric)"
"                    Rows Removed by Filter: 108773"
"                    Buffers: shared hit=10258"
"              ->  Index Only Scan using pk_another_table001 on another_table001 another_table  (cost=0.42..0.53 rows=1 width=26) (actual time=0.009..0.009 rows=1 loops=60758)"
"                    Index Cond: ((ulcodkey = (ba_salart.saunilog)::text) AND (field1 = ba_salart.saflgart) AND (field2 = (ba_salart.sakeyart)::text))"
"                    Heap Fetches: 7"
"                    Buffers: shared hit=182288"
"        ->  Index Scan using idx_arkeyart on mytable001 mytable  (cost=0.28..0.33 rows=1 width=22) (actual time=2.268..2.268 rows=1 loops=60758)"
"              Index Cond: ((arkeyart)::text = (another_table.field2)::text)"
"              Filter: (arflgart = another_table.field1)"
"              Buffers: shared hit=14224232"
"Planning:"
"  Buffers: shared hit=890"
"Planning Time: 14.776 ms"
"Execution Time: 34813.134 ms"
postgresql

1 respostas

  1. Best Answer

    O planejamento para índices GiST é geralmente meio ruim. Como eles são tão extensíveis, o planejador não tem muita percepção do que acontece dentro deles para fazer um planejamento melhor (embora eu tenha certeza de que ainda poderia ser melhorado um pouco, seja genericamente ou especificamente para gist_trgm_ops, com esforço suficiente). Isso geralmente não é um grande problema, pois normalmente você não tem um índice GiST mais outros índices ao mesmo tempo, que suportam os mesmos operadores.

    Como a varredura de índice só deve retornar uma linha e como nenhuma linha é removida pelo filtro, isso significa que há pouco sentido em usar um índice de várias colunas quando um índice de coluna única já é seletivo o suficiente para chegar a uma linha. Então, como não há sentido em usar um índice de várias colunas para obter seletividade desnecessária e como o planejamento do GiST é meio ruim, ele decide usar o índice GiST de coluna única.

    A capacidade de usar gist_trgm_ops para dar suporte à igualdade não foi adicionada ao pg_trgm até a versão '1.6'. Eu tinha algumas dúvidas sobre fazer isso na época, só por esse motivo -- ele de fato dá suporte à igualdade, mas de uma forma inferior, então declarar o suporte pode representar um incômodo atraente para o planejador.

    Você pode remover a extensão pg_trgm (em cascata para remover o índice de suporte) e recriá-la com create extension pg_trgm version '1.5';. Depois, reconstrua os índices e eles não serão mais usados ​​para consultas de igualdade. Observe que a mudança de 1.5 para 1.6 não fez nada além de adicionar esses operadores de igualdade, então reverter isso não deve causar nenhum dano. No entanto, um dump não memorizará qual versão estava em uso, então restaurar de um dump voltaria a usar a versão mais recente disponível e criaria o problema novamente.

    Alternativamente, você pode executar alter operator family gist_trgm_ops USING gist drop operator 11 (text, text);, mas acho que isso também apresenta riscos de dump/restore e pg_upgrade.

    • 0

relate perguntas