Índices e performance

Sem índice, WHERE email = 'ana@exemplo.com' numa tabela de 10 milhões de linhas faz o banco olhar linha por linha, do início ao fim, até achar (ou confirmar que não existe) — chamado full table scan. Um índice existe pra evitar exatamente isso.

O que um índice realmente é

Pense num índice remissivo no final de um livro — em vez de ler o livro inteiro procurando "container", você vai direto na página que o índice aponta. Um índice de banco de dados faz a mesma coisa: uma estrutura auxiliar ordenada que aponta direto pra onde cada valor está, sem precisar varrer a tabela inteira.

CREATE INDEX idx_usuarios_email ON usuarios (email);

-- agora esta consulta usa o índice em vez de full table scan:
SELECT * FROM usuarios WHERE email = 'ana@exemplo.com';

O preço de um índice

Índice não é de graça: toda vez que uma linha é inserida/atualizada, o índice também precisa ser atualizado — mais índices significam INSERT/UPDATE um pouco mais lentos, em troca de SELECT muito mais rápido. A regra prática: indexe colunas usadas com frequência em WHERE, JOIN e ORDER BY — não toda coluna da tabela.

Chave primária já vem indexada de graça

CREATE TABLE usuarios (
  id SERIAL PRIMARY KEY, -- já cria um índice automaticamente
  email VARCHAR(255)      -- esse, se buscado com frequência, merece um índice manual
);

EXPLAIN — ver o que o banco realmente faz

EXPLAIN SELECT * FROM usuarios WHERE email = 'ana@exemplo.com';

"Index Scan" confirma que o índice foi usado. Se aparecesse "Seq Scan" (sequential scan), a consulta estaria varrendo a tabela inteira mesmo com o índice existindo — geralmente porque faltou criar o índice na coluna certa, ou porque a tabela é pequena demais pro banco achar que vale a pena usar o índice.