Coleta de Estatísticas de Uso de Índices e Tabelas

Imagina que seu banco de dados é tipo um grande depósito. Índices são os catálogos e listas que ajudam a achar o que você precisa rapidinho. Tabelas são os produtos nas prateleiras. Se um índice é pouco usado, é como se o catálogo estivesse jogado num canto e ninguém nunca olhasse pra ele. Se uma tabela é muito usada, mas tem estrutura ruim ou dados demais, isso pode sobrecarregar o depósito (banco de dados) e deixar tudo mais lento.

Principais objetivos da análise:

1. Avaliar a eficiência do uso dos índices. Por exemplo, aquele índice caríssimo tá só ocupando espaço? Pode jogar fora!
2. Descobrir a frequência das operações de leitura e escrita. Ajuda a entender quais tabelas são mais usadas.
3. Otimizar queries. A estatística mostra onde dá pra acelerar o processamento dos dados, adicionando ou mudando índices.

Views pg_stat_user_indexes e pg_stat_user_tables

No PostgreSQL tem duas views muito úteis pra coletar estatísticas: pg_stat_user_indexes e pg_stat_user_tables. Bora ver como elas funcionam.

pg_stat_user_indexes: como os índices são usados?

Campos principais:

• relname — nome da tabela relacionada ao índice.
• indexrelname — nome do índice.
• idx_scan — quantas vezes o índice foi usado pra buscar dados.
• idx_tup_read — quantidade de linhas lidas usando o índice.
• idx_tup_fetch — quantidade de linhas realmente retornadas (depois dos filtros).

Exemplo de query:

SELECT relname AS table_name, 
       indexrelname AS index_name, 
       idx_scan AS index_scans, 
       idx_tup_read AS index_tuples_read, 
       idx_tup_fetch AS index_tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

Aqui a gente:

• ordena os dados pelo número de vezes que o índice foi chamado (idx_scan), pra ver quais são os mais populares.
• se o índice quase não é usado (idx_scan = 0), vale pensar: será que ele precisa existir?

Aplicação prática:

Você faz deploy de uma nova versão do app e adicionou um índice novo. Com o pg_stat_user_indexes dá pra checar se sua query realmente começou a usar o índice novo, ou se o PostgreSQL ainda tá ignorando sua obra-prima de otimização.

pg_stat_user_tables: vendo os dados das tabelas

Campos principais:

• relname — nome da tabela.
• seq_scan — número de varreduras sequenciais na tabela (sem usar índices).
• seq_tup_read — quantidade de linhas retornadas nas varreduras sequenciais.
• idx_scan — número de buscas por índice na tabela.
• n_tup_ins — quantidade de linhas inseridas
• n_tup_upd — quantidade de linhas atualizadas.
• n_tup_del — quantidade de linhas deletadas.

Exemplo de query:

SELECT relname AS table_name, 
       seq_scan AS sequential_scans, 
       idx_scan AS index_scans, 
       n_tup_ins AS rows_inserted, 
       n_tup_upd AS rows_updated, 
       n_tup_del AS rows_deleted
FROM pg_stat_user_tables
ORDER BY sequential_scans DESC;

O que dá pra ver aqui?

• Tabelas com muitos scans sequenciais (seq_scan) podem estar precisando de um índice.
• O número de inserts, updates e deletes ajuda a entender o quanto os dados mudam na tabela.

Aplicação prática: Você trabalha com a tabela users, onde ficam os dados de todos os usuários do seu app. Com o pg_stat_user_tables você percebe que os scans sequenciais (seq_scan) dessa tabela estão explodindo. Isso é um sinal: tá na hora de criar índices nas colunas mais usadas pra acelerar as queries.

Exemplo: analisando índices e tabelas num banco real

Vamos supor que temos um banco com as tabelas orders (pedidos) e products (produtos). Queremos entender como as tabelas e índices estão sendo usados.

Análise dos índices:

SELECT relname AS table_name, 
       indexrelname AS index_name, 
       idx_scan AS index_scans, 
       idx_tup_read AS tuples_read, 
       idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY index_scans DESC;

Você vê que o índice orders_customer_id_idx foi chamado 50 mil vezes, mas o orders_date_idx só 5 vezes. Talvez o orders_date_idx seja desnecessário.

Análise das tabelas:

SELECT relname AS table_name, 
       seq_scan AS sequential_scans, 
       seq_tup_read AS tuples_read, 
       idx_scan AS index_scans, 
       n_tup_ins AS rows_inserted, 
       n_tup_upd AS rows_updated, 
       n_tup_del AS rows_deleted
FROM pg_stat_user_tables
WHERE relname IN ('orders', 'products')
ORDER BY seq_scan DESC;

A tabela products tá sempre sofrendo scans sequenciais. Isso é um toque: tá faltando índice no catálogo de produtos.

Erros comuns e como evitar

Uma armadilha clássica pra quem tá começando é ignorar as estatísticas. Por exemplo, você adiciona um índice novo pensando: “Agora as queries vão voar”, mas o PostgreSQL nem usa ele porque as estatísticas não foram atualizadas automaticamente. Depois de grandes mudanças nas tabelas, não esquece de atualizar as estatísticas manualmente com o comando ANALYZE.

Outro erro comum é sair criando índice pra tudo. Lembra que cada índice ocupa espaço no disco e deixa inserts, updates e deletes mais lentos. Use as estatísticas do pg_stat_user_indexes pra garantir que o índice tá sendo realmente usado, e não só ocupando espaço à toa.

Onde esse conhecimento é útil?

No desenvolvimento real: se o banco de dados tá lento, a primeira coisa é procurar problema nas tabelas e índices.

Na entrevista de emprego: perguntas sobre otimização de índices são clássicas em entrevistas de SQL. Sabe explicar o pg_stat_user_indexes? Já tá meio caminho andado.

Na administração de bancos: monitorar é rotina diária de DBA. Sem estatística de tabelas e índices, não tem como melhorar nada.