1---
2title: Enable pg_stat_statements for Query Analysis
3impact: LOW-MEDIUM
4impactDescription: Identify top resource-consuming queries
5tags: pg-stat-statements, monitoring, statistics, performance
6---
7 
8## Enable pg_stat_statements for Query Analysis
9 
10pg_stat_statements tracks execution statistics for all queries, helping identify slow and frequent queries.
11 
12**Incorrect (no visibility into query patterns):**
13 
14```sql
15-- Database is slow, but which queries are the problem?
16-- No way to know without pg_stat_statements
17```
18 
19**Correct (enable and query pg_stat_statements):**
20 
21```sql
22-- Enable the extension
23create extension if not exists pg_stat_statements;
24 
25-- Find slowest queries by total time
26select
27  calls,
28  round(total_exec_time::numeric, 2) as total_time_ms,
29  round(mean_exec_time::numeric, 2) as mean_time_ms,
30  query
31from pg_stat_statements
32order by total_exec_time desc
33limit 10;
34 
35-- Find most frequent queries
36select calls, query
37from pg_stat_statements
38order by calls desc
39limit 10;
40 
41-- Reset statistics after optimization
42select pg_stat_statements_reset();
43```
44 
45Key metrics to monitor:
46 
47```sql
48-- Queries with high mean time (candidates for optimization)
49select query, mean_exec_time, calls
50from pg_stat_statements
51where mean_exec_time > 100  -- > 100ms average
52order by mean_exec_time desc;
53```
54 
55Reference: [pg_stat_statements](https://supabase.com/docs/guides/database/extensions/pg_stat_statements)
56