Início / dba / Perguntas / 125413
Accepted
Clodoaldo
Asked: 2016-01-07 09:48:46 +0800 CST

Índice não usado com = ANY() mas usado com IN

  • 772

A tabela tpossui dois índices:

create table t (a int, b int);
create type int_pair as (a int, b int);
create index t_row_idx on t (((a,b)::int_pair));
create index t_a_b_idx on t (a,b);

insert into t (a,b)
select i, i
from generate_series(1, 100000) g(i)
;

Nenhum índice é usado com o ANYoperador:

explain analyze
select *
from t
where (a,b) = any(array[(1,1),(1,2)])
;
                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1693.00 rows=1000 width=8) (actual time=0.042..126.789 rows=1 loops=1)
   Filter: (ROW(a, b) = ANY (ARRAY[ROW(1, 1), ROW(1, 2)]))
   Rows Removed by Filter: 99999
 Planning time: 0.122 ms
 Execution time: 126.836 ms

Mas um deles é usado com o INoperador:

explain analyze
select *
from t
where (a,b) in ((1,1),(1,2))
;
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Index Only Scan using t_a_b_idx on t  (cost=0.29..8.32 rows=1 width=8) (actual time=0.028..0.029 rows=1 loops=1)
   Index Cond: (a = 1)
   Filter: ((b = 1) OR (b = 2))
   Heap Fetches: 1
 Planning time: 0.161 ms
 Execution time: 0.066 ms

Ele usa o índice de registro se o registro for convertido para o tipo correto:

explain analyze
select *
from t
where (a,b)::int_pair = any(array[row(1,1),row(1,2)])
;
                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 Index Scan using t_row_idx on t  (cost=0.42..12.87 rows=2 width=8) (actual time=0.106..0.126 rows=1 loops=1)
   Index Cond: (ROW(a, b)::int_pair = ANY (ARRAY[ROW(1, 1), ROW(1, 2)]))
 Planning time: 0.208 ms
 Execution time: 0.203 ms

Por que o planejador não usa o índice de não registro para o ANYoperador como o usa para o INoperador?

postgresql index

1 respostas

  1. Best Answer

    Internamente, existem duas formas separadas de IN, e também duas formas separadas da ANYconstrução.

    Um de cada, tomando um set , é equivalente ao outro e expr IN (<set>)também leva ao mesmo plano de consulta expr = ANY(<set>)que pode usar um índice simples. Detalhes:

    Consequentemente, as duas consultas a seguir são equivalentes e ambas podem usar o índice simples t_a_b_idx(que também pode ser a solução se você estiver tentando fazer com que sua consulta use o índice):

    EXPLAIN ANALYZE
SELECT *
FROM t
WHERE (a,b) = ANY(VALUES (1,1),(1,2));

    Ou:

    ...
WHERE (a,b) IN (VALUES (1,1),(1,2));

    Idêntico para ambos:

                                                            QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.33..16.71 rows=1 width=8) (actual time=0.101..0.101 rows=0 loops=1)
   ->  Unique  (cost=0.04..0.05 rows=2 width=8) (actual time=0.068..0.070 rows=2 loops=1)
         ->  Sort  (cost=0.04..0.04 rows=2 width=8) (actual time=0.067..0.068 rows=2 loops=1)
               Sort Key: "*VALUES*".column1, "*VALUES*".column2
               Sort Method: quicksort  Memory: 25kB
               ->  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=8) (actual time=0.005..0.005 rows=2 loops=1)
   ->  Index Only Scan using t_plain_idx on t  (cost=0.29..8.32 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=2)
         Index Cond: ((a = "*VALUES*".column1) AND (b = "*VALUES*".column2))
         Heap Fetches: 0
 Planning time: 4.080 ms
 Execution time: 0.202 ms

    No entanto , isso não pode ser passado facilmente para uma função, pois não há "variáveis ​​de tabela" no Postgres. O que leva ao problema que iniciou este tópico:

    Existem várias soluções alternativas para esse problema. Uma delas é a resposta alternativa que adicionei lá. Alguns outros:

    A segunda forma de cada um é diferente: ANYleva uma matriz real , enquanto INleva uma lista separada por vírgulas de valores .

    Isso tem consequências diferentes para digitar a entrada. Como podemos ver na EXPLAINsaída da pergunta, este formulário:

    WHERE (a,b) = ANY(ARRAY[(1,1),(1,2)]);

    é visto como uma abreviação para:

    ROW(a, b) = ANY (ARRAY[ROW(1, 1), ROW(1, 2)])

    E os valores ROW reais são comparados. Postgres atualmente não é inteligente o suficiente para ver que o índice no tipo composto t_row_idxé aplicável. Também não percebe que o índice simples t_a_b_idxtambém deve ser aplicável.

    Um elenco explícito ajuda a superar essa falta de inteligência:

    WHERE (a,b)::int_pair = ANY(ARRAY[(1,1),(1,2)]::int_pair[]);

    A conversão do operando correto ( ::int_pair[]) é opcional (embora seja preferível para desempenho e para evitar ambiguidades). Uma vez que o operando esquerdo tenha um tipo conhecido, o operando direito é forçado de "registro anônimo" para um tipo correspondente. Só então, o operador é definido de forma inequívoca. E o Postgres escolhe os índices aplicáveis ​​com base no operador e no operando esquerdo . Para muitos operadores que definem um COMMUTATOR, o planejador de consulta pode inverter os operandos para trazer a expressão indexada para a esquerda. Mas isso não é possível com a ANYconstrução.

    Relacionado:

    .. os valores são tomados como elementos e o Postgres é capaz de comparar valores inteiros individuais, como podemos ver na EXPLAINsaída mais uma vez:

    Filter: ((b = 1) OR (b = 2))

    Portanto, o Postgres descobre que o índice simples t_a_b_idxpode ser usado.

    Consequentemente, haveria outra solução para o caso particular do exemplo : como o tipo composto personalizado int_pairno exemplo é equivalente ao tipo de linha da tprópria tabela, poderíamos simplificar:

    CREATE INDEX t_row_idx2 ON t ((t.*));

    Sintaxe equivalente mais curta:

    CREATE INDEX t_row_idx2 ON t ((t));

    Mas a primeira variante é mais segura. A segunda variante resolveria para a coluna se uma coluna com o mesmo nome existisse.

    Então esta consulta usaria o índice sem qualquer conversão mais explícita:

    EXPLAIN ANALYZE
SELECT *
FROM   t
WHERE  t = ANY(ARRAY[(1,1),(1,2)]);

                                                          QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t  (cost=40.59..496.08 rows=1000 width=8) (actual time=0.19
1..0.191 rows=0 loops=1)
   Recheck Cond: (t.* = ANY (ARRAY[ROW(1, 1), ROW(1, 2)]))
   ->  Bitmap Index Scan on t_row_idx2  (cost=0.00..40.34 rows=1000 width=0) (actual time=0.188..0.188 rows=0 loops=1)
         Index Cond: (t.* = ANY (ARRAY[ROW(1, 1), ROW(1, 2)]))
 Planning time: 2.575 ms
 Execution time: 0.267 ms

    Mas os casos de uso típicos não poderão utilizar o tipo implicitamente existente da linha da tabela.

    • 21

relate perguntas