56 lines
1.4 KiB
Markdown
56 lines
1.4 KiB
Markdown
---
|
|
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):**
|
|
|
|
```sql
|
|
-- Database is slow, but which queries are the problem?
|
|
-- No way to know without pg_stat_statements
|
|
```
|
|
|
|
**Correct (enable and query pg_stat_statements):**
|
|
|
|
```sql
|
|
-- 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:
|
|
|
|
```sql
|
|
-- 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](https://supabase.com/docs/guides/database/extensions/pg_stat_statements)
|