1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
| -- Query optimization and performance tuning
-- 1. Identify slow queries with detailed analysis
WITH slow_queries AS (
SELECT
query,
calls,
total_time,
mean_time,
max_time,
stddev_time,
rows,
100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0) AS cache_hit_ratio,
shared_blks_read,
shared_blks_written,
temp_blks_read,
temp_blks_written
FROM pg_stat_statements
WHERE mean_time > 100 -- Queries averaging more than 100ms
OR total_time > 10000 -- Queries with high total time
ORDER BY total_time DESC
)
SELECT
LEFT(query, 100) as query_snippet,
calls,
ROUND(total_time::numeric, 2) as total_time_ms,
ROUND(mean_time::numeric, 2) as mean_time_ms,
ROUND(max_time::numeric, 2) as max_time_ms,
rows,
ROUND(cache_hit_ratio::numeric, 2) as cache_hit_pct,
CASE
WHEN temp_blks_written > 0 THEN 'Uses temp files'
WHEN cache_hit_ratio < 95 THEN 'Poor cache utilization'
WHEN mean_time > 1000 THEN 'Very slow average'
ELSE 'Review for optimization'
END as optimization_hint
FROM slow_queries
LIMIT 20;
-- 2. Index recommendations based on query patterns
WITH table_scans AS (
SELECT
schemaname,
tablename,
seq_scan,
seq_tup_read,
idx_scan,
idx_tup_fetch,
n_tup_ins + n_tup_upd + n_tup_del as modifications
FROM pg_stat_user_tables
WHERE schemaname = 'public'
),
index_candidates AS (
SELECT
schemaname,
tablename,
seq_scan,
seq_tup_read,
CASE
WHEN seq_scan > 0 THEN seq_tup_read / seq_scan
ELSE 0
END as avg_seq_read,
idx_scan,
modifications,
CASE
WHEN seq_scan > idx_scan AND seq_tup_read > 10000
THEN 'High sequential scan activity - consider indexing'
WHEN modifications > (idx_scan + seq_scan) * 10
THEN 'High modification rate - index overhead may be significant'
ELSE 'Normal access pattern'
END as recommendation
FROM table_scans
)
SELECT * FROM index_candidates
WHERE recommendation LIKE 'High%'
ORDER BY avg_seq_read DESC;
-- 3. Connection and lock analysis
WITH connection_analysis AS (
SELECT
datname,
usename,
application_name,
state,
COUNT(*) as connection_count,
AVG(EXTRACT(EPOCH FROM (now() - query_start))) as avg_query_duration,
MAX(EXTRACT(EPOCH FROM (now() - query_start))) as max_query_duration,
SUM(CASE WHEN state = 'idle in transaction' THEN 1 ELSE 0 END) as idle_in_transaction
FROM pg_stat_activity
WHERE pid != pg_backend_pid()
GROUP BY datname, usename, application_name, state
)
SELECT
*,
CASE
WHEN idle_in_transaction > 0 THEN 'Idle transactions detected - may cause blocking'
WHEN max_query_duration > 300 THEN 'Long-running queries detected'
WHEN connection_count > 100 THEN 'High connection count'
ELSE 'Normal'
END as alert
FROM connection_analysis
ORDER BY connection_count DESC;
|
