Usando pg_stat_statements para analisar uso de índices e filtros

Índices são tipo marcadores de página num livro. Eles ajudam a achar rapidinho os dados que tu quer. Mas e se a gente colocou um monte de marcador e ninguém usa? Ou pior, marcadores ruins fazem a gente ter que folhear o livro inteiro? É aí que entra a necessidade de analisar o uso dos índices.

Queries mal feitas podem ignorar os índices, o que leva a scans sequenciais caros (Seq Scan). Isso deixa as queries mais lentas e aumenta a carga no servidor. Nosso objetivo é entender quais queries não usam índices e por quê.

Como saber se os índices estão sendo usados?

Bora ver dois problemas principais:

1. Os índices que a gente criou estão sendo usados?
2. Se estão, eles são eficientes?

Pra isso, dá pra analisar as estatísticas das queries no pg_stat_statements, olhando pra algumas colunas:

• rows: quantidade de linhas processadas pela query.
• shared_blks_hit: quantidade de páginas lidas da memória (e não do disco).
• shared_blks_read: quantidade de páginas realmente lidas do disco.

Quanto menos linhas a query processa, e quanto maior a proporção de shared_blks_hit em relação ao total de páginas, melhor tá funcionando nosso índice.

Exemplo de análise de indexação

Imagina que a gente tem uma tabela de estudantes:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    grade_level INTEGER
);

-- Vamos adicionar um índice em grade_level
CREATE INDEX idx_grade_level ON students(grade_level);

Agora vamos inserir uns dados pra testar:

INSERT INTO students (first_name, last_name, birth_date, grade_level)
SELECT 
    'Estudante ' || generate_series(1, 100000),
    'Sobrenome',
    '2000-01-01'::DATE + (random() * 3650)::INT,
    floor(random() * 12)::INT
FROM generate_series(1, 100000);

Vamos rodar uma query pra achar estudantes de um certo nível:

SELECT *
FROM students
WHERE grade_level = 10;

Checando no pg_stat_statements

Depois de rodar a query algumas vezes, dá pra checar as estatísticas:

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level = 10%';

Como interpretar o resultado:

• rows: Se a query retorna linhas demais, será que o índice faz sentido? Talvez não vale a pena pra condições com baixa seletividade.
• shared_blks_hit e shared_blks_read: Se muita página tá sendo lida do disco (shared_blks_read), ou o índice não tá funcionando ou os dados tão fora do buffer pool.

Otimização de indexação

Criar índice é só metade do caminho. O importante é o PostgreSQL realmente usar ele. Às vezes, mesmo com tudo certinho, o banco resolve ignorar o índice e faz scan sequencial na tabela toda. Por quê? Bora entender.

Primeiro vamos ver por que o índice pode ser ignorado mesmo quando parece que ele seria útil. Depois, algumas dicas pra fazer o banco "lembrar" que o índice existe e usar ele direito.

E se o índice não for usado?

Às vezes o PostgreSQL ignora o índice e faz scan sequencial (Seq Scan). Pode ser por vários motivos:

1. Baixa seletividade da condição. Se a query retorna mais da metade das linhas da tabela, scan sequencial pode ser mais rápido.
2. Tipo de dado ou funções. Se tu usa função na coluna indexada, o índice pode ser ignorado. Por exemplo:

   SELECT *
   FROM students
   WHERE grade_level + 1 = 11; -- Índice não é usado

Nesses casos, dá pra reescrever a query:

   SELECT * 
   FROM students
   WHERE grade_level = 10; -- Usa o índice

3. Tipo de índice errado. Por exemplo, pra busca full-text é melhor usar índice GIN ou GiST, não B-TREE.

4. Estatísticas desatualizadas. Se as estatísticas tão velhas, o otimizador pode tomar decisões ruins. Usa ANALYZE:

   ANALYZE students;
   

Melhorando a query

Voltando pro nosso exemplo. Se o índice não tá funcionando, tenta essas dicas:

1. Confere se a query usa filtros que podem usar o índice: não usa funções, conversão de tipo, etc.
2. Se o filtro retorna muitos valores, pensa se o índice é necessário. Se for uma query frequente, tenta mudar a estrutura da tabela ou criar views materializadas.
3. Se tá rolando Seq Scan por causa do volume de dados, tenta particionar a tabela (PARTITION BY).

Checando a eficiência da indexação

Depois de otimizar, roda a query de novo e confere as estatísticas:

SELECT query, calls, rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE query LIKE '%grade_level%';

Compara as métricas antes e depois. Tu deve ver menos leitura de disco (shared_blks_read) e mais hits (shared_blks_hit).

Casos reais

1. Uso errado do índice

A gente tem uma tabela de produtos com um campo de texto description:

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    description TEXT
);

-- Índice pra busca full-text
CREATE INDEX idx_description ON products USING GIN (to_tsvector('english', description));

Se a gente faz essa query:

SELECT *
FROM products
WHERE description ILIKE '%smartphone%';

O índice não vai ser usado! O motivo é que ILIKE não funciona com GIN. Pra usar o índice, tem que reescrever a query:

SELECT *
FROM products
WHERE to_tsvector('english', description) @@ to_tsquery('smartphone');

2. Falta de índice onde precisa

Imagina essa query:

SELECT *
FROM students
WHERE birth_date BETWEEN '2001-01-01' AND '2003-01-01';

fazendo scan sequencial (Seq Scan). Isso pode ser porque não tem índice em birth_date. Criando o índice:

CREATE INDEX idx_birth_date ON students(birth_date);

e rodando as estatísticas de novo (ANALYZE students), tu consegue acelerar bastante essa query.