Aurora PostgreSQL — Query Load Analysis & Explain Plan Review
Purpose
Identify the top queries generating load, run EXPLAIN on them, and flag plan patterns that behave differently after a major version upgrade (e.g., PG 14→15, 15→16, 16→17).
Step 1: Get Top 5 Queries by Load
Using pg_stat_statements (must be enabled)
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;Fallback: pg_stat_activity snapshot (running queries)
SELECT pid, now() - query_start AS duration, state, query
FROM pg_stat_activity
WHERE state = 'active' AND query NOT LIKE '%pg_stat_activity%'
ORDER BY duration DESC LIMIT 5;Step 2: Run EXPLAIN on Each Query
For each Step 1 query, run:
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) <query>;For data-modifying queries (INSERT/UPDATE/DELETE), wrap in a rollback:
BEGIN;
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) <query>;
ROLLBACK;For parameterized queries ($1, $2...), substitute representative values or use:
EXPLAIN (FORMAT JSON) <query with literal values>;Step 3: Flag Upgrade-Impacting Patterns
Analyze each EXPLAIN output for these patterns, by target version:
🔴 Critical — Behavior Changes That Impact Performance
| Pattern in EXPLAIN | Versions Affected | Why It Matters | Action |
|---|---|---|---|
Sort Method: external merge (disk sort) |
PG 15+ | PG 15 replaced polyphase merge sort with a balanced k-way merge and improved on-disk sorts exceeding work_mem; large sorts may spill differently. Per-operation hash memory accounting via hash_mem_multiplier arrived in PG 13; PG 15 only raised its default from 1.0 to 2.0, so hash-heavy plans get ~2x work_mem after a 14→15 upgrade. |
Tune work_mem and hash_mem_multiplier. Test on snapshot cluster. |
HashAggregate with Batches > 1 (spilling) |
PG 15+ | Hash aggregation disk spill improved but changed; memory accounting differs. | Monitor temp_blks_written. Tune work_mem or hash_mem_multiplier. |
Nested Loop with high actual rows on inner |
PG 14+ | PG 14 introduced enable_memoize; optimizer may add Memoize nodes changing plan shape. |
Usually beneficial. If regression, set enable_memoize=off per query. |
JIT compilation on short queries |
PG 12+ (any upgrade) | JIT (LLVM expression/tuple compilation) arrived in PG 11, enabled by default since PG 12. On short/OLTP queries the planner can still trigger JIT when estimated cost exceeds jit_above_cost, adding compilation latency. Not PG14-specific — verify JIT thresholds on any upgrade from PG 12 onward. |
Adjust jit_above_cost, jit_inline_above_cost, jit_optimize_above_cost or disable JIT for OLTP. |
🟡 Warning — Optimizer Behavior Differences
| Pattern in EXPLAIN | Versions Affected | Why It Matters | Action |
|---|---|---|---|
Parallel Seq Scan or Parallel Hash Join |
PG 14→15→16 | Parallel thresholds and costing refined each version; plans may gain or lose parallelism. | Compare max_parallel_workers_per_gather. Test on snapshot. |
Index Scan vs Bitmap Index Scan choice |
All major upgrades | Cost model updates may flip index scan strategy. | Compare via EXPLAIN on test cluster. Usually fine. |
Incremental Sort |
PG 13+ | When upgrading from PG 12 or earlier, incremental sort may change plans for ORDER BY with partial indexes. | Usually beneficial. Monitor. |
Merge Join on large tables |
PG 16+ | PG 16 improved merge join costing; may choose merge join where hash join ran before. | Benchmark on test cluster. |
Large Rows Removed by Filter (bad estimates) |
All versions | A major upgrade does NOT carry over optimizer statistics (pg_upgrade does not transfer pg_statistic), so the planner has none until you run ANALYZE. Skipping this can cause severe plan regressions and slow queries. |
Run ANALYZE on all tables post-upgrade. |
SubPlan (correlated scalar subquery) |
Aurora PG 16.8+ (NOT core PG16) | Aurora can transform a single-aggregate correlated subquery in SELECT/WHERE into an outer join, and/or add a Memoize subquery cache. Aurora-specific, from Aurora PostgreSQL 16.8 (Babelfish 4.2.0), controlled by apg_enable_correlated_scalar_transform (default OFF) and apg_enable_subquery_cache (default OFF). Opt-in; do NOT activate automatically on a PG15→16 upgrade. |
Optional: test on a snapshot, then set ON in the parameter group if beneficial. Validate per AWS-documented limitations (aggregate-only, plain equality correlation, no GROUP BY/HAVING). See Aurora correlated subquery optimization. |
🟢 Clean — No Upgrade Impact
| Pattern | Notes |
|---|---|
Simple Index Scan / Index Only Scan |
Stable across versions. |
Seq Scan on small tables |
No behavioral change. |
CTE Scan (non-recursive) |
PG 12+ inlines CTEs by default. |
Append for partitioned tables |
Partition pruning stable since PG 11. |
Step 4: Generate Recommendations
For each flagged query, provide:
- The query (truncated to first 200 chars if long)
- Current stats (calls, avg time, rows, shared buffers)
- The problematic EXPLAIN pattern found
- The version change causing the behavioral difference
- Specific action: parameter to tune, index to add, or test on snapshot cluster
Key PostgreSQL Optimizer Changes by Version
PG 14
- Memoize node for nested loops
- Improved extended statistics
- Better handling for many-connection workloads
PG 15
- Improved sort (balanced k-way merge replaces polyphase merge; leaner in-memory sorts)
hash_mem_multiplierdefault raised 1.0 → 2.0MERGEcommand (SQL-standard merge)pg_stat_statementstracks JIT stats
PG 16
- Improved parallelism for
FULLandRIGHTjoins - Better merge join costing
pg_stat_ioview for I/O statistics- Logical replication improvements
PG 17
- Incremental backup support
- Improved vacuum performance
- Better memory management for large operations
- Enhanced JSON functionality
- Improved planner for complex joins
Important Notes
- Always run
ANALYZEon all tables after a major version upgrade (a major upgrade does not transfer thepg_statistictable, so tables start with no statistics; skipping this can cause severe plan regressions and slow queries) - Consider
REINDEXon critical indexes after upgrade - Monitor
pg_stat_user_tablesfor sequential-scan increases
