≈463 tokens on demand. Your agent reads this file only when SKILL.md points to it.
RDS PostgreSQL — Query Load Analysis & Explain Plan Review
Step 1: Get Top 5 Queries by Load
SELECT queryid, query, calls, total_exec_time::numeric(12,2) AS total_time_ms,
mean_exec_time::numeric(12,2) AS avg_time_ms,
rows, shared_blks_hit, shared_blks_read
FROM pg_stat_statements
WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
ORDER BY total_exec_time DESC LIMIT 5;
Step 2: Run EXPLAIN on Each Query
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) <query>;
For data-modifying queries, wrap in a transaction and rollback.
Step 3: Flag Upgrade-Impacting Patterns
🔴 Critical
| Pattern |
Versions |
Action |
Sort Method: external merge |
PG 15+ |
work_mem per-operation accounting changed. Tune work_mem and hash_mem_multiplier. |
HashAggregate with Batches > 1 |
PG 15+ |
Memory accounting different. Adjust work_mem. |
| JIT on short queries |
PG 14+ |
JIT overhead causes latency spikes. Adjust jit_above_cost thresholds. |
🟡 Warning
| Pattern |
Versions |
Action |
| Nested Loop without Memoize |
PG 14+ |
PG 14 introduced Memoize. Usually beneficial. Set enable_memoize=off if regression. |
| Parallel scan threshold changes |
PG 14-16 |
Plans may gain/lose parallelism. Compare on test instance. |
| Merge Join on large tables |
PG 16+ |
Improved costing may change join strategy. Benchmark. |
🟢 Clean
| Pattern |
Notes |
| Simple Index Scan / Index Only Scan |
Stable across versions. |
| Seq Scan on small tables |
No change. |
Key PostgreSQL Optimizer Changes
- PG 14: Memoize node, improved extended statistics
- PG 15: work_mem hash operation changes, improved sort
- PG 16: Subquery decorrelation, improved merge join costing
- PG 17: Enhanced memory management, improved vacuum
