PostgreSQL: Índices do PostgreSQL: Internos e Otimização

Última atualização: 2026-08-26

1. O Que Você Vai Aprender

• Os 6 tipos de índice do PostgreSQL: B-Tree / Hash / GIN / GiST / BRIN / SP-GiST
• CREATE INDEX / UNIQUE INDEX / CONCURRENTLY
• Índices parciais (condição WHERE) — uma especialidade do PostgreSQL
• Índices de expressão
• Índices compostos e a regra do prefixo mais à esquerda
• Leitura de planos de execução com EXPLAIN / EXPLAIN ANALYZE
• Manutenção de índices com REINDEX
• Cenários comuns onde os índices não são utilizados

2. A História

Bob é o DBA de uma plataforma de e-commerce. Os usuários relatam que a página de busca de produtos está cada vez mais lenta — uma varredura completa em uma coluna JSONB faz com que as consultas levem 3 segundos.

Depois que Bob adiciona um índice GIN na coluna JSONB, a busca cai para 300 ms. Ele então percebe que as consultas de usuários ativos também estão lentas, mas 90% dos usuários já foram desativados, então criar um índice na tabela inteira desperdiça espaço. Ele usa um índice parcial que indexa apenas as linhas onde status = 'active', reduzindo o tamanho do índice em 80% e tornando as consultas mais rápidas.

3. Conceito: Visão Geral dos Tipos de Índice

(1) Os Seis Tipos de Índice

▶ Exemplo: Visualizar Índices Existentes em uma Tabela

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'orders';

 indexname              | indexdef
------------------------+--------------------------------------------------
 orders_pkey            | CREATE UNIQUE INDEX ... ON orders USING btree (order_id)
 idx_orders_customer_id | CREATE INDEX ... ON orders USING btree (customer_id)

(2) Decisão de Seleção do Tipo de Índice

flowchart TD
    A{Tipo de consulta?} -->|Igualdade/intervalo/ordenação| B[B-Tree]
    A -->|Igualdade simples| C[Hash]
    A -->|Array/JSONB contém| D[GIN]
    A -->|Espacial/geométrico/mais próximo| E[GiST]
    A -->|Varredura sequencial em tabela grande| F[BRIN]
    A -->|Estrutura de partição irregular| G[SP-GiST]

    B --> B1["Tipo de índice padrão<br/>90% dos casos"]
    C --> C1["Raramente usado<br/>B-Tree geralmente é melhor"]
    D --> D1["JSONB @> ?<br/>array @> <br/>tsvector @@ "]
    E --> E1["PostGIS<br/>sobreposição de intervalo"]
    F --> F1["Série temporal com +10M linhas<br/>tamanho minúsculo"]
    G --> G1["Prefixo de telefone<br/>roteamento IP"]

    style A fill:#e1f5fe
    style B fill:#c8e6c9
    style D fill:#fff9c4
    style F fill:#fff9c4

flowchart TD
    A{Tipo de consulta?} -->|Igualdade/intervalo/ordenação| B[B-Tree]
    A -->|Igualdade simples| C[Hash]
    A -->|Array/JSONB contém| D[GIN]
    A -->|Espacial/geométrico/mais próximo| E[GiST]
    A -->|Varredura sequencial em tabela grande| F[BRIN]
    A -->|Estrutura de partição irregular| G[SP-GiST]

    B --> B1["Tipo de índice padrão<br/>90% dos casos"]
    C --> C1["Raramente usado<br/>B-Tree geralmente é melhor"]
    D --> D1["JSONB @> ?<br/>array @> <br/>tsvector @@ "]
    E --> E1["PostGIS<br/>sobreposição de intervalo"]
    F --> F1["Série temporal com +10M linhas<br/>tamanho minúsculo"]
    G --> G1["Prefixo de telefone<br/>roteamento IP"]

    style A fill:#e1f5fe
    style B fill:#c8e6c9
    style D fill:#fff9c4
    style F fill:#fff9c4

4. Conceito: Índice B-Tree

(1) Características e Casos de Uso do B-Tree

O B-Tree é o tipo de índice padrão do PostgreSQL; ele suporta consultas de igualdade, intervalo, ordenação, IS NULL e LIKE com prefixo.

▶ Exemplo: Criar um Índice B-Tree

CREATE INDEX idx_orders_amount ON orders (amount);

CREATE INDEX idx_orders_date_amount ON orders (order_date, amount DESC);

Output:

CREATE TABLE

▶ Exemplo: Índice UNIQUE

CREATE UNIQUE INDEX idx_users_email ON users (email);

Output:

CREATE TABLE

Um índice UNIQUE garante tanto a unicidade dos dados quanto o desempenho da consulta.

5. Conceito: Índice GIN

(1) Princípio do Índice GIN

O GIN (Índice Invertido Generalizado) é um índice invertido: um mapeamento de um elemento para as linhas que o contêm. Ele se adapta a consultas do tipo "contém".

▶ Exemplo: Índice GIN em uma Coluna JSONB

CREATE INDEX idx_products_attrs ON products USING GIN (attrs);

SELECT product_id, name
FROM products
WHERE attrs @> '{"category": "electronics"}';

 product_id |    name
------------+------------
        101 | Laptop Pro
        205 | Smart Watch

▶ Exemplo: Índice GIN em uma Coluna de Array

CREATE INDEX idx_products_tags ON products USING GIN (tags);

SELECT product_id, name
FROM products
WHERE tags @> ARRAY['summer', 'sale'];

Output:

  result  
----------
   42.50
(1 row)

▶ Exemplo: Índice GIN para Busca Textual

CREATE INDEX idx_articles_body ON articles USING GIN (to_tsvector('english', body));

SELECT id, title
FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('postgresql & index');

Output:

CREATE TABLE

(2) Comparação GIN vs B-Tree

6. Conceito: GiST / BRIN / SP-GiST / Hash

(1) Índice GiST

O GiST é um framework de árvore de busca generalizada que suporta estratégias de particionamento personalizadas. Seu uso típico é para dados espaciais.

▶ Exemplo: Índice GiST em um Tipo de Intervalo

CREATE INDEX idx_events_time_range ON events USING GiST (time_range);

SELECT event_id, title
FROM events
WHERE time_range && daterange('2025-01-01', '2025-03-01');

Output:

CREATE TABLE

(2) Índice BRIN

O BRIN (Índice de Intervalo de Bloco) armazena informações de resumo para cada bloco de dados (valores mínimos/máximos). É extremamente compacto e adequado para tabelas grandes ordenadas fisicamente.

▶ Exemplo: Índice BRIN em uma Tabela de Série Temporal

CREATE INDEX idx_logs_created_at ON logs USING BRIN (created_at)
  WITH (pages_per_range = 32);

SELECT count(*) FROM logs
WHERE created_at BETWEEN '2025-06-01' AND '2025-06-30';

Output:

 count 
-------
     5
(1 row)

(3) Índice SP-GiST

O SP-GiST é adequado para estruturas de particionamento desbalanceadas, como prefixos de números de telefone e roteamento IP.

▶ Exemplo: Índice SP-GiST em um Prefixo de Número de Telefone

-- Habilite a extensão btree_gist primeiro: CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE INDEX idx_customers_phone ON customers USING SP-GiST (phone prefix_range);

Output:

CREATE TABLE

(4) Índice Hash

Um índice Hash suporta apenas consultas simples de igualdade; não suporta intervalo nem ordenação. Antes do PostgreSQL 10, os índices Hash tinham um problema de WAL (agora corrigido), mas o B-Tree geralmente é a melhor escolha.

7. Conceito: Recursos Avançados de Índice

(1) Índice Parcial

Um índice parcial contém apenas as linhas que satisfazem uma condição WHERE, reduzindo o tamanho do índice e o custo de manutenção. Este é um recurso especializado do PostgreSQL.

▶ Exemplo: Indexar Apenas Usuários Ativos

CREATE INDEX idx_users_active_email ON users (email)
WHERE status = 'active';

Output:

CREATE TABLE

SELECT email FROM users WHERE status = 'active' AND email = 'alice@example.com';

Esta consulta atinge o índice parcial. Uma consulta sem a condição status = 'active' não o atingirá.

▶ Exemplo: Indexar Apenas Pedidos Não Enviados

CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE shipped = false;

Output:

CREATE TABLE

(2) Índice de Expressão

Quando uma condição de consulta envolve uma coluna em uma função ou computação, um índice normal não pode ser usado. Um índice de express��o constrói o índice sobre o resultado computado.

▶ Exemplo: Consulta Case-Insensitive

CREATE INDEX idx_users_email_lower ON users (LOWER(email));

SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

Output:

CREATE TABLE

▶ Exemplo: Consulta com Truncamento de Data

CREATE INDEX idx_orders_date_trunc ON orders (DATE_TRUNC('day', created_at));

SELECT COUNT(*) FROM orders
WHERE DATE_TRUNC('day', created_at) = '2025-06-15'::date;

Output:

 count 
-------
     5
(1 row)

(3) Índice Composto e o Prefixo Mais à Esquerda

Os padrões de consulta que um índice composto (a, b, c) pode atender:

▶ Exemplo: Criar um Índice Composto

CREATE INDEX idx_orders_customer_date
ON orders (customer_id, order_date DESC);

Output:

CREATE TABLE

(4) Construção de Índice Online com CONCURRENTLY

A construção de um índice obtém um bloqueio exclusivo por padrão, bloqueando escritas. O CONCURRENTLY não bloqueia escritas, mas constrói mais lentamente.

▶ Exemplo: Construir um Índice Online

CREATE INDEX CONCURRENTLY idx_orders_region
ON orders (region);

Output:

CREATE TABLE

8. Conceito: Plano de Execução EXPLAIN

(1) Básico do EXPLAIN

▶ Exemplo: Visualizar o Plano de Execução

EXPLAIN
SELECT * FROM orders WHERE customer_id = 1;

                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders  (cost=0.29..8.31 rows=1 width=72)
   Index Cond: (customer_id = 1)

▶ Exemplo: EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 1;

                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using idx_orders_customer_id on orders
   (cost=0.29..8.31 rows=1 width=72) (actual time=0.015..0.016 rows=2 loops=1)
   Index Cond: (customer_id = 1)
 Planning Time: 0.085 ms
 Execution Time: 0.032 ms

(2) Principais Tipos de Varredura

▶ Exemplo: Index Only Scan

EXPLAIN
SELECT customer_id FROM orders WHERE customer_id = 1;

 Index Only Scan using idx_orders_customer_id on orders
   Index Cond: (customer_id = 1)

Apenas o índice é lido — sem busca na tabela para as linhas de dados — proporcionando o melhor desempenho.

9. Manutenção e Falha de Índices

(1) Reconstrução com REINDEX

Após muitas inserções/exclusões ao longo do tempo, um índice pode ficar inchado; o REINDEX o reconstrói para recuperar espaço.

▶ Exemplo: Reconstruir um Índice

REINDEX INDEX idx_orders_customer_id;

REINDEX INDEX CONCURRENTLY idx_orders_customer_id;

Output:

-- Comando SQL executado com sucesso

(2) Cenários Comuns de Falha de Índice

▶ Exemplo: Incompatibilidade de Tipo Implícita Causa Falha de Índice

SELECT * FROM users WHERE phone = 13800138000;

SELECT * FROM users WHERE phone = '13800138000';

Output:

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

phone é VARCHAR. A conversão implícita na primeira linha faz com que o índice não seja usado; a segunda linha atinge o índice.

10. Exemplo Abrangente

Plano de otimização de índices de Bob — busca de produtos JSONB + índice parcial de usuários ativos + índice composto + índice BRIN de série temporal:

CREATE INDEX CONCURRENTLY idx_products_attrs_gin
  ON products USING GIN (attrs);

CREATE INDEX CONCURRENTLY idx_users_active_email
  ON users (email, last_login_at)
  WHERE status = 'active';

CREATE INDEX CONCURRENTLY idx_orders_customer_date
  ON orders (customer_id, order_date DESC);

CREATE INDEX CONCURRENTLY idx_audit_log_created_brin
  ON audit_log USING BRIN (created_at)
  WITH (pages_per_range = 32);

EXPLAIN ANALYZE
SELECT p.product_id, p.name, p.attrs
FROM products p
WHERE p.attrs @> '{"category": "electronics", "in_stock": true}';

EXPLAIN ANALYZE
SELECT user_id, email
FROM users
WHERE status = 'active'
  AND email LIKE 'alice%'
ORDER BY last_login_at DESC
LIMIT 10;

11. Fluxo de Execução

Fluxo de seleção e otimização do tipo de índice:

flowchart TD
    A[Consulta lenta encontrada] --> B[EXPLAIN ANALYZE]
    B --> C{Tipo de varredura?}
    C -->|Seq Scan| D{Tem índice adequado?}
    C -->|Index Scan| E[Índice atingido<br/>otimize a consulta/índice]
    D -->|Não| F{Tipo de dado?}
    D -->|Sim, mas não atinge| G[Verificar invalidação do índice]
    F -->|Escalar/ordenação| H[Criar B-Tree]
    F -->|JSONB/array| I[Criar GIN]
    F -->|Geométrico/intervalo| J[Criar GiST]
    F -->|Tabela grande sequencial| K[Criar BRIN]
    F -->|Apenas subconjunto| L[Criar Índice Parcial]
    F -->|Função/computação| M[Criar Índice de Expressão]
    H --> N[Implantar com CONCURRENTLY]
    I --> N
    J --> N
    K --> N
    L --> N
    M --> N

    style B fill:#e1f5fe
    style G fill:#ffccbc
    style N fill:#c8e6c9

flowchart TD
    A[Consulta lenta encontrada] --> B[EXPLAIN ANALYZE]
    B --> C{Tipo de varredura?}
    C -->|Seq Scan| D{Tem índice adequado?}
    C -->|Index Scan| E[Índice atingido<br/>otimize a consulta/índice]
    D -->|Não| F{Tipo de dado?}
    D -->|Sim, mas não atinge| G[Verificar invalidação do índice]
    F -->|Escalar/ordenação| H[Criar B-Tree]
    F -->|JSONB/array| I[Criar GIN]
    F -->|Geométrico/intervalo| J[Criar GiST]
    F -->|Tabela grande sequencial| K[Criar BRIN]
    F -->|Apenas subconjunto| L[Criar Índice Parcial]
    F -->|Função/computação| M[Criar Índice de Expressão]
    H --> N[Implantar com CONCURRENTLY]
    I --> N
    J --> N
    K --> N
    L --> N
    M --> N

    style B fill:#e1f5fe
    style G fill:#ffccbc
    style N fill:#c8e6c9

❓ Perguntas Frequentes

P: "Mais índices" é sempre melhor? R: Não. Cada índice adiciona sobrecarga de escrita (INSERT/UPDATE/DELETE precisam manter o índice) e espaço de armazenamento. Crie índices apenas para consultas de alta frequência e verifique periodicamente o uso com pg_stat_user_indexes.

P: E se a construção com CONCURRENTLY falhar? R: Uma construção CONCURRENTLY que falha deixa um índice INVALID. Encontre-o com SELECT indexname FROM pg_indexes WHERE indexdef LIKE '%INVALID%' ou \d+ tbl, depois remova-o com DROP INDEX.

P: Por que minha consulta LIKE não usa índice? R: LIKE 'abc%' pode usar B-Tree; LIKE '%abc' com curinga inicial não pode — precisa de GIN mais a extensão pg_trgm.

P: Quais cenários são adequados para um índice BRIN? R: Tabelas grandes ordenadas fisicamente (como tabelas de log somente de acréscimo, escritas em ordem temporal). Se os dados não têm relação com a ordem física, a filtragem aproximada do BRIN funciona mal e não é recomendada.

P: Um índice parcial afeta o desempenho de escrita? R: Menos que um índice completo. As linhas que não atendem à condição WHERE não precisam ter seu índice parcial atualizado na escrita, economizando E/S.

P: Um índice composto (a, b) pode suportar WHERE a=1 ORDER BY b? R: Sim. O índice composto é ordenado por a e depois por b, então ele satisfaz tanto o filtro WHERE quanto o ORDER BY, evitando uma ordenação extra.

📖 Resumo

• O PostgreSQL suporta 6 tipos de índice; o B-Tree se adequa a cerca de 90% dos cenários
• Índices GIN são adequados para consultas de contenção em JSONB / arrays / busca textual
• Índices BRIN são extremamente compactos e adequados para varreduras de intervalo em tabelas grandes ordenadas fisicamente
• Índices parciais indexam apenas as linhas que atendem a uma condição, reduzindo tamanho e custo de manutenção
• Índices de expressão indexam um resultado de função/computação, corrigindo falhas de índice causadas por colunas envolvidas em funções
• Índices compostos seguem a regra do prefixo mais à esquerda; a ordem das colunas afeta se uma consulta atinge o índice
• CONCURRENTLY constrói índices online sem bloquear escritas, mas não pode ser executado dentro de uma transação
• EXPLAIN ANALYZE é a ferramenta principal para diagnosticar consultas lentas
• Causas comuns de falha de índice: envolvimento em função, incompatibilidade de tipo, estatísticas desatualizadas

📝 Exercícios

1. ⭐ Crie um índice B-Tree na coluna customer_id da tabela orders e verifique com EXPLAIN que a consulta atinge o índice.
2. ⭐ Crie um índice GIN na coluna JSONB da tabela products e observe como o plano de execução muda para uma consulta @>.
3. ⭐⭐ Crie um índice parcial: indexe apenas a coluna created_at dos pedidos onde status = 'pending' e compare a diferença de tamanho em relação a um índice completo.
4. ⭐⭐ Crie um índice de expressão em LOWER(email), verifique se uma consulta case-insensitive atinge o índice; depois crie um índice composto (region, created_at DESC) para suportar consultas ordenadas por região e tempo.
5. ⭐⭐⭐ Para uma tabela de log com dezenas de milhões de linhas, projete um plano combinado de índice BRIN + índice composto B-Tree + índice parcial e use EXPLAIN ANALYZE para comparar o tempo de consulta antes e depois da otimização.

Equipe Técnica Web-Tutorial

Uma plataforma de tutoriais mantida por diversos desenvolvedores. Cada tutorial é escrito e revisado por profissionais da área correspondente. Trabalhamos para manter nosso conteúdo preciso e confiável — se encontrar algum problema, avise-nos.

Sobre Nós