A view pg_stat_statements guarda, para cada consulta, quantas vezes ela rodou e quanto tempo gastou. Ordenada pelo tempo total, ela mostra onde o banco passa o dia.
- Ordene por total_exec_time, não pela consulta mais lenta.
- Compare calls e mean_exec_time para saber onde mexer.
- Zere as estatísticas depois de cada mudança e meça de novo.
O guia do primeiro dia manda colocar o pg_stat_statements em shared_preload_libraries. Esta matéria começa daí: com a extensão carregada, como achar as consultas que mais consomem tempo.
Ative a view no banco
Carregar a biblioteca exige reiniciar o servidor, porque o módulo usa memória compartilhada. Mesmo carregada, a view não aparece sozinha: rode CREATE EXTENSION pg_stat_statements; no banco onde você vai consultar. Ela tem uma linha por consulta, usuário e banco. O texto das consultas de outros usuários só aparece para superusuário ou para quem tem o papel pg_read_all_stats.
Leia pelo tempo total
SELECT calls,
round(total_exec_time::numeric / 1000, 1) AS total_s,
round(mean_exec_time::numeric, 2) AS media_ms,
rows,
left(query, 80) AS consulta
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;O total_exec_time é a soma, em milissegundos, de todas as execuções. Ordenar por ele mostra onde o banco gasta o tempo, e não só a consulta mais lenta. As constantes aparecem trocadas por $1, $2, então a mesma consulta com valores diferentes cai numa linha só.
Muitas chamadas ou uma consulta lenta
Há dois jeitos de chegar ao topo. Com calls alto e mean_exec_time baixo, cada execução é rápida, mas roda demais. O ajuste costuma estar na aplicação: uma consulta dentro de um laço, um ORM que busca linha por linha, um dado que podia ficar em cache.
Com calls baixo e mean_exec_time alto, cada execução pesa. Rode EXPLAIN (ANALYZE, BUFFERS) com valores reais no lugar dos $1 e veja onde o plano gasta: uma leitura sequencial de tabela grande pode indicar que falta um índice. O ANALYZE executa a consulta de verdade, então, em UPDATE ou DELETE, rode entre BEGIN e ROLLBACK.
Essas consultas também prendem conexões. Se o PgBouncer mostra clientes na fila, as primeiras da lista são as suspeitas.
Zere e meça de novo
Os números acumulam desde a última limpeza e, por padrão, são gravados quando o servidor desliga e voltam quando ele sobe. Depois de criar um índice ou mudar a aplicação, rode SELECT pg_stat_statements_reset();, que por padrão só o superusuário executa, e compare depois de um período de uso parecido. A cópia restaurada no ensaio de recuperação serve para testar o índice com o volume real antes de mexer em produção.
