Problemas típicos ao trabalhar com índices

Até o computador mais top vai travar se você colocar limonada no tanque em vez de gasolina. Com índices no PostgreSQL é a mesma coisa. Eles são super poderosos, mas tem que usar com inteligência. Bora ver alguns problemas clássicos que rolam com índices.

Problema 1: indexação exagerada

Pra começar, vamos lembrar do que já falamos lá na aula retrasada. Quando você coloca índice demais numa tabela, o PostgreSQL tem que cuidar de todos eles pra manter tudo atualizado. Isso afeta direto as operações de insert, update e delete. Porque cada índice precisa ser atualizado e sincronizado!

Imagina que a gente tem uma tabela students:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(255) UNIQUE,
    age INTEGER,
    grade INTEGER
);

E aí você resolve criar índice em cada coluna “só por garantia”:

CREATE INDEX idx_students_name ON students(name);
CREATE INDEX idx_students_age ON students(age);
CREATE INDEX idx_students_grade ON students(grade);

Agora imagina que você insere 10 mil registros novos. O PostgreSQL não só grava os dados na tabela, mas também tem que atualizar esses três índices. Se tiver muito dado, a velocidade de escrita cai e a performance do sistema vai pro saco.

Como evitar esse problema? Antes de criar um índice, se faz duas perguntas:

1. Com que frequência essa coluna aparece em filtros (WHERE), ordenação (ORDER BY) ou agrupamento (GROUP BY)?
2. Será que a query vai usar esse índice ou vai acabar fazendo um scan completo da tabela?

Se a resposta pras duas for “raro” ou “nunca”, nem cria o índice.

Problema 2: escolher as colunas erradas pra indexar

Criar índice em coluna com pouca variedade de dados é tipo tentar colocar chá numa xícara com tampa: não serve pra nada. Se a coluna só tem 2 ou 3 valores diferentes, o PostgreSQL provavelmente vai preferir escanear a tabela toda do que usar o índice.

Vamos supor que temos a tabela courses:

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    level VARCHAR(10) -- Pode ser só 'Beginner', 'Intermediate' ou 'Advanced'
);

E aí você resolve criar índice na coluna level:

CREATE INDEX idx_courses_level ON courses(level);

Mas a query:

SELECT * FROM courses WHERE level = 'Beginner';

pode nem usar o índice, porque o PostgreSQL vai ver que é mais fácil escanear a tabela toda do que olhar o índice. Isso é ainda mais verdade pra tabelas pequenas e dados com pouca variedade.

Então, índice faz sentido em colunas com alta cardinalidade (ou seja, muitos valores únicos). Pra dados com pouca variedade, melhor usar outras técnicas de otimização, tipo particionamento de tabela.

Problema 3: índices velhos e esquecidos

Às vezes a galera cria índice e esquece de apagar, mesmo quando já não serve pra nada. É igual arquivo largado na área de trabalho: começa com dois, três, cinco... Daqui a pouco você perde tempo só procurando o que precisa. Quem nunca?

Imagina que criamos um índice pra uma funcionalidade antiga, depois mudamos as queries e criamos outro índice. O velho fica lá, ocupando espaço e deixando os inserts mais lentos.

Pra não cair nessa, sempre dá uma olhada e analisa os índices. O PostgreSQL tem uma métrica massa pra isso:

SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS total_scans
FROM
    pg_stat_user_indexes
WHERE
    idx_scan = 0;

Aqui o idx_scan mostra quantas queries usaram cada índice. Se for 0, o índice tá largado e pode ser apagado:

DROP INDEX idx_courses_level;

Problema 4: índices em colunas que mudam toda hora

Se você tem índice numa coluna que vive mudando, o PostgreSQL tem que ficar reconstruindo esse índice toda vez que rola update. Isso pode deixar tudo bem mais lento.

Pensa numa tabela de pedidos:

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status VARCHAR(20), -- Pode mudar várias vezes (tipo "novo", "em andamento", "finalizado")
    total NUMERIC(10, 2)
);

Você cria um índice na coluna status pra filtrar pelos status:

CREATE INDEX idx_orders_status ON orders(status);

Mas se o status muda dezenas de vezes pra cada registro, esse índice só vai atrapalhar a performance.

Pra evitar isso, não cria índice em coluna que muda toda hora. Se realmente precisar, pensa em usar índices parciais:

CREATE INDEX idx_orders_status_partial
ON orders(status) 
WHERE status = 'em andamento';

Assim, o índice só é atualizado pros registros com esse valor específico.

Problema 5: Restrições UNIQUE em colunas desnecessárias

Índices únicos (UNIQUE) são criados automaticamente pra garantir que não tenha dado repetido. Mas se não precisa dessa garantia, esses índices só vão pesar no sistema.

Imagina que criamos uma tabela de logs:

CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    message TEXT,
    created_at TIMESTAMP UNIQUE
);

Se a cada segundo entram milhares de registros, garantir unicidade em created_at vai dar um baita trabalho pro banco.

Pra ficar de boa, só coloca UNIQUE onde realmente precisa. No nosso exemplo, se não precisa que created_at seja único, troca por um índice normal:

CREATE INDEX idx_logs_created_at ON logs(created_at);

Problema 6: uso errado de índices combinados

Índices combinados (multi-column indexes) são ótimos quando as queries filtram ou ordenam por várias colunas ao mesmo tempo. Mas tem que criar do jeito certo, senão ficam lá só ocupando espaço.

Imagina que temos esse índice:

CREATE INDEX idx_students_name_grade ON students(name, grade);

Esse índice é usado se a query filtra ou ordena pelas duas colunas:

SELECT * FROM students WHERE name = 'Alice' AND grade = 90;

Mas a query:

SELECT * FROM students WHERE grade = 90;

não usa esse índice, porque o campo name vem primeiro.

Pra não errar, cria índices combinados só na ordem que as queries mais usam. Se precisa filtrar só por uma coluna, faz um índice separado pra ela.

Dicas úteis

Fica de olho no uso dos índices. O PostgreSQL tem a view pg_stat_user_indexes pra ver quais índices tão sendo usados e quais tão largados.

Otimiza as queries junto com os índices. Query ruim continua ruim mesmo com índice.

Não esquece de apagar o que não usa. Índice velho só ocupa espaço e deixa os inserts mais lentos.

É isso aí, galera! Índice é uma ferramenta poderosa, mas lembra sempre: com grande poder vem grande responsabilidade. Usa índice com consciência e seu banco vai voar igual foguete da SpaceX!