Index: Melhoria de desempenho

Published in

Bemobi

8 min readOct 18, 2020

Seguindo na pegada do último post que falei sobre CQRS e seus bancos de leitura e de escrita, agora irei tocar em outro assunto interessante sobre bancos, porém, algo já mais que batido, mesmo assim sempre gera dor de cabeça na gente….nosso querido index.
Na realidade tentarei colocar aqui um compilado de tudo que já vi sobre o tema de forma bastante sucinta, já que sempre que tenho que criar um índice eu recorro a internet das coisas em busca desse conteúdo.

O que é e para que serve um Índice

> Índice, no contexto da estrutura de dados, é uma referência associada a uma chave, que é utilizada para fins de otimização, permitindo uma localização mais rápida de um registro quando efetuada uma consulta (wikipedia)

Dito isso, antes de chegarmos nos detalhes sobre índice, acho bacana começar pelo começo, ou quase, porque já estou partindo do princípio que ERD/MER não é novidade pra você, então vamos começar pela modelagem do nosso banco. Se você chegou aqui e termos como: Normalização (Normalized) e Desnormalização (Denormalized) de dados soam estranho, sugiro que leia esse conteúdo:

Mesmo assim, em algum momento, podemos nos deparar com situações esquisitas e nos perguntar se estamos indo no caminho certo (justamente por estar deixando o modelo meio esquizofrênico rs) e acredito que conhecimentos sobre Star Schema, Snowflake Schema, Master Data possam ajudar.

Depois desse passo, estaremos prontos para modelar melhor nosso banco, mas existem vários propósitos para um banco, por exemplo, atender a demanda de um Data Lake, Data Warehouse e etc

Tem um post bastante completo sobre modelagem no devmedia:

https://www.devmedia.com.br/guia/modelagem-de-dados/34654

Enfim, espero que tenha notado que índice não vai ser a solução mágica se o modelo também não for eficiente para o seu propósito, mas…. vamos aos índices! zo/

ou não….

Antes de adentramos nas profundezas desse mundo caótico (ao menos pra mim) esteja certo que você conhece o mínimo que seu banco tem a oferecer sobre o tema, ou seja, que você saiba os tipos de índice oferecidos pela ferramenta, como e onde usá-los. No Postgres temos:

Hash
GIN
GIST
R-tree
B-tree

Cada tipo de índice é mais apropriado para um determinado tipo de consulta devido ao algoritmo utilizado. Por padrão, o comando CREATE INDEX cria um índice B-tree, adequado para as situações mais comuns. Sugiro que entenda bem a árvore B antes de criar índices ou usar outro tipo de índice. Você pode ler sobre os outros aqui:

B-tree

Como já dito antes, o tipo default que será usado, uma árvore auto balanceada permitem pesquisas, inserções, exclusões e acesso sequencial em tempo logarítmico [O (log n)]. O foco aqui não vai ser desvendar os mistérios de uma B-tree, mas tem muito conteúdo bom por aí já:

Indexação

Agora sim, inicia-se o sofrimento! XD

Digo isso porque além de nem sempre ser fácil “testar” um índice, no final…o Postgres pode rir na sua cara e falar que não vai usar, e pronto. =/

Mas, vamos lá. Existem 5 coisas básicas que temos sobre index no Postgres:

Índices não blocantes (concorrentes)
No momento da construção dos índices, a tabela é bloqueada automaticamente, mas podemos evitar esse lock usando a palavra reservada CONCURRENTLY.
CREATE INDEX CONCURRENTLY index_name ON table_name using btree (column);
DROP INDEX CONCURRENTLY index_name;
Single-column
Um índice de coluna única é um índice baseado nos valores de uma coluna de uma tabela.
Multicolumn
Um índice com várias colunas é um índice baseado nos valores de várias colunas de uma tabela. (link)
Multicolumn para um index-scan-only (covering indexes)
Mesmo que o multicolumn, mas feito para que não precise fazer lookup na tabela origem evitando assim I/O de disco e/ou leitura na heap.
Parcial
Um índice com algum predicado.

Tips

Sempre que tenho que criar um índice novo, o que costuma ser uma tarefa bem esporádica, tenho que correr por ai procurando material de apoio pra não fazer besteira! Pois bem, pensando nisso que este post surgiu… =)
Agora vem as dicas que sempre releio antes de iniciar os trabalhos com índice.

1 — Se o índice composto possuir uma PK o mesmo será unique por padrão; (link)

2 — Índice em tabelas temporárias pode ser pior que usar Common Table Expression (CTE), with por exemplo. Uma CTE é muito parecida com uma tabela derivada que não é armazenada como um objeto e que existe apenas durante a execução da consulta; (link)

3 — OR pode fazer o não uso de índice, se for este o caso, tente usar UNION; (link)

4 — De preferência a “forçar” o uso de BITMAP index, ou seja, criar índices de campo único (Single-column) mesmo que o desempenho não seja o melhor, mas em aplicações grandes, com muitos índices, pode fazer a diferença; (link)

5 — Fique de olho em tuplas mortas; (pgstattuple)

6 — Fique de olho na “densidade da folha”; (avg_leaf_density)

7 — Fique de olho no tamanho do seu índice; (link)

8 — Fique de olho se está com algum overhead no insert/update por conta dos índices;

9 — Dê preferência a uma política de expurgo do que index parcial, que, apesar do ganho de disco/memória, perde-se no tempo de insert/update ;(link)

10 — EXPLAIN! EXPLAIN! EXPLAIN! EXPLAIN! EXPLAIN! (link) (deveria ser a primeira dica rsrs) PgAdmin faz isso muito bem, mas você pode gerar um json e jogar em algum ferramenta para gerar o gráfico; (explain json)

11 — Wildcard (%) no início vai fazer fullscan; (link)

12 — Usar include no index pode parecer bom, mas também pode ser uma armadilha, cuidado para não abusar e “corromper” a tabela impedindo insert/update; (link)

13 — enable_seqscan = off/false/0 serve bastante para te dar uma dica se o index está no lugar “certo” e o banco que achou melhor não usar-lo; (link)

14 — A ordem das colunas no index importa; (link)

15 — Monitore se tem índice não sendo usado; (link)

16 — Usar função no predicado pode ocasionar um fullscan; (link)

17 — Garanta o uso adequado do VACUUM. Uma forma de recuperar espaço em disco devido a registros atualizados ou deletados; (link e aqui )

18 — Evite manipulação de índice com AWS DMS ligado; (link)

19 — Para campos tipo texto pode ser interessante, e mais rápido, usar hash (no index e na comparação, claro); (link).

20 — Table partitioning pode suprir a necessidade do index em alguns casos; (link)

21 — Fique atento a fragmentação do index; (link)

22 — Entenda um pouco sobre densidade e seletividade, Mais seletiva (total de valores distinto / total) — densidade; (link)

23 — Assim como VACUUM, tenha um política para regerar as estatísticas; (link)

24 — Com a evolução dos dados o planejamento do banco será afetado, fique atento a isso; (pg_catalog.pg_stat_user_indexes)

25 — Partial index pode escapar por algum motivo do Postgres, mas você pode tentar ajuda-lo usando no predicado na mesma condição usada no índice; (link)

26 — Explain BUFFERS — Isso pode ser útil quando você está tentando entender o uso do cache; (link)

27 — Ferramentas para visualizar o explain em grafico usando EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)

28 — Ao criar um índice o query plan muda e isso pode fazer com que um outro índice caia em desuso (Alguém teria um link?)

29 — Usar INDEX DESC NULLS LAST onde o valor do topo do conjunto é o que importa, como no uso do MAX, pode fazer muita diferença. (docs)(link)

30 — HOT(Heap Only Tuple) update `n_tup_hot_up` deve cresce tão rápido quanto n_tup_upd(a contagem de atualizações) (link)

Experimento

Hora de tentar consolidar toda essa leitura e verificar na prática o que funciona eo que não funciona. O experimento consiste em testar alguns meios de se criar índices e como eles afetariam um modelo mais normalizado e outro menos, tanto para leitura quanto para alterações no modelo.

Criei 2 modelos com a mesma massa respeitando as características de cada um.

+ Normalizado
+ Desnormalizado

Para cada modelo criei 4 questions:

Quais os clientes ativos do último ano?
Qual marca vendeu mais?
Como estão as vendas?
Qual o detalhamento da compra do cliente?

Para cada question criei 4 “estratégias” de indexação, são elas:

Criando index somente em uma coluna tentando aproveitar os BITMAP INDEX; (INDEX 1)
Criando index, quando possível, usando mais de uma coluna; (INDEX 2)
Índices tirando o máximo do include; (INDEX 3)
Transformando em index parcial quando possível; (INDEX 4)

(Obs: Cada approach rodando em um banco próprio, sem interferir um no outro)

Rodei um teste massivo de select, update e insert para cada cenário.

Como o esperado, index parcial acaba ficando menor em disco mesmo, mas o que surpreendeu foi seu ótimo desempenho para inserts e updates. Também ficou evidente como o index pode inflar bastante nosso banco saindo de 674 MB para 1653 MB na indexação por muitas colunas (INDEX 3).