Monitor Pg Stat Statements

title: Enable pg_stat_statements for Query Analysis impact: LOW-MEDIUM impactDescription: Identify top resource-consuming queries tags: pg-stat-statements, monitoring, statistics, performance

Enable pg_stat_statements for Query Analysis

pg_stat_statements tracks execution statistics for all queries, helping identify slow and frequent queries.

Incorrect (no visibility into query patterns):

-- Database is slow, but which queries are the problem?
-- No way to know without pg_stat_statements

Correct (enable and query pg_stat_statements):

-- Enable the extension
create extension if not exists pg_stat_statements;

-- Find slowest queries by total time
select
  calls,
  round(total_exec_time::numeric, 2) as total_time_ms,
  round(mean_exec_time::numeric, 2) as mean_time_ms,
  query
from pg_stat_statements
order by total_exec_time desc
limit 10;

-- Find most frequent queries
select calls, query
from pg_stat_statements
order by calls desc
limit 10;

-- Reset statistics after optimization
select pg_stat_statements_reset();

Key metrics to monitor:

-- Queries with high mean time (candidates for optimization)
select query, mean_exec_time, calls
from pg_stat_statements
where mean_exec_time > 100  -- > 100ms average
order by mean_exec_time desc;

Reference: pg_stat_statements