Performance SQL com Índices: Guia Completo (PostgreSQL e MySQL)
Uma query que leva 5 segundos em 1 milhão de registros pode levar 5 ms com o índice certo — diferença de 1000×. Este guia cobre tipos de índice, índices compostos e parciais, leitura de EXPLAIN, anti-padrões e checklist de manutenção.
Por Vitor Morais
Fundador do MochaLabz ·
Formate suas queries SQL
Queries legíveis são mais fáceis de analisar e otimizar.
Usar formatador SQL →Índices são a alavanca de performance mais barata e mais subutilizada em bancos relacionais. Uma query que rastreia 1 milhão de linhas pode cair de 5 segundos para 5 milissegundos com o índice certo — três ordens de grandeza. Este guia cobre tipos de índice (B-tree, Hash, GIN, GiST), índices compostos, parciais e funcionais, leitura de plano via EXPLAIN, anti-padrões que silenciosamente impedem o uso de índices, e a manutenção que mantém tudo funcionando em produção.
O que é um índice e por que ele acelera
Sem índice, o banco precisa fazer sequential scan (Seq Scan): ler todas as linhas da tabela e filtrar uma por uma. Para tabelas pequenas, isso é trivial. Para tabelas grandes, é desastre — o tempo cresce linearmente com o tamanho.
Um índice é uma estrutura auxiliar (geralmente uma B-tree balanceada) que mapeia valores de uma coluna para os endereços físicos dos registros. Buscar num índice tem complexidade O(log n): 1 milhão de linhas custa só ~20 comparações. É o mesmo princípio do índice no fim de um livro: em vez de ler 500 páginas para encontrar um termo, você consulta 2 páginas no índice e vai direto.
O preço dos índices
Índices não são grátis. Cada um adiciona overhead em três dimensões:
- Espaço em disco: índices ocupam armazenamento extra (10–50% do tamanho da tabela é comum).
- Escrita: cada INSERT, UPDATE ou DELETE precisa atualizar todos os índices da tabela. Mais índices = escrita mais lenta.
- Manutenção: índices se fragmentam com o tempo, exigindo reindex/vacuum periódico.
Regra do equilíbrio
Cada índice troca um pouco de performance de escrita por muito de leitura. Em sistemas read-heavy (a maioria), vale a pena. Em pipelines de ingestão massiva, considere remover índices durante a carga e recriá-los depois.
Tipos de índice (PostgreSQL e MySQL)
| Critério | Quando usar | Suporte |
|---|---|---|
| B-tree | Padrão para igualdade, range (<, >), ORDER BY | Postgres + MySQL |
| Hash | Apenas igualdade, casos específicos | Postgres + MySQL Memory engine |
| GIN | Arrays, JSONB, full-text search, trigram | Postgres |
| GiST | Geoespacial, busca por similaridade | Postgres (PostGIS) |
| BRIN | Tabelas enormes com dados ordenados (timeseries) | Postgres |
| Fulltext (FULLTEXT) | Busca textual MATCH AGAINST | MySQL |
| Spatial | Geometrias (POINT, POLYGON) | MySQL InnoDB + Postgres PostGIS |
Em 95% dos casos, B-tree resolve. Os outros tipos brilham em cenários específicos — full-text search, JSONB queries, geoespacial.
Criando índices: a sintaxe básica
-- Índice simples (B-tree por padrão)
CREATE INDEX idx_pedidos_usuario_id
ON pedidos (usuario_id);
-- Índice composto (a ordem importa!)
CREATE INDEX idx_pedidos_usuario_data
ON pedidos (usuario_id, criado_em DESC);
-- Índice único (também garante unicidade)
CREATE UNIQUE INDEX idx_usuarios_email
ON usuarios (email);
-- Índice parcial (Postgres) — só indexa parte das linhas
CREATE INDEX idx_pedidos_pendentes
ON pedidos (criado_em)
WHERE status = 'pendente';
-- Índice funcional / expression index
CREATE INDEX idx_usuarios_email_lower
ON usuarios (LOWER(email));
-- PostgreSQL: criação concorrente (sem lock na tabela)
CREATE INDEX CONCURRENTLY idx_pedidos_status
ON pedidos (status);
-- Removendo
DROP INDEX idx_pedidos_status;
-- PostgreSQL concorrente:
DROP INDEX CONCURRENTLY idx_pedidos_status;Índices compostos: a ordem das colunas importa
Para queries com múltiplos filtros, índice composto é muito mais eficiente que vários índices separados. Mas a ordem das colunas define quais queries o índice serve:
-- Índice composto (usuario_id, status, criado_em DESC)
CREATE INDEX idx_pedidos_composto
ON pedidos (usuario_id, status, criado_em DESC);
-- Queries que ESTE índice acelera:
-- ✅ WHERE usuario_id = 42
-- ✅ WHERE usuario_id = 42 AND status = 'ativo'
-- ✅ WHERE usuario_id = 42 AND status = 'ativo' ORDER BY criado_em DESC
-- ✅ ORDER BY usuario_id, status, criado_em DESC
-- Queries que NÃO usam (ou usam parcialmente):
-- ❌ WHERE status = 'ativo' ← pulou usuario_id
-- ❌ WHERE criado_em > '2026-01-01' ← pulou usuario_id e status
-- ⚠️ WHERE status = 'ativo' AND usuario_id = 42 ← funciona, ordem AND não importaPrincípio do prefixo mais à esquerda
Em índice composto, o banco pode usar qualquer prefixo da sequência de colunas (1ª, 1ª+2ª, 1ª+2ª+3ª). Pular do meio para a direita não funciona. Por isso a coluna mais filtrada deve vir primeiro.
Índices parciais: economia de espaço e velocidade extra
Se a maioria das queries só consulta um subset dos dados (ex: pedidos com status ‘pendente’, que são 5% do total), um índice parcial é menor, mais rápido e atualiza menos:
-- Sem índice parcial: índice cobre 10M de pedidos
CREATE INDEX ON pedidos (criado_em);
-- Com índice parcial: índice cobre só 500k pedidos pendentes
CREATE INDEX ON pedidos (criado_em)
WHERE status = 'pendente';
-- Casos de uso comuns:
-- - Soft delete: WHERE deleted_at IS NULL
-- - Tarefas em fila: WHERE processed = false
-- - Status ativo: WHERE active = true
-- - Logs recentes: WHERE created_at > NOW() - INTERVAL '7 days'Diagnosticando com EXPLAIN ANALYZE
A ferramenta mais importante para tunar SQL. EXPLAIN mostra o plano que o planner pretende executar; ANALYZE roda de verdade e mede:
-- PostgreSQL
EXPLAIN ANALYZE
SELECT * FROM pedidos WHERE usuario_id = 42;
-- Sem índice:
-- Seq Scan on pedidos (cost=0..15000 rows=1000000)
-- Filter: (usuario_id = 42)
-- Rows Removed by Filter: 999990
-- Actual time: 450 ms
-- Com índice:
-- Index Scan using idx_pedidos_usuario on pedidos
-- Index Cond: (usuario_id = 42)
-- Actual time: 0.15 ms ← 3000× mais rápido
-- MySQL
EXPLAIN ANALYZE
SELECT * FROM pedidos WHERE usuario_id = 42;
-- Mostra type=ref (bom) ou type=ALL (sem índice, ruim)| Critério | Sinal | O que significa |
|---|---|---|
| Seq Scan / type=ALL | Sem índice | Tabela varrida do início ao fim — em tabela grande é problema |
| Index Scan / type=ref | Com índice | Índice está sendo usado |
| Index Only Scan | Cobertura total | Todos os dados estão no próprio índice — ótimo |
| Bitmap Heap Scan | Múltiplos índices | Combinou bitmaps de vários índices |
| rows estimado ≠ actual | Estatísticas ruins | Rode ANALYZE para atualizar estatísticas |
| Hash Join, Merge Join | Estratégia de JOIN | Para tabelas grandes geralmente bom |
| Nested Loop em milhões | JOIN ineficiente | Geralmente falta índice na coluna de JOIN |
Os 7 anti-padrões que silenciosamente matam índices
1. Função na coluna indexada
-- ❌ Não usa o índice em email
WHERE LOWER(email) = 'ana@example.com'
-- ✅ Crie índice funcional
CREATE INDEX ON usuarios (LOWER(email));
-- ✅ Ou normalize na escrita (lowercase no INSERT/UPDATE)2. LIKE com % no início
-- ❌ B-tree não consegue usar
WHERE nome LIKE '%silva'
-- ✅ Postgres: índice trigram
CREATE EXTENSION pg_trgm;
CREATE INDEX ON usuarios USING gin (nome gin_trgm_ops);
-- ✅ MySQL: índice fulltext
CREATE FULLTEXT INDEX ON usuarios (nome);
SELECT * FROM usuarios WHERE MATCH(nome) AGAINST ('silva');3. Conversão implícita de tipo
-- ❌ id é INT, '42' é text → conversão linha por linha
WHERE id = '42'
-- ✅ Use o tipo correto
WHERE id = 424. NOT, <>, IS NOT NULL
-- ❌ Negação raramente usa índice eficientemente
WHERE status <> 'ativo'
-- ✅ Inverta para igualdade quando possível
WHERE status IN ('cancelado', 'expirado')5. OR entre colunas diferentes
-- ❌ OR pode forçar Seq Scan
WHERE email = 'a@b.com' OR telefone = '11999999999'
-- ✅ Reescreva como UNION
SELECT * FROM usuarios WHERE email = 'a@b.com'
UNION
SELECT * FROM usuarios WHERE telefone = '11999999999'6. ORDER BY em coluna sem índice
-- ❌ Sem índice em criado_em — sort em memória ou disco
SELECT * FROM pedidos
ORDER BY criado_em DESC LIMIT 100;
-- ✅ Índice com ordem explícita (Postgres respeita DESC)
CREATE INDEX idx_pedidos_data ON pedidos (criado_em DESC);7. Excesso de índices
Cada índice extra: mais espaço, escrita mais lenta, mais manutenção. Se você tem 15 índices em uma tabela de e-commerce de alto volume, provavelmente está gastando mais em escrita do que ganhando em leitura.
Manutenção: o que faz seus índices durarem
- ANALYZE periódico (PostgreSQL): atualiza estatísticas que o planner usa. Auto-vacuum cuida disso por padrão, mas em workloads pesados pode atrasar.
- VACUUM / OPTIMIZE TABLE: recupera espaço e reorganiza páginas após muitos UPDATEs/DELETEs.
- REINDEX: reconstrói índices fragmentados. Em Postgres, use REINDEX CONCURRENTLY para não travar.
- Auditoria de índices não usados: pg_stat_user_indexes (Postgres) ou performance_schema.table_io_waits_summary_by_index_usage (MySQL) mostram índices que nunca foram lidos.
- Log de slow queries: ative log de queries acima de 100–500ms; revise periodicamente.
Auditoria de índices em produção
-- PostgreSQL: índices nunca usados
SELECT schemaname, tablename, indexrelname,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
-- PostgreSQL: índices duplicados (cobertura redundante)
SELECT a.indexrelname, b.indexrelname
FROM pg_indexes a
JOIN pg_indexes b ON a.tablename = b.tablename
WHERE a.indexname < b.indexname
AND a.indexdef LIKE b.indexdef || '%';
-- MySQL: tamanho dos índices
SELECT table_schema, table_name, index_name,
ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size'
ORDER BY size_mb DESC;Estratégia em 4 passos para acelerar uma query lenta
- Reproduza: rode
EXPLAIN ANALYZEna query exata, com dados reais. - Identifique o gargalo: Seq Scan em tabela grande? Nested Loop com milhões? Sort em coluna sem índice?
- Crie/ajuste índice: simples para uma coluna, composto para múltiplas, parcial se filtro fixo, funcional se transformação na coluna.
- Re-rode EXPLAIN ANALYZE: confirme que o plano mudou para Index Scan e o tempo caiu. Em produção, use
CREATE INDEX CONCURRENTLY(Postgres) para evitar lock.
Diferenças PostgreSQL vs MySQL
| Critério | PostgreSQL | MySQL (InnoDB) |
|---|---|---|
| Tipos disponíveis | B-tree, Hash, GIN, GiST, BRIN, SP-GiST | B-tree, Fulltext, Spatial, Hash (Memory) |
| Índice parcial (WHERE) | ✅ | ❌ |
| Índice funcional | ✅ | ✅ (8.0+ via generated columns) |
| INVISIBLE INDEX | ❌ | ✅ (testar sem dropar) |
| Concorrente sem lock | ✅ CONCURRENTLY | Online DDL no InnoDB |
| Cluster por chave primária | Heap (clustered opcional) | Sempre clustered |
| Estatísticas | ANALYZE / auto-vacuum | ANALYZE TABLE / innodb_stats_persistent |
Checklist rápido para queries rápidas
- ✅ Toda chave estrangeira tem índice.
- ✅ Colunas usadas em WHERE de queries frequentes têm índice.
- ✅ Índices compostos com a coluna mais seletiva primeiro.
- ✅ Sem função em coluna indexada na query (ou índice funcional criado).
- ✅ Tipos batem entre coluna e valor (sem conversão implícita).
- ✅ Filtros fixos viraram índice parcial (Postgres).
- ✅ EXPLAIN ANALYZE rodado em queries críticas antes do deploy.
- ✅ Auditoria mensal de índices não usados.
- ✅ ANALYZE/VACUUM agendado em workloads pesados.
- ✅ Slow query log ativo em produção.
Perguntas frequentes
O que é um índice em SQL?+
Índice é uma estrutura de dados auxiliar (geralmente uma B-tree balanceada) que mapeia valores de uma coluna para a localização física dos registros, permitindo que o banco encontre dados sem precisar percorrer a tabela inteira. Funciona como o índice no fim de um livro: em vez de ler tudo, você vai direto à página certa.
Quando devo criar um índice?+
Crie índice em colunas usadas frequentemente em WHERE, JOIN ON, ORDER BY ou GROUP BY, especialmente em tabelas com mais de alguns milhares de linhas. Chaves estrangeiras quase sempre precisam de índice. Também valem índices em colunas com filtros muito seletivos (poucos registros casam).
Quando NÃO devo criar índice?+
Em tabelas pequenas (até alguns milhares de linhas, onde scan completo é barato), em colunas raramente usadas em filtros, em colunas com baixa cardinalidade (boolean simples, status com 2 valores), e em sistemas com muito mais escrita que leitura — cada índice torna INSERT/UPDATE/DELETE mais lentos.
O que é EXPLAIN ANALYZE e como usar?+
EXPLAIN mostra o plano que o banco PRETENDE executar; EXPLAIN ANALYZE executa de fato e mostra os tempos reais. Use ANALYZE para investigar queries lentas: olhe se aparece Seq Scan em tabelas grandes (ruim), se Index Scan está sendo usado (bom), e se as estimativas (rows) batem com os actual rows (estatísticas precisam de ANALYZE/auto-vacuum).
Qual a diferença entre índice simples e composto?+
Índice simples cobre uma coluna; composto cobre várias colunas em ordem específica. Para queries com múltiplos filtros (WHERE a = 1 AND b = 2), um índice composto (a, b) costuma ser muito mais eficiente que dois índices separados, porque o banco pode aplicar os dois filtros num único acesso. A ordem das colunas importa: a mais seletiva primeiro, geralmente.
Por que minha query usa Seq Scan mesmo com índice criado?+
Várias razões possíveis: (1) função na coluna indexada — LOWER(email) impede uso de índice em email; (2) tipo diferente — id INT comparado com '123' string; (3) LIKE com % no início — '%silva' não usa B-tree; (4) tabela muito pequena, planner julga scan mais rápido; (5) estatísticas desatualizadas (rode ANALYZE); (6) seletividade baixa (filtro retorna >20% das linhas).
Quantos índices uma tabela deveria ter?+
Não há número mágico. Regra prática: comece com chaves primárias, chaves estrangeiras e índices nas colunas mais usadas em WHERE/ORDER BY. Adicione conforme aparecem queries lentas em produção. Em OLTP típico, 5–10 índices por tabela é razoável; em OLAP, mais. Periodicamente revise via pg_stat_user_indexes (Postgres) ou information_schema (MySQL) para identificar índices não usados.
Índice único melhora performance ou só garante unicidade?+
Faz as duas coisas. UNIQUE INDEX garante que não existem valores duplicados, e simultaneamente permite buscas extremamente rápidas por aquele valor (porque o banco sabe que vai encontrar no máximo um). Use sempre que tiver constraint de unicidade — é gratuito em performance.
Continue lendo
SQL para Iniciantes (2026): Guia Prático com Formatação, Exemplos e Boas Práticas
Guia de SQL do zero: SELECT, WHERE, JOIN, GROUP BY, tipos de dados, formatação consistente e erros que iniciantes cometem em produção.
UUID como Chave Primária: Vantagens, Problemas e Alternativas (2026)
Tudo sobre usar UUID como chave primária em PostgreSQL e MySQL: por que vale, problema de fragmentação do v4, como UUID v7 resolve, comparativo com BIGINT, ULID, NanoID, CUID2 e Snowflake.
DATETIME vs TIMESTAMP no Banco de Dados (Guia 2026): MySQL e PostgreSQL
Comparativo técnico completo: armazenamento, fuso horário, range, tamanho, comportamento em mudanças de TZ, TIMESTAMPTZ, problema 2038 e migração.
SQL JOINs: Guia Completo (2026) com INNER, LEFT, RIGHT, FULL e CROSS
Guia definitivo de SQL JOINs com diagramas, tabelas e exemplos prontos para PostgreSQL e MySQL. INNER, LEFT, RIGHT, FULL OUTER, CROSS, SELF JOIN, ON vs USING, anti-join, semi-join e armadilhas de performance.