Índices no PostgreSQL

Os índices em banco de dados são semelhantes ao índice de um livro: você olha o assunto que te interessa e vai direto a página desejada, não precisa passar pelo livro inteiro para encontrar o que você deseja.
Em banco de dados a ideia é a mesma, eles ajudam nas consultas, agilizam a pesquisa com filtros e otimizam as junções de tabelas.
Vou mostrar os índices do PostgreSQL, sua utilização, tipos e algumas consultas para verificação.

Índices no PostgreSQL 9.5:

• b-tree - índice padrão e que contempla todas as funcionalidades;
• hash - não grava no log transacional, então se utilizado após um restore tem de ser realizado o reindex nele. No manual desencoraja o uso deste;
• gist - para multi funções(busca textual, funções geográficas, ...), de acordo com o operador a ser utilizado;
• spgist - eficiente para mapeamento de disco e busca em estrutura de árvores, não utilizar se possuir valores nulos;
• gin - índice invertido, bom para mais de uma chave ou arrays e após a versão 9.5 esta muito bom para a busca textual;
• brin - para tabelas muito grandes que possuam uma coluna que tenha uma correlação natural com sua posição na tabela. ex: data em uma tabela com os posts de um blog.

Imagem 1: Índice B-Tree

Quando criado o índice ele irá armazenar o(s) dado(s) da(s) coluna(s) indexada(s) e a posição do dado na tabela de origem, duplicando a sua informação no banco e com isso ele irá utilizar mais espaço em disco.
Mesmo ele gastando espaço em disco ele se faz necessário por ser muito mais rápido de percorrer um índice com tamanho menor em relação a tabela inteira, para então retornar os dados das linhas que condizem com a busca.

Quando é bom utilizar o índice

Quando tem o relacionamento entre duas tabelas e nas consultas realiza o join entre essas tabelas é necessário relacionar de forma rápida as duas tabelas.
Para otimizar consultas em uma tabela grande e que apresente um padrão de pesquisa com uma condição.

Quando não é bom utilizar o índice

Como o índice gasta espaço de armazenamento e também recurso do servidor para se manter balanceado um índice no banco que não esta sendo utilizado só é prejudicial ao desempenho do banco. O que está sendo pouco utilizado já tem de ser avaliado se pode ser recriado de forma melhor ou se o custo de mante-lo justifica com o uso e desempenho da consulta.

Quando o índice não é utilizado

Há momentos que no explain mostra um "Seq Scan" em uma tabela que possuí índice e este não é utilizado de forma alguma.
Isso ocorre quando uma tabela é 'pequena' (isso vai depender do tamanho da tabela em relação as outras tabelas da base de dados, em geral menos de 200 linhas) é mais fácil para o banco percorrer a tabela inteira do que ler o índice e depois buscar os dados.

Índice composto (mais de uma coluna)

Em uma consulta recorrente com mais de um filtro é interessante utilizar um índice composto, porque se ele possuir as colunas pesquisadas os dados retornados serão trazidos diretamente deste.
A ordem das colunas é importante, pois a primeira coluna é por onde ele é percorrido, então essa coluna pode utiliza-lo em consultas sem as demais colunas do índice.
Há rumores que logo sairá a busca utilizando a segunda coluna do índice. Isso seria algo fantástico em termos de otimização e utilização dos índices.
Atualmente o índice composto é suportado pelos tipos: B-tree, GiST and GIN.

CREATE INDEX idx_nome_indice   
  ON tabela_nome (coluna a, coluna b, ... coluna n);

Se você possuí esses índices:
- idx_tabela_coluna_a   btree (coluna_a)
- idx_tabela_coluna_ab btree (coluna_a, coluna_b)
O primeiro índice pode ser excluído pois a consulta que utilizar a coluna_a utilizará o composto das colunas A e B.

Índice e ordenação

É possível criar o índice em uma coluna e já definir a ordem a qual ficam ordenados os dados, assim quando ocorrer uma consulta com order by na coluna x os dados já vem ordenados e o otimizador de consultas pula o passo de ordenação.

CREATE INDEX idx_nome_indice   
  ON tb_tabela (coluna x DESC NULLS FIRST);

Índice Unique

É possível criar um índice (apenas disponível no btree) e falar que o conteúdo contenha uma constraint unique, ou seja, os valores das colunas não podem repetir.
No PostgreSQL quando se é criado uma primary key, automaticamente é criado um índice unique nas colunas da PK, mas é comum encontrar índices unique iguais a PK, estes podem ser removidos.

CREATE UNIQUE INDEX idx_nome_indice ON tb_tabela (coluna x);

Índice utilizando funções

No PostgreSQL é possível criar um índice utilizando uma função, desde que a função seja imutável (para uma entrada x o retorno sempre será o mesmo).
Por exemplo uma condição da consulta sempre é passado upper(nome) ele não consegue utilizar o índice na coluna nome, então se cria um índice utilizando a função upper na coluna nome.

CREATE INDEX idx_nome_indice 
  ON tabela_nome (upper(nome));

Índice Parcial

Uma utilização muito boa é o índice parcial, onde ele é criado em relação a um subconjunto de dados da tabela baseado em uma expressão condicional.
Um uso comum que utilizamos são em tabelas grandes e que tem uma coluna com a data, então são criados índices por ano e por que as consultas costumam a ser de períodos recentes, fazendo com que seja utilizado o índice parcial.

CREATE INDEX idx_data_2015 ON tb_tabela (data) 
  WHERE ((data >= '2015-01-01 00:00:00') 
     AND (data <= '2015-12-31 23:59:59'));

CREATE INDEX idx_data_2016 ON tb_tabela (data)
  WHERE ((data >= '2016-01-01 00:00:00') 
     AND (data <= '2016-12-31 23:59:59'));

Índice para ser utilizado no LIKE 'dados%'

A utilização do LIKE é algo que nenhum DBA gosta de encontrar, mas quando se depara com isso pode ser que um índice possa otimizar a sua consulta.
Existem três situações:

• LIKE 'dado': pode vir a utilizar o índice;
• LIKE 'dado%': pode vir a utilizar o índice;
• LIKE '%dado%': não irá utilizar o índice.

Ao criar o índice, deve ser utilizado um operador de classe, o mais comum utilizado é o varchar_pattern_ops para colunas do tipo varchar.
Operadores de classe:

• varchar → varchar_pattern_ops
• char      → bpchar_pattern_ops
• text       → text_pattern_ops

Minha opinião é que se precisou utilizar o LIKE da uma lida sobre busca textual (tsearch2) que provavelmente você poderá utilizar e terá um resultado muito melhor.

CREATE INDEX idx_nome_indice 
  ON nome_tabela (coluna varchar_pattern_ops);

Índice para LIKE '%dado%'

A partir da versão 9.1 do PostgreSQL há uma extensão chamada pg_trgm, que trabalha com trigramas, ou seja, similaridade.

Utilizando os operadores de classe dessa extensão é possível utilizar índices do tipo GIN ou GIST para serem utilizados no %LIKE%.

• GIN → gin_trgm_ops
• GIST → gist_trgm_ops
  

CREATE INDEX idx_nome_indice 
  ON nome_tabela USING gin (coluna gin_trgm_ops);

Índice clusterizado

Ao clusterizar uma tabela, ela será ordenada fisicamente baseada na informação do índice. Os novos dados ou atualizações que ocorrerem após clusterizada a tabela serão adicionados ao final da tabela. Então pode ser necessário refazer o cluster de tempos em tempo para reordenar os dados fisicamente e melhorar o desempenho.

CLUSTER nome_tabela USING indice_nome;

Utilização dos índices

A forma padrão de verificação dos seus índices é utilizar as views de sistema:

• pg_stat_all_indexes: tem todos os índices;
• pg_stat_sys_indexes: tem os índices das tabelas de sistema;
• pg_stat_user_indexes: tem os índices das tabelas dos usuários.

Essas views mostram:

• relid: identificador da tabela referenciada;
• indexrelid: identificador do índice;
• schemaname: nome do esquema onde se encontra a tabela e o índice;
• relname: nome da tabela;
• indexrelname: nome do índice;
• idx_scan: quantidade de utilizações do índice; 
• idx_tup_read: quantidade de linhas lidas do índice;
• idx_tup_fetch: quantidade de linhas da tabela lidas pelo índice.

O importante é verificar a utilização dos índices. Um índice com idx_scan=0 nunca foi utilizado e tem um custo de manutenção e espaço em disco.  Ele pode ser excluído.

Consultas que utilizo no trabalho para a verificação dos índices

1) Consulta para verificar os índices de uma tabela especifica:

SELECT schemaname AS Esquema,
       relname AS Tabela,
       indexrelname AS Indice,
       Pg_size_pretty(Pg_relation_size(i1.indexrelid :: bigint)) AS tamanho,
       (i1.idx_scan /
          (SELECT SUM(i2.idx_scan)
           FROM pg_stat_all_indexes i2
           WHERE i1.relname = i2.relname) * 100) :: VARCHAR(4) || '%' AS Uso
FROM pg_stat_all_indexes i1
INNER JOIN pg_index idx ON idx.indexrelid = i1.indexrelid
WHERE i1.relname IN ('tabela' )-- ##Alterar a tabela
  AND idx.indisprimary = 'n'
ORDER BY (i1.idx_scan /
            (SELECT SUM(i2.idx_scan)
             FROM pg_stat_all_indexes i2
             WHERE i1.relname = i2.relname) * 100); --Order By no USO

2) Tamanho de cada índice NUNCA utilizado e que NÃO é PK

SELECT sai.schemaname AS esquema,
       sai.relname AS tabela,
       sai.indexrelname AS nome_indice,
       sai.schemaname||'.'||sai.indexrelname AS esq_indice,
       pg_size_pretty(pg_relation_size(sai.indexrelid::bigint)) AS tamanho
FROM pg_stat_all_indexes sai
INNER JOIN pg_index idx ON idx.indexrelid = sai.indexrelid
WHERE sai.idx_scan = 0
  AND sai.schemaname NOT IN ('pg_toast','pg_catalog')
  AND idx.indisprimary='n'
ORDER BY pg_relation_size(sai.indexrelid::bigint) DESC;

3) Índices duplicados

SELECT pg_stat_user_indexes.schemaname AS nome_do_esquema,
       pg_stat_user_indexes.relname AS nome_da_tabela,
       pg_attribute.attname AS nome_do_atributo,
       pg_stat_user_indexes.indexrelname AS nome_do_indice,
       pg_size_pretty(pg_relation_size(indexrelid::bigint)) AS tamanho,
       CASE pg_index.indisprimary
           WHEN 't' THEN 'Sim'
           ELSE 'Nao'
       END AS indice_na_chave_primaria
FROM pg_index
JOIN pg_stat_user_indexes USING (indexrelid)
JOIN
  (SELECT pg_index.indrelid,
          pg_index.indkey,
          count(*)
   FROM pg_index
   JOIN pg_stat_user_indexes USING (indexrelid)
   GROUP BY pg_index.indrelid,
            pg_index.indkey
   HAVING count(*)>1) ind_dup ON pg_index.indrelid=ind_dup.indrelid
AND pg_index.indkey=ind_dup.indkey
JOIN pg_attribute ON pg_attribute.attrelid=ind_dup.indrelid
AND pg_attribute.attnum=SOME(pg_index.indkey)
ORDER BY pg_stat_user_indexes.schemaname,
         tamanho DESC,
         pg_stat_user_indexes.relname,
         pg_index.indisprimary='t' DESC;

4) Índices com alta probabilidade de serem removidos.

Consulta super interessante enviada pelo Bruno Silva na lista de e-mail pgbr-geral no dia 26 de agosto de 2014 às 19h09.
Funciona no PostgreSQL 9 ou superior.

Apresenta os índices e as informações:

• Never Used Indexes - Índices nunca utilizados;
• Low Scans, High Writes - Índices pouco utilizados e com muita escrita na tabela, então ele é mais custoso do que eficiente;
• Seldom Used Large Indexes - Índices grandes e que raramente são utilizados;
• High-Write Large Non-Btree - Índice grande que não é btree e tem muitas escritas. Cuidado nesse caso que pode ser um índice utilizado por exemplo na busca textual e que deve existir para manter a regra de negócio funcionando.

WITH table_scans AS
  (SELECT relid,
          tables.idx_scan + tables.seq_scan AS all_scans,
          (tables.n_tup_ins + tables.n_tup_upd + tables.n_tup_del) AS writes,
          pg_relation_size(relid) AS table_size
   FROM pg_stat_user_tables AS tables),
     all_writes AS
  (SELECT sum(writes) AS total_writes
   FROM table_scans),
     indexes AS
  (SELECT idx_stat.relid,
          idx_stat.indexrelid,
          idx_stat.schemaname,
          idx_stat.relname AS tablename,
          idx_stat.schemaname || '.' || idx_stat.indexrelname AS indexname,
          idx_stat.idx_scan,
          pg_relation_size(idx_stat.indexrelid) AS index_bytes,
          indexdef ~* 'USING btree' AS idx_is_btree
   FROM pg_stat_user_indexes AS idx_stat
   JOIN pg_index USING (indexrelid)
   JOIN pg_indexes AS indexes ON idx_stat.schemaname = indexes.schemaname
   AND idx_stat.relname = indexes.tablename
   AND idx_stat.indexrelname = indexes.indexname
   WHERE pg_index.indisunique = FALSE),
     index_ratios AS
  (SELECT schemaname,
          tablename,
          indexname,
          idx_scan,
          all_scans,
          round((CASE WHEN all_scans = 0 THEN 0.0::NUMERIC ELSE idx_scan::NUMERIC/all_scans * 100 END),2) AS index_scan_pct,
          writes,
          round((CASE WHEN writes = 0 THEN idx_scan::NUMERIC ELSE idx_scan::NUMERIC/writes END),2) AS scans_per_write,
          pg_size_pretty(index_bytes) AS index_size,
          pg_size_pretty(table_size) AS table_size,
          idx_is_btree,
          index_bytes
   FROM indexes
   JOIN table_scans USING (relid)),
     index_groups AS
  (SELECT 'Never Used Indexes' AS reason,
          *,
          1 AS grp
   FROM index_ratios
   WHERE idx_scan = 0
     AND idx_is_btree
   UNION ALL SELECT 'Low Scans, High Writes' AS reason,
                    *,
                    2 AS grp
   FROM index_ratios
   WHERE scans_per_write <= 1
     AND index_scan_pct < 10
     AND idx_scan > 0
     AND writes > 100
     AND idx_is_btree
   UNION ALL SELECT 'Seldom Used Large Indexes' AS reason,
                    *,
                    3 AS grp
   FROM index_ratios
   WHERE index_scan_pct < 5
     AND scans_per_write > 1
     AND idx_scan > 0
     AND idx_is_btree
     AND index_bytes > 100000000
   UNION ALL SELECT 'High-Write Large Non-Btree' AS reason,
                    index_ratios.*,
                    4 AS grp
   FROM index_ratios,
        all_writes
   WHERE (writes::NUMERIC / (total_writes + 1)) > 0.02
     AND NOT idx_is_btree
     AND index_bytes > 100000000
   ORDER BY grp,
            index_bytes DESC)
SELECT reason,
       schemaname,
       tablename,
       indexname,
       index_scan_pct,
       scans_per_write,
       index_size,
       table_size
FROM index_groups;

Referências

http://www.postgresql.org/docs/current/static/indexes.html

Edit

08/03/16 - Corrigido o exemplo do índice com função

08/03/16 - Adicionado os exemplos de operadores de classe para o Like%

08/03/16 - Adicionado o tópico sobre o pg_trgm