Carregando...
Sem categoria

Índices no PostgreSQL

Criando índices

Documentação oficial do PostgreSQL sobre criação de índices
https://www.postgresql.org/docs/current/sql-createindex.html

Índices múltiplos

Você pode criar um índice que utiliza várias colunas ao mesmo tempo. Ele também funcionará nos casos em que você não use todas as colunas, por exemplo:

Índice em:
(group_id, salary, age)

Ao usar estes WHEREs, estes índice será usado:
group_id
group_id E salary
group_id E salary E age

Não será usado em:
salary
salary E age
age

Será usado parcialmente quando:
group_id E age (o age não será indexado)

Escaneamento de apenas Índices

Ao criar um índice você pode chamar o INCLUDE para adicionar outras colunas no índice, fazendo com que a consulta nem mesmo acesse a tabela. Isso se chama Index-Only Scan.

Referências em Index-Only Scans

https://dev.to/elmuerte/improving-postgresql-queries-4pc1

Reindexando seu índice

Se você realiza muitos UPDATEs ou DELETEs em uma tabela com índices, estes índices ficarão degradados com o passar do tempo. Esse problema se chama index bloat. É possível estimar o quanto sua tabela está com index bloat com o software check_postgres feito pelos criadores do Bucardo.

Para resolver isso você pode chamar a operação REINDEX, porém isso fará um LOCK na tabela inteira.

A alternativa é criar um novo índice concorrentemente CREATE INDEX CONCURRENTLY com um nome diferente e apagar o índice antigo.

Referências sobre reindexação

Documentação oficial
https://www.postgresql.org/docs/current/sql-reindex.html

Documentação do Heroku
https://devcenter.heroku.com/articles/postgresql-indexes

Manutenção de índices – PostgreSQL Wiki
https://wiki.postgresql.org/wiki/Index_Maintenance

Índices para tipos RANGE no PostgreSQL

O PostgreSQL conta com uma categoria de tipo de dados chamados RANGEs.

Esses tipos de dados representam um início e um fim de qualquer tipo de dado que possa crescer, que seja escalar, tipo números inteiros, números com vírgula, hora, data, data-hora, etc.

Caso você decida usar esse tipo, aqui vai um exemplo de como criar um índice usando o tipo TSRANGE (range de timestamp sem fuso horário).

Você irá usar um índice do tipo GIST, é um índice especial, diferente da tradicional BTREE (Árvore B):

CREATE INDEX CONCURRENTLY
--o concurrently serve para que enquanto o índice for criado,
--a tabela não seja travada e escritas sejam bloqueadas
ON nome_da_tabela
USING GIST (nome_da_coluna_tsrange)
--Ou caso tenha duas colunas de timestamps,
--você também pode usar funções ou casting:
-- USING GIST (tsrange(coluna_ts_inicio,coluna_ts_fim))
;

Referências sobre índices de range

Artigo sobre tipos de índices
https://medium.com/@Alibaba_Cloud/principles-and-optimization-of-5-postgresql-indexes-btree-hash-gin-gist-and-brin-4d133e7f1842

Artigo sobre tipos de índices no PostgreSQL
https://robots.thoughtbot.com/postgres-index-types

Artigo sobre criação de índices para busca textual
https://www.compose.com/articles/indexing-for-full-text-search-in-postgresql/

Artigo no DevMedia sobre vários tipos de índices do PostgreSQL
https://www.devmedia.com.br/trabalhando-com-indices-no-postgresql/34028

Documentação PostgreSQL de tipos de dados baseados em Range (intervalo)
https://www.postgresql.org/docs/current/static/rangetypes.html

Documentação PostgreSQL de tipos de índices
https://www.postgresql.org/docs/current/static/indexes-types.html

Documentação PostgreSQL de índices para busca em texto
https://www.postgresql.org/docs/current/static/textsearch-indexes.html

Documentação PostgreSQL do CREATE INDEX
https://www.postgresql.org/docs/current/static/sql-createindex.html

Forçando índices a serem usados em uma consulta

As vezes o otimizador de consultas não usa índices.

Uma forma de forçar o seu uso é com SQLs aninhados e CTEs (vulgo WITH)

Um exemplo bacana de otimização com índices e JOINs
https://cube.dev/blog/postgresql-query-optimization/

Um exemplo bacana de otimização com índices e SORTs
https://hackernoon.com/using-simple-indexes-to-optimize-complicated-sorts-in-postgres-1761b097d97c

Quais contras um índice traz?

Apesar de aumentar muito o tempo de consulta, os índices trazem um custo:

Ocupam um bom espaço no HD
Diminuem a velocidade dos INSERTs

Quais tipos de índices existem no PostgreSQL?

Referências

Conheça os 6 tipos de índices no PostgreSQL
https://hub.packtpub.com/6-index-types-in-postgresql-10-you-should-know/

Um índice especial para tabelas com muitas colunas – Bloom Index

Esse índice é uma extensão, e é usada em casos de tabelas com muitas colunas
https://www.percona.com/blog/2019/06/14/bloom-indexes-in-postgresql/

Como procurar índices não usados?

Índices não usados ocupam espaço, processamento e deixam seus INSERTs e UPDATEs mais lentos.

Há algumas formas de procurar índices que não são usados no banco:
https://wiki.postgresql.org/wiki/Index_Maintenance

Índices e seu armazenamento em caches

https://dba.stackexchange.com/questions/25513/postgresql-index-caching

Como o índice GiST funciona?

http://patshaughnessy.net/2017/12/15/looking-inside-postgres-at-a-gist-index

Primary Key cria um Índice?

Sim, no PostgreSQL uma PRIMARY KEY automaticamente cria um índice para aquelas colunas. Geralmente seu nome é nome_da_tabela_pkey.

Você pode alterar este índice usando o ALTER INDEX:

ALTER INDEX 

Como descobrir todos os índices existentes em uma tabela?

SELECT
    tablename,
    indexname,
    indexdef
FROM
    pg_indexes
WHERE
    schemaname = 'public'
	AND tablename = 'sensor_1'
ORDER BY
    tablename,
    indexname;
Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *