Trabalho de índices no PostgreSQL

Esta resposta é sobre índices de árvore B (padrão) . Veja isso mais tarde, resposta relacionada sobre GiST, GIN etc.:

• Por que um índice GiST é usado para filtrar uma coluna não inicial?

Aqui estão os resultados da consulta de uma tabela na segunda coluna de um índice de várias colunas .
Os efeitos são fáceis de reproduzir para qualquer um. Experimente em casa.

Testei com o PostgreSQL 9.0.5 no Debian usando uma tabela de tamanho médio de um banco de dados real com 23322 linhas. Ele implementa o relacionamento n:m entre as tabelas adr(endereço) e att(atributo), mas isso não é relevante aqui. Esquema simplificado:

CREATE TABLE adratt (
  adratt_id serial PRIMARY KEY
, adr_id    integer NOT NULL
, att_id    integer NOT NULL
, log_up    timestamp NOT NULL DEFAULT (now()::timestamp)
, CONSTRAINT adratt_uni UNIQUE (adr_id, att_id)
);

A UNIQUErestrição implementa efetivamente um índice exclusivo. Repeti o teste com um índice simples para ter certeza e obtive resultados idênticos como esperado.

CREATE INDEX adratt_idx ON adratt(adr_id, att_id);

A tabela está agrupada no adratt_uniíndice e antes do teste eu executei:

CLUSTER adratt;
ANALYZE adratt;

As varreduras sequenciais para consultas (adr_id, att_id)são tão rápidas quanto possível. O índice de várias colunas ainda pode ser usado para uma condição de consulta apenas na segunda coluna de índice.

Executei as consultas algumas vezes para preencher o cache e escolhi a melhor das dez execuções para obter resultados comparáveis.

1. Consulta usando as duas colunas

SELECT *
FROM   adratt
WHERE  att_id = 90
AND    adr_id = 10;

 adratt_id | adr_id | att_id |       log_up
-----------+--------+--------+---------------------
       123 |     10 |     90 | 2008-07-29 09:35:54
(1 row)

Saída de EXPLAIN ANALYZE:

Index Scan using adratt_uni on adratt  (cost=0.00..3.48 rows=1 width=20) (actual time=0.022..0.025 rows=1 loops=1)
  Index Cond: ((adr_id = 10) AND (att_id = 90))
Total runtime: 0.067 ms

2. Consulta usando a primeira coluna

SELECT * FROM adratt WHERE adr_id = 10;

 adratt_id | adr_id | att_id |       log_up
-----------+--------+--------+---------------------
       126 |     10 |     10 | 2008-07-29 09:35:54
       125 |     10 |     13 | 2008-07-29 09:35:54
      4711 |     10 |     21 | 2008-07-29 09:35:54
     29322 |     10 |     22 | 2011-06-06 15:50:38
     29321 |     10 |     30 | 2011-06-06 15:47:17
       124 |     10 |     62 | 2008-07-29 09:35:54
     21913 |     10 |     78 | 2008-07-29 09:35:54
       123 |     10 |     90 | 2008-07-29 09:35:54
     28352 |     10 |    106 | 2010-11-22 12:37:50
(9 rows)

Saída de EXPLAIN ANALYZE:

Index Scan using adratt_uni on adratt  (cost=0.00..8.23 rows=9 width=20) (actual time=0.007..0.023 rows=9 loops=1)
  Index Cond: (adr_id = 10)
Total runtime: 0.058 ms

3. Consulta usando a segunda coluna

SELECT * FROM adratt WHERE att_id = 90;

 adratt_id | adr_id | att_id |       log_up
-----------+--------+--------+---------------------
       123 |     10 |     90 | 2008-07-29 09:35:54
       180 |     39 |     90 | 2008-08-29 15:46:07
...
(83 rows)

Saída de EXPLAIN ANALYZE:

Index Scan using adratt_uni on adratt  (cost=0.00..818.51 rows=83 width=20) (actual time=0.014..0.694 rows=83 loops=1)
  Index Cond: (att_id = 90)
Total runtime: 0.849 ms

4. Desabilite indexscan e bitmapscan

SET enable_indexscan = off;
SELECT * FROM adratt WHERE att_id = 90;

Saída de EXPLICAR ANALISAR:

Bitmap Heap Scan on adratt  (cost=779.94..854.74 rows=83 width=20) (actual time=0.558..0.743 rows=83 loops=1)
  Recheck Cond: (att_id = 90)
  ->  Bitmap Index Scan on adratt_uni  (cost=0.00..779.86 rows=83 width=0) (actual time=0.544..0.544 rows=83 loops=1)
        Index Cond: (att_id = 90)
Total runtime: 0.894 ms

SET enable_bitmapscan = off;
SELECT * FROM adratt WHERE att_id = 90;

Saída de EXPLAIN ANALYZE:

Seq Scan on adratt  (cost=0.00..1323.10 rows=83 width=20) (actual time=0.009..2.429 rows=83 loops=1)
  Filter: (att_id = 90)
Total runtime: 2.680 ms

Conclusão

Conforme esperado, o índice de várias colunas é usado para uma consulta somente na segunda coluna.
Como esperado, é menos eficiente, mas a consulta ainda é 3x mais rápida do que sem o índice.
Após desabilitar as verificações de índice, o planejador de consulta escolhe uma verificação de heap de bitmap, que executa quase tão rápido. Somente depois de desabilitar isso também, ele volta para uma verificação sequencial.

Veja outra resposta para a citação original do manual.

Atualizações desde o Postgres 9.0

Tudo ainda é basicamente verdade no Postgres 13. Mudanças mais notáveis:

• varreduras somente de índice no Postgres 9.2
• Verdadeiros índices de cobertura com a palavra- INCLUDEchave no Postgres 11
• Várias melhorias de desempenho e espaço (especialmente para índices de várias colunas) no Postgres 12
• Desduplicação no Postgres 13.

Todos a favor do desempenho do índice. (As varreduras sequenciais também ficaram mais rápidas.)

1) Sim e não.

Para uma consulta que usa ambas as colunas, por exemplo where (user_id1, user_id2) = (1,2), não importa qual índice é criado.

Para uma consulta que tem uma condição em apenas uma das colunas, por exemplo where user_id1 = 1, isso importa porque geralmente apenas as colunas "principais" podem ser usadas para uma comparação pelo otimizador. Então where user_id1 = 1seria capaz de usar o índice (user_id1, user_id2), mas não seria capaz de um índice (user_id2, user_id1) para todos os casos.

Depois de brincar com isso (depois que Erwin tão gentilmente nos mostrou uma configuração onde funciona), parece que isso depende muito da distribuição de dados da segunda coluna, embora eu ainda não tenha descoberto qual situação permite que o otimizador use colunas à direita para uma condição WHERE.

Oracle 11 que também pode (às vezes) usar colunas que não estão no início da definição do índice.

re 2) Sim, ele criará um índice

Citação do manual

Adicionar uma chave primária criará automaticamente um índice btree exclusivo na coluna ou grupo de colunas usado na chave primária.

re 2a) Primary Key (user_id1,user_id2)criará um índice em (user_id1,user_id2) (que você pode descobrir sozinho com muita facilidade simplesmente criando uma chave primária)

Eu recomendo que você leia o capítulo sobre índices no manual , basicamente responde a todas as perguntas acima.

Além disso, qual índice criar? by depesz faz um bom trabalho explicando a ordem nas colunas de índice e outros tópicos relacionados ao índice.

Anúncio 1)
Existem limitações no PostgreSQL como @a_horse_with_no_name descreve . Até a versão 8.0, os índices de várias colunas só podiam ser usados ​​para consultas na(s) coluna(s) inicial(is). Isso foi melhorado na versão 8.1. O manual atual do Postgres 14 (atualizado) explica:

Um índice de árvore B de várias colunas pode ser usado com condições de consulta que envolvem qualquer subconjunto das colunas do índice, mas o índice é mais eficiente quando há restrições nas colunas iniciais (mais à esquerda). A regra exata é que as restrições de igualdade nas colunas iniciais, mais quaisquer restrições de desigualdade na primeira coluna que não tenham uma restrição de igualdade, serão usadas para limitar a parte do índice que é verificada. As restrições nas colunas à direita dessas colunas são verificadas no índice, de modo que salvam as visitas à tabela propriamente dita, mas não reduzem a parte do índice que deve ser verificada. Por exemplo, dado um índice ligado (a, b, c)e uma condição de consulta WHERE a = 5 AND b >= 42 AND c < 77, o índice teria que ser verificado desde a primeira entrada com a= 5 e b= 42 até a última entrada coma= 5. As entradas de índice com c>= 77 seriam ignoradas, mas ainda teriam que ser examinadas. Esse índice pode, em princípio, ser usado para consultas que têm restrições be/ou csem restriçõesa — mas o índice inteiro teria que ser verificado, portanto, na maioria dos casos, o planejador preferiria uma verificação sequencial da tabela em vez de usar o índice.

Destaque meu. Posso confirmar isso por experiência.
Veja também o caso de teste adicionado minha resposta posterior aqui .

Isso é uma resposta à resposta de Jack , um comentário não faria.

Não havia índices de cobertura no PostgreSQL antes da versão 9.2. Devido ao modelo MVCC, cada tupla no conjunto de resultados deve ser visitada para verificar a visibilidade. Você pode estar pensando na Oracle.

Os desenvolvedores do PostgreSQL falam sobre "varreduras somente de índice" . Na verdade, o recurso foi lançado com o Postgres 9.2. Leia a mensagem de confirmação .
Depesz escreveu um post muito informativo no blog .

Índices de cobertura verdadeiros (atualização) são introduzidos com a INCLUDEcláusula com o Postgres 11. Relacionado:

• Uma consulta com chave primária e chaves estrangeiras é executada mais rapidamente do que uma consulta com apenas chaves primárias?

Isso também está um pouco fora:

ele se baseia no fato de que uma 'varredura completa' de um índice geralmente é mais rápida do que uma 'varredura completa' da tabela indexada devido às colunas extras na tabela que não aparecem no índice.

Conforme relatado nos comentários da minha outra resposta, também executei testes com uma tabela de dois inteiros e nada mais. O índice contém as mesmas colunas que a tabela. O tamanho de um índice btree é cerca de 2/3 do tamanho da tabela. Não o suficiente para explicar um speedup de fator 3. Fiz mais testes, baseado em sua configuração, simplificado para duas colunas e com 100.000 linhas. Na minha instalação do PostgreSQL 9.0 os resultados foram consistentes.

Se a tabela tiver colunas adicionais, o speedup com índice se torna mais substancial, mas certamente não é o único fator aqui .

Resumo

• Multi-column indexes can be used for selective criteria with queries on non-leading columns, but the speedup is only a low factor depending on table and index tuple size and visibility. Higher for wider rows, lower for larger portions of the table in the result set.

• Create an additional index with those columns first if performance is important.

• If all involved columns are included in an index (covering index) and all involved rows (per block) are visible to all transactions, you can get an "index-only scan" in Postgres 9.2 or later.

1. Estes são equivalentes? Se não, então por quê?

   Index(user_id1,user_id2) e Index(user_id2,user_id1)

Estes não são equivalentes e, de um modo geral, index(bar,baz) não será eficiente para consultas do formulárioselect * from foo where baz=?

Erwin demonstrou que esses índices podem realmente acelerar uma consulta, mas esse efeito é limitado e não é da mesma ordem que você geralmente espera que um índice melhore uma pesquisa - ele se baseia no fato de que uma 'varredura completa' de um índice geralmente é mais rápido do que uma 'varredura completa' da tabela indexada devido às colunas extras na tabela que não aparecem no índice.

Resumo: os índices podem ajudar as consultas mesmo em colunas não iniciais, mas de uma das duas maneiras secundárias e relativamente menores e não da maneira dramática que você normalmente espera que um índice ajude devido à sua estrutura btree

_{nb as duas maneiras pelas quais o índice pode ajudar são se uma varredura completa do índice for significativamente mais barata do que uma varredura completa da tabela e: 1. as pesquisas de tabela são baratas (porque são poucas ou estão agrupadas) ou 2. o índice está cobrindo , então não há pesquisas de tabela em todos os oops, veja os comentários de Erwins aqui}

banco de teste:

create table foo(bar integer not null, baz integer not null, qux text not null);

insert into foo(bar, baz, qux)
select random()*100, random()*100, 'some random text '||g from generate_series(1,10000) g;

query 1 (sem índice, atingindo 74 buffers ):

explain (buffers, analyze, verbose) select max(qux) from foo where baz=0;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=181.41..181.42 rows=1 width=32) (actual time=3.301..3.302 rows=1 loops=1)
   Output: max(qux)
   Buffers: shared hit=74
   ->  Seq Scan on stack.foo  (cost=0.00..181.30 rows=43 width=32) (actual time=0.043..3.228 rows=52 loops=1)
         Output: bar, baz, qux
         Filter: (foo.baz = 0)
         Buffers: shared hit=74
 Total runtime: 3.335 ms

consulta 2 (com índice - o otimizador ignora o índice - atingindo 74 buffers novamente):

create index bar_baz on foo(bar, baz);

explain (buffers, analyze, verbose) select max(qux) from foo where baz=0;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=199.12..199.13 rows=1 width=32) (actual time=3.277..3.277 rows=1 loops=1)
   Output: max(qux)
   Buffers: shared hit=74
   ->  Seq Scan on stack.foo  (cost=0.00..199.00 rows=50 width=32) (actual time=0.043..3.210 rows=52 loops=1)
         Output: bar, baz, qux
         Filter: (foo.baz = 0)
         Buffers: shared hit=74
 Total runtime: 3.311 ms

consulta 2 (com índice - e enganamos o otimizador para usá-lo):

explain (buffers, analyze, verbose) select max(qux) from foo where bar>-1000 and baz=0;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=115.56..115.57 rows=1 width=32) (actual time=1.495..1.495 rows=1 loops=1)
   Output: max(qux)
   Buffers: shared hit=36 read=30
   ->  Bitmap Heap Scan on stack.foo  (cost=73.59..115.52 rows=17 width=32) (actual time=1.370..1.428 rows=52 loops=1)
         Output: bar, baz, qux
         Recheck Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
         Buffers: shared hit=36 read=30
         ->  Bitmap Index Scan on bar_baz  (cost=0.00..73.58 rows=17 width=0) (actual time=1.356..1.356 rows=52 loops=1)
               Index Cond: ((foo.bar > (-1000)) AND (foo.baz = 0))
               Buffers: shared read=30
 Total runtime: 1.535 ms

Portanto, o acesso através do índice é duas vezes mais rápido, neste caso, atingindo 30 buffers - o que em termos de indexação é 'um pouco mais rápido'!, e YMMV dependendo do tamanho relativo da tabela e do índice, juntamente com o número de linhas filtradas e características de cluster dos dados da tabela

Por outro lado, as consultas na coluna inicial usam a estrutura btree do índice - neste caso, atingindo 2 buffers :

explain (buffers, analyze, verbose) select max(qux) from foo where bar=0;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=75.70..75.71 rows=1 width=32) (actual time=0.172..0.173 rows=1 loops=1)
   Output: max(qux)
   Buffers: shared hit=38
   ->  Bitmap Heap Scan on stack.foo  (cost=4.64..75.57 rows=50 width=32) (actual time=0.036..0.097 rows=59 loops=1)
         Output: bar, baz, qux
         Recheck Cond: (foo.bar = 0)
         Buffers: shared hit=38
         ->  Bitmap Index Scan on bar_baz  (cost=0.00..4.63 rows=50 width=0) (actual time=0.024..0.024 rows=59 loops=1)
               Index Cond: (foo.bar = 0)
               Buffers: shared hit=2
 Total runtime: 0.209 ms