O custo real de um índice extra no Postgres
· 7 min
Índices costumam ser tratados como almoço grátis, "lento? adiciona um índice", mas cada índice é uma segunda estrutura de dados que o banco precisa manter, não só um atalho de leitura.
Toda escrita na tabela é uma escrita a mais por índice
Um INSERT, UPDATE ou DELETE numa coluna indexada também precisa atualizar a árvore B do índice correspondente. Com N índices, o custo de escrita daquela coluna é multiplicado por N+1, aproximadamente, em tabelas com muita escrita, isso se acumula rápido e pode pesar mais do que o ganho de leitura compensa.
Espaço em disco que ninguém mede até doer
O espaço ocupado por índices compostos ou sobre colunas largas pode rivalizar com o tamanho da própria tabela. Isso afeta a taxa de acerto do cache de páginas (shared_buffers), já que mais páginas em disco competem pelo mesmo cache, um efeito indireto que deixa o resto do sistema mais lento sem nenhuma query específica parecer culpada.
Mais opções pro planner errar
Mais índices significam mais planos de execução pro planner considerar, e estatísticas desatualizadas ou dados com distribuição enviesada podem fazer ele escolher um índice pior do que o esperado. Mais opções não é estritamente melhor se as estimativas de custo por trás da escolha estiverem erradas.
Quando o índice realmente compensa
Vale checar `pg_stat_user_indexes` pra encontrar índices que nunca são usados, preferir um índice composto que cubra vários padrões de consulta em vez de vários índices estreitos, e só adicionar um índice novo depois de confirmar com `EXPLAIN ANALYZE` que a query em questão está mesmo fazendo um sequential scan que importa.