Índices

Autor

Douglas Braga

Muitas consultas acessam apenas uma pequena fração das tuplas de uma tabela. Percorrer todas as linhas para encontrar as que satisfazem um predicado — varredura sequencial (sequential scan) — é ineficiente quando a tabela é grande. Um índice resolve esse problema.

O Que é um Índice

Um índice sobre um atributo de uma relação é uma estrutura de dados que permite ao SGBD encontrar as tuplas que possuem um valor específico para aquele atributo sem precisar percorrer todas as tuplas da relação.

A sintaxe para criar um índice é:

CREATE INDEX <nome> ON <relação> (<atributo>);

O índice mais comum em SGBDs relacionais é a B-tree (árvore-B), que suporta eficientemente buscas por igualdade, comparações e ordenações. O PostgreSQL também oferece índices HASH, GIN e GiST para casos específicos.

Criando Índices

-- Índice simples sobre id_curso na tabela aluno
-- Acelera consultas como: WHERE id_curso = 311
CREATE INDEX idx_aluno_curso ON aluno (id_curso);
-- Índice composto: acelera consultas que filtram por ano E semestre
CREATE INDEX idx_ministra_ano_sem ON ministra (ano, semestre);
-- Índice único: garante unicidade além de acelerar buscas
CREATE UNIQUE INDEX idx_prof_nome_escola ON professor (nome, id_escola);

Verificando os índices criados no banco:

SELECT tablename AS tabela,
       indexname AS indice,
       indexdef  AS definicao
FROM   pg_indexes
WHERE  schemaname = 'public'
ORDER BY tablename, indexname;
Displaying records 1 - 10
tabela indice definicao
aluno aluno_pkey CREATE UNIQUE INDEX aluno_pkey ON public.aluno USING btree (id_aluno)
centro centro_pkey CREATE UNIQUE INDEX centro_pkey ON public.centro USING btree (id_centro)
centro centro_sigla_key CREATE UNIQUE INDEX centro_sigla_key ON public.centro USING btree (sigla)
curso curso_pkey CREATE UNIQUE INDEX curso_pkey ON public.curso USING btree (id_curso)
curso curso_sigla_curso_key CREATE UNIQUE INDEX curso_sigla_curso_key ON public.curso USING btree (sigla_curso)
disciplina disciplina_pkey CREATE UNIQUE INDEX disciplina_pkey ON public.disciplina USING btree (id_disciplina)
escola escola_pkey CREATE UNIQUE INDEX escola_pkey ON public.escola USING btree (id_escola)
escola escola_sigla_key CREATE UNIQUE INDEX escola_sigla_key ON public.escola USING btree (sigla)
evento_academico evento_academico_nome_data_inicio_key CREATE UNIQUE INDEX evento_academico_nome_data_inicio_key ON public.evento_academico USING btree (nome, data_inicio)
evento_academico evento_academico_pkey CREATE UNIQUE INDEX evento_academico_pkey ON public.evento_academico USING btree (id_evento)

Por Que Índices Aceleram Consultas

Sem índice, para executar:

SELECT * FROM aluno WHERE id_curso = 311;

o SGBD lê cada linha da tabela aluno e verifica o predicado — O(n) operações.

Com um índice B-tree sobre id_curso, o SGBD percorre a árvore em O(log n) para encontrar as entradas com id_curso = 311, depois acessa diretamente as páginas de disco onde essas linhas estão armazenadas.

-- EXPLAIN mostra o plano de execução: com índice vs. sem índice
-- (Com tabelas pequenas como a da UnDF, o planner pode preferir seq scan mesmo com índice)
EXPLAIN
SELECT *
FROM   aluno
WHERE  id_curso = 311;
2 records
QUERY PLAN
Seq Scan on aluno (cost=0.00..16.38 rows=3 width=130)
Filter: (id_curso = 311)

Tradeoffs dos Índices

Aviso

Índices não são gratuitos. Cada índice:

  • Ocupa espaço em disco — pode ser significativo em tabelas grandes.
  • Retarda inserções, atualizações e exclusões — o SGBD precisa manter o índice sincronizado com os dados.
  • Não é sempre utilizado — o otimizador de consultas decide quando usar um índice com base em estatísticas da tabela.

Boas candidatas a índice: colunas usadas frequentemente em WHERE, JOIN ON, ORDER BY e que têm alta cardinalidade (muitos valores distintos).

Más candidatas: colunas booleanas ou com poucos valores distintos (como aprovado IN (0, 1)) — o índice raramente compensa o custo.

Índice Parcial

O PostgreSQL permite criar índices parciais — que indexam apenas as linhas que satisfazem um predicado. São menores e mais eficientes que índices completos quando o caso de uso foco em um subconjunto:

-- Índice apenas para matrículas reprovadas (aprovado = 0)
-- Útil se consultas de reprovados são frequentes
CREATE INDEX idx_reprovados ON matricula_disciplina (id_aluno)
WHERE aprovado = 0;

Para Praticar

-- Criar índice na coluna nome de aluno (busca por nome é comum)
CREATE INDEX IF NOT EXISTS idx_aluno_nome ON aluno (nome);
-- Listar índices criados explicitamente (excluindo os de PK e FK automáticos)
SELECT indexname, tablename, indexdef
FROM   pg_indexes
WHERE  schemaname = 'public'
  AND  indexname NOT LIKE 'pg_%'
  AND  indexname NOT LIKE '%_pkey'
ORDER BY tablename, indexname;
7 records
indexname tablename indexdef
idx_aluno_nome aluno CREATE INDEX idx_aluno_nome ON public.aluno USING btree (nome)
centro_sigla_key centro CREATE UNIQUE INDEX centro_sigla_key ON public.centro USING btree (sigla)
curso_sigla_curso_key curso CREATE UNIQUE INDEX curso_sigla_curso_key ON public.curso USING btree (sigla_curso)
escola_sigla_key escola CREATE UNIQUE INDEX escola_sigla_key ON public.escola USING btree (sigla)
evento_academico_nome_data_inicio_key evento_academico CREATE UNIQUE INDEX evento_academico_nome_data_inicio_key ON public.evento_academico USING btree (nome, data_inicio)
reserva_sala_id_sala_id_prof_data_reserva_key reserva_sala CREATE UNIQUE INDEX reserva_sala_id_sala_id_prof_data_reserva_key ON public.reserva_sala USING btree (id_sala, id_prof, data_reserva)
sala_codigo_key sala CREATE UNIQUE INDEX sala_codigo_key ON public.sala USING btree (codigo) 
-- EXPLAIN ANALYZE: tempo real de execução com e sem uso de índice
-- (resultado varia conforme o tamanho dos dados e as estatísticas do planner)
EXPLAIN ANALYZE
SELECT a.nome, c.nome_curso
FROM   aluno a
JOIN   curso c USING (id_curso)
WHERE  a.nome LIKE 'A%';
8 records
QUERY PLAN
Nested Loop (cost=0.14..9.46 rows=1 width=336) (actual time=0.010..0.011 rows=1 loops=1)
-> Seq Scan on aluno a (cost=0.00..1.23 rows=1 width=122) (actual time=0.005..0.006 rows=1 loops=1)
Filter: ((nome)::text ‘A%’::text)
Rows Removed by Filter: 17
-> Index Scan using curso_pkey on curso c (cost=0.14..8.16 rows=1 width=222) (actual time=0.003..0.003 rows=1 loops=1)
Index Cond: (id_curso = a.id_curso)
Planning Time: 0.277 ms
Execution Time: 0.021 ms
Nota

Com o banco da UnDF (18 alunos, 7 professores), o planner PostgreSQL tende a usar sequential scan porque as tabelas são pequenas demais para justificar o overhead de acessar um índice. Em produção, com milhares ou milhões de linhas, índices fazem diferença substancial.