Funcionamento e Otimizacao de Indices de Banco de Dados - Principios para Acelerar Consultas

22 min leitura | 2025.12.02

Banco de DadosPerformancePostgreSQLDesign

Muitos problemas de performance de banco de dados podem ser resolvidos com um design adequado de indices. No entanto, indices nao sao algo que se cria “por precaucao”, mas sim algo que precisa ser projetado com base no entendimento da estrutura de dados e padroes de consulta. Neste artigo, explicamos sistematicamente desde a estrutura interna dos indices ate tecnicas praticas de otimizacao.

O que sao Indices

Analogia com o Indice de um Livro

Indices podem ser comparados ao “indice remissivo” no final de um livro.

flowchart LR
    subgraph NoIndex["Busca sem Indice"]
        A1["Escanear todas as paginas sequencialmente"] --> A2["Complexidade de tempo O(n)"]
    end

    subgraph WithIndex["Busca com Indice"]
        B1["Buscar palavra-chave no indice"] --> B2["Obter numero da pagina"] --> B3["Complexidade de tempo O(log n)"]
    end

Funcionamento Basico do Indice

-- Sem indice: Full Table Scan (varredura completa)
SELECT * FROM users WHERE email = 'user@example.com';
-- Escaneia todas as 1 milhao de linhas

-- Com indice: Index Scan
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'user@example.com';
-- Usa o indice para busca rapida

Tipos de Indices

1. Indice B-Tree

A estrutura de indice mais comum, padrao no PostgreSQL.

flowchart TB
    R["[50]"] --> L["[25]"]
    R --> RI["[75]"]
    L --> LL["[10,20]"]
    L --> LR["[30,40]"]
    RI --> RL["[60,70]"]
    RI --> RR["[80,90]"]
    LL --> D1["Pagina de Dados"]
    LR --> D2["Pagina de Dados"]
    RL --> D3["Pagina de Dados"]
    RR --> D4["Pagina de Dados"]

Caracteristicas:

  • Busca por igualdade (=): O(log n)
  • Busca por intervalo (<, >, BETWEEN): O(log n + m)
  • Acesso a dados ordenados: eficiente
-- Operacoes adequadas para B-Tree
SELECT * FROM orders WHERE created_at > '2025-12-01';  -- Busca por intervalo
SELECT * FROM products ORDER BY price;                 -- Ordenacao
SELECT * FROM users WHERE id = 12345;                  -- Busca por igualdade

2. Indice Hash

Indice especializado apenas para buscas por igualdade.

flowchart LR
    Input["'user@example.com'"] --> Hash["hash() = 42"]

    subgraph Buckets["Array de Buckets"]
        B0["[0] → NULL"]
        B1["[1] → NULL"]
        B42["[42] → (user@example.com, row_pointer)"]
        B99["[99] → NULL"]
    end

    Hash --> B42

Caracteristicas:

  • Busca por igualdade (=): O(1)
  • Busca por intervalo: impossivel
  • Ordenacao: impossivel
-- Hash index melhorado no PostgreSQL 10+
CREATE INDEX idx_users_email_hash ON users USING hash(email);

-- Operacao adequada
SELECT * FROM users WHERE email = 'user@example.com';  -- ✓
-- Operacoes inadequadas
SELECT * FROM users WHERE email LIKE 'user%';          -- ✗
SELECT * FROM users ORDER BY email;                    -- ✗

3. GIN (Generalized Inverted Index)

Indice adequado para arrays, JSONB e busca de texto completo.

-- Indice GIN em coluna JSONB
CREATE INDEX idx_products_metadata ON products USING gin(metadata);

-- Busca eficiente
SELECT * FROM products
WHERE metadata @> '{"category": "electronics"}';

-- Coluna de array
CREATE INDEX idx_posts_tags ON posts USING gin(tags);
SELECT * FROM posts WHERE tags @> ARRAY['javascript', 'react'];

-- Busca de texto completo
CREATE INDEX idx_articles_fts ON articles
USING gin(to_tsvector('portuguese', title || ' ' || content));

4. GiST (Generalized Search Tree)

Indice adequado para dados geoespaciais e tipos de intervalo.

-- Indice geoespacial PostGIS
CREATE INDEX idx_locations_geom ON locations USING gist(geom);

-- Busca por intervalo
SELECT * FROM locations
WHERE ST_DWithin(geom, ST_MakePoint(139.7, 35.6), 1000);  -- Dentro de 1km

-- Tipo de intervalo
CREATE INDEX idx_reservations_period ON reservations
USING gist(daterange(start_date, end_date));

Comparacao de Tipos de Indices

IndiceBusca por IgualdadeBusca por IntervaloOrdenacaoUso
B-TreeOtimoOtimoOtimoUso geral
HashOtimoNaoNaoApenas igualdade
GINOtimoParcialNaoArray, JSONB, busca de texto
GiSTBomOtimoNaoGeoespacial, intervalos
BRINParcialOtimoParcialGrande escala, series temporais

Indices Compostos

A Ordem das Colunas e Importante

Em indices compostos, a ordem das colunas afeta significativamente a performance da consulta.

-- Indice composto
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Consultas que usam este indice:
SELECT * FROM orders WHERE user_id = 123;                    -- ✓
SELECT * FROM orders WHERE user_id = 123 AND status = 'paid'; -- ✓
SELECT * FROM orders WHERE user_id = 123 ORDER BY status;    -- ✓

-- Consultas que NAO usam este indice:
SELECT * FROM orders WHERE status = 'paid';  -- ✗ Coluna inicial ausente

flowchart TB
    Root["(user_id=100, status=active)"] --> Left["(user_id=50, status=*)"]
    Root --> Right["(user_id=150, status=*)"]
    Left --> LL["Folhas Ordenadas"]
    Right --> RL["Folhas Ordenadas"]

Fluxo da busca:

  1. Primeiro filtra pela coluna inicial (user_id)
  2. Em seguida, filtra pela proxima coluna (status)

Criterios para Determinar a Ordem das Colunas

-- Principio basico: colunas com maior cardinalidade (variedade de valores) primeiro

-- Tabela users
-- user_id: 1 milhao de tipos (alta cardinalidade)
-- status: 3 tipos (baixa cardinalidade)
-- country: 200 tipos (media cardinalidade)

-- Recomendado: Alta cardinalidade → Baixa cardinalidade
CREATE INDEX idx_users_composite ON users(user_id, country, status);

-- Porem, considere tambem os padroes de consulta
-- Se WHERE status = 'active' for frequente, pode ser util colocar status primeiro

Indice de Cobertura (Clausula INCLUDE)

-- Clausula INCLUDE no PostgreSQL 11+
CREATE INDEX idx_orders_covering ON orders(user_id, status)
INCLUDE (total_amount, created_at);

-- Permite Index Only Scan
SELECT user_id, status, total_amount, created_at
FROM orders
WHERE user_id = 123 AND status = 'paid';

-- Completa apenas com o indice, sem acesso a tabela

Como Ler Planos de Execucao

EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';

Index Scan using idx_orders_user_status on orders
    (cost=0.43..8.45 rows=1 width=100)
    (actual time=0.025..0.026 rows=1 loops=1)
    Index Cond: ((user_id = 123) AND (status = 'paid'))
Planning Time: 0.150 ms
Execution Time: 0.050 ms

flowchart TB
    subgraph Cost["cost=0.43..8.45"]
        C1["0.43: Custo ate retornar a primeira linha<br/>(custo de inicializacao)"]
        C2["8.45: Custo total para retornar todas as linhas"]
    end

    subgraph Estimate["Valores Estimados"]
        E1["rows=1: Numero estimado de linhas"]
        E2["width=100: Bytes estimados por linha"]
    end

    subgraph Actual["Valores Reais"]
        A1["actual time=0.025..0.026: Tempo real de execucao (ms)"]
        A2["rows=1: Linhas realmente retornadas"]
        A3["loops=1: Numero de execucoes desta operacao"]
    end

Comparacao de Metodos de Varredura

-- Seq Scan (Varredura Sequencial): varredura completa
Seq Scan on orders (cost=0.00..18584.00 rows=100000 width=100)
  Filter: (status = 'paid')

-- Index Scan: usa indice para acessar a tabela
Index Scan using idx_orders_status on orders (cost=0.43..8.45 rows=1)
  Index Cond: (status = 'paid')

-- Index Only Scan: completa apenas com o indice (mais rapido)
Index Only Scan using idx_orders_covering on orders (cost=0.43..4.45 rows=1)
  Index Cond: (status = 'paid')

-- Bitmap Index Scan: combinacao de multiplos indices
Bitmap Heap Scan on orders (cost=5.00..100.00 rows=100)
  -> Bitmap Index Scan on idx_orders_status (cost=0.00..4.50 rows=100)
     Index Cond: (status = 'paid')

Melhores Praticas de Design de Indices

1. Analise de Padroes de Consulta

-- Verificar consultas frequentemente executadas com pg_stat_statements
SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;

-- Identificar consultas lentas
SELECT query, mean_time, calls
FROM pg_stat_statements
WHERE mean_time > 100  -- Mais de 100ms
ORDER BY mean_time DESC;

2. Monitoramento de Uso de Indices

-- Detectar indices nao utilizados
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- Detectar indices duplicados
SELECT
    a.indexrelid::regclass AS index1,
    b.indexrelid::regclass AS index2,
    a.indkey AS columns1,
    b.indkey AS columns2
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
    AND a.indexrelid < b.indexrelid
    AND a.indkey <@ b.indkey;

3. Indice Parcial

Indexa apenas dados de condicoes especificas.

-- Indexar apenas usuarios ativos
CREATE INDEX idx_users_active ON users(email)
WHERE status = 'active';

-- Apenas dados recentes
CREATE INDEX idx_orders_recent ON orders(created_at)
WHERE created_at > '2024-01-01';

-- Apenas dados nao nulos
CREATE INDEX idx_products_sku ON products(sku)
WHERE sku IS NOT NULL;

4. Indice de Expressao

Indexa resultados de calculos ou funcoes.

-- Busca sem distincao de maiusculas/minusculas
CREATE INDEX idx_users_email_lower ON users(lower(email));
SELECT * FROM users WHERE lower(email) = 'user@example.com';

-- Parte da data
CREATE INDEX idx_orders_year_month ON orders(
    date_trunc('month', created_at)
);
SELECT * FROM orders
WHERE date_trunc('month', created_at) = '2025-12-01';

-- Chave especifica de JSONB
CREATE INDEX idx_users_settings_theme ON users((settings->>'theme'));
SELECT * FROM users WHERE settings->>'theme' = 'dark';

Antipadroes de Indices

1. Excesso de Indices

-- Antipadrao: indice em todas as colunas
CREATE INDEX idx_users_id ON users(id);           -- Ja criado automaticamente pela PK
CREATE INDEX idx_users_name ON users(name);       -- Realmente necessario?
CREATE INDEX idx_users_email ON users(email);     -- Realmente necessario?
CREATE INDEX idx_users_created ON users(created_at);
CREATE INDEX idx_users_updated ON users(updated_at);
CREATE INDEX idx_users_status ON users(status);

-- Problemas:
-- - INSERT/UPDATE/DELETE ficam mais lentos
-- - Desperdicio de espaco de armazenamento
-- - Aumento da carga no processamento VACUUM

2. Indices de Baixa Seletividade

-- Antipadrao: coluna com poucos tipos de valores
CREATE INDEX idx_users_gender ON users(gender);  -- Apenas 3 tipos: M/F/Outro

-- Problemas:
-- - Mesmo usando o indice, acessa grande volume de linhas
-- - Seq Scan costuma ser mais eficiente

-- Solucao: combinar com outras colunas
CREATE INDEX idx_users_gender_age ON users(gender, birth_date);

3. Clausula WHERE com Funcoes

-- Antipadrao: indice nao utilizado
SELECT * FROM users WHERE YEAR(created_at) = 2025;

-- Forma que utiliza o indice
SELECT * FROM users
WHERE created_at >= '2025-12-01'
  AND created_at < '2026-01-01';

-- Ou criar indice de expressao
CREATE INDEX idx_users_created_year ON users(EXTRACT(YEAR FROM created_at));

4. Incompatibilidade de Tipos

-- Antipadrao: conversao implicita de tipos
-- user_id e do tipo INTEGER
SELECT * FROM users WHERE user_id = '123';  -- Literal string

-- Forma que utiliza o indice
SELECT * FROM users WHERE user_id = 123;    -- Literal inteiro

Exemplo de Tuning de Performance

Estudo de Caso: Busca de Pedidos em E-commerce

-- Consulta original (lenta)
SELECT o.*, u.name AS user_name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '7 days'
ORDER BY o.created_at DESC
LIMIT 50;

-- Analise do plano de execucao
EXPLAIN ANALYZE ...
-- Seq Scan on orders: 10 segundos

-- Passo de otimizacao 1: criar indice apropriado
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

-- Passo de otimizacao 2: indice parcial (apenas pending)
CREATE INDEX idx_orders_pending ON orders(created_at DESC)
WHERE status = 'pending';

-- Passo de otimizacao 3: indice de cobertura
CREATE INDEX idx_orders_pending_covering ON orders(created_at DESC)
INCLUDE (user_id, total_amount)
WHERE status = 'pending';

-- Resultado: 10 segundos → 5ms

Manutencao de Indices

REINDEX Periodico

-- Reconstruir indice inchado
REINDEX INDEX idx_orders_status;

-- Todos os indices de uma tabela
REINDEX TABLE orders;

-- PostgreSQL 12+: opcao CONCURRENTLY
REINDEX INDEX CONCURRENTLY idx_orders_status;

Verificar Inchaço de Indices

-- Verificar taxa de inchaço dos indices
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

Resumo

Indices de banco de dados podem proporcionar melhorias dramaticas de performance quando projetados e gerenciados adequadamente.

Principios de Design

  1. Priorize padroes de consulta: Projete com base nas consultas reais
  2. Indices compostos: Considere seletividade e padroes de consulta para a ordem das colunas
  3. Indices parciais: Indexe apenas os dados necessarios
  4. Evite excesso de indices: Equilibre com o custo de escrita

Monitoramento e Otimizacao

  1. EXPLAIN ANALYZE: Verificacao regular do plano de execucao
  2. pg_stat_statements: Identificacao de consultas lentas
  3. Indices nao utilizados: Inventario periodico
  4. REINDEX: Reconstrucao de indices inchados

Indices nao sao “projete e esqueca”, mas sim algo que requer monitoramento e otimizacao continua.

Artigos Recomendados

Notícias

Novidades do PostgreSQL 17 - Performance e Usabilidade Aprimoradas

PostgreSQLBanco de Dados
Conceitos

Diferenças entre SQL e NoSQL - Guia de Escolha de Banco de Dados

Banco de DadosDesign
Conceitos

GraphQL vs REST API - Filosofias de Design e Criterios de Escolha

APIDesign
Conceitos

Estrategias de Cache - Fundamentos da Otimizacao de Performance

PerformanceDesign
Conceitos

Transações Distribuídas - Padrões de Design para Manter a Consistência

DesignBanco de Dados
Tutoriais

Introducao ao PostgreSQL - Fundamentos de Banco de Dados Relacional

PostgreSQLBanco de Dados
← Voltar para a lista