Além do B-tree padrão, PostgreSQL oferece vários tipos de índice especializados — GIN, GiST, BRIN, Hash — cada um adequado para diferentes dados e padrões de consulta, além de índices parciais e de expressão para flexibilidade. Escolher o tipo de índice correto pode melhorar drasticamente o desempenho para consultas especializadas.
CREATE INDEX idx_users_email ON users(email); -- default = B-tree
-- great for: equality (=), ranges (<, >, BETWEEN), sorting (ORDER BY), most queries
B-tree é o padrão e lida com a maioria dos casos — igualdade, intervalos e ordenação em colunas ordinárias.
-- GIN (Generalized Inverted Index) — for values CONTAINING multiple items
CREATE INDEX idx_attrs ON products USING GIN (attributes); -- JSONB
CREATE INDEX idx_tags ON posts USING GIN (tags); -- arrays
CREATE INDEX idx_search ON docs USING GIN (to_tsvector('english', body)); -- full-text
-- great for: JSONB containment, array membership, full-text search
Índices GIN são essenciais para JSONB, arrays e busca de texto completo — consultas que verificam contenção ou associação.
-- GiST — geometric, range, nearest-neighbor (e.g. PostGIS, range types)
CREATE INDEX idx_geo ON places USING GiST (location);
-- BRIN (Block Range INdex) — tiny index for LARGE tables with naturally-ordered data
CREATE INDEX idx_time ON logs USING BRIN (created_at); -- great for huge time-series
-- tiny size, efficient for large append-only/ordered data
-- Hash — equality only (=); rarely needed (B-tree handles equality too)
CREATE INDEX idx_hash ON t USING HASH (col);
-- PARTIAL index — index only SOME rows (smaller, targeted)
CREATE INDEX idx_active ON users(email) WHERE active = true; -- only active users
-- EXPRESSION index — index a computed value
CREATE INDEX idx_lower_email ON users(LOWER(email)); -- for case-insensitive lookups
SELECT * FROM users WHERE LOWER(email) = '[email protected]'; -- uses the index
Índices parciais (indexar apenas linhas relevantes — menor, mais rápido) e índices de expressão (indexar um valor computado — permitindo consultas baseadas em funções indexadas) são ferramentas poderosas e flexíveis.
Compreender a variedade de tipos de índice do PostgreSQL é conhecimento valioso para otimização de desempenho, porque o tipo de índice correto para um determinado padrão de dados e consulta pode acelerar dramaticamente consultas que o B-tree padrão trata mal ou não trata.
Enquanto B-tree (o padrão) cobre a maioria dos casos (igualdade, intervalos, ordenação), tipos especializados desbloqueiam desempenho para dados especializados: GIN é essencial para JSONB, arrays e busca de texto completo (consultas de contenção/associação — tornando os poderosos recursos JSONB e array do Postgres performáticos); GiST lida com consultas geométricas, de intervalo e de vizinho mais próximo (PostGIS geoespacial, tipos de intervalo); e BRIN fornece índices minúsculos e eficientes para grandes tabelas ordenadas naturalmente (como dados de série temporal/apenas adição — uma opção valiosa para tabelas enormes onde um B-tree completo seria custoso).
Saber qual tipo de índice se encaixa em qual cenário (GIN para JSONB/arrays/texto completo, GiST para geométrico/intervalo, BRIN para dados grandes ordenados) é fundamental para otimizar os recursos avançados do Postgres.
Igualmente valiosos são índices parciais (indexar apenas linhas relevantes — menor e mais eficiente quando você consulta um subconjunto, como usuários ativos) e índices de expressão (indexar valores computados — permitindo buscas rápidas baseadas em funções como busca de email sem diferenciação de maiúsculas/minúsculas, que um índice simples não consegue suportar).
Como a escolha de índice afeta significativamente o desempenho da consulta, e como tipos de índice especializados do Postgres (especialmente GIN para JSONB/arrays/texto completo) são o que tornam seus tipos de dados avançados performáticos, compreender os tipos de índice e quando usar cada um — incluindo índices parciais e de expressão — é conhecimento valioso e frequentemente relevante para otimizar bancos de dados PostgreSQL, distinguindo desenvolvedores que conseguem tornar Postgres rápido para padrões de consulta diversos e um aspecto fundamental para aproveitar todas as capacidades de desempenho do banco de dados.