postgresql.conf - Query Planner Optimization Profile

import { Client } from 'pg';

const client = new Client({ connectionString: process.env.DATABASE_URL });
await client.connect();

const query = `
  EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
  SELECT o.id, u.email, SUM(li.price * li.quantity) as total
  FROM orders o
  JOIN users u ON u.id = o.user_id
  JOIN line_items li ON li.order_id = o.id
  WHERE o.created_at >= NOW() - INTERVAL '30 days'
  GROUP BY o.id, u.email;
`;

const res = await client.query(query);
const plan = res.rows[0]['EXPLAIN'][0];
console.log(JSON.stringify(plan, null, 2));
await client.end();

ANALYZE VERBOSE orders;
ANALYZE VERBOSE line_items;

-- Verify histogram accuracy
SELECT attname, n_distinct, most_common_vals

, histogram_bounds FROM pg_stats WHERE tablename = 'orders' AND attname = 'created_at';

### Step 3: Restructure for Planner Compatibility
The optimizer struggles with:
- Functions applied to indexed columns (`WHERE DATE(created_at) = ...`)
- Implicit type casting (`WHERE user_id = '123'` when column is integer)
- Unnecessary CTEs that force materialization barriers (PostgreSQL < 12)
- Cartesian joins missing explicit `ON` conditions

Rewrite the query to expose filter predicates early, use explicit join syntax, and avoid runtime transforms:

```sql
SELECT o.id, u.email, SUM(li.price * li.quantity) as total
FROM orders o
INNER JOIN users u ON u.id = o.user_id
INNER JOIN line_items li ON li.order_id = o.id
WHERE o.created_at >= '2024-01-01'::timestamp
  AND o.status = 'completed'
GROUP BY o.id, u.email;

Step 4: Implement Strategic Indexes

Indexes are not free. They consume I/O, increase write latency, and consume memory. Create indexes only when selectivity < 15% and query frequency justifies maintenance overhead. Use covering indexes to eliminate heap fetches:

CREATE INDEX idx_orders_created_status_user 
ON orders (created_at, status, user_id) 
INCLUDE (id);

CREATE INDEX idx_line_items_order_id_price 
ON line_items (order_id) 
INCLUDE (price, quantity);

Step 5: Enforce Plan Stability

PostgreSQL caches plans but may switch strategies when statistics shift. Use plan_cache_mode = force_custom_plan for highly variable queries, or deploy pg_hint_plan for deterministic execution paths in critical workloads:

-- Enable hint plan extension
CREATE EXTENSION IF NOT EXISTS pg_hint_plan;

-- Force hash join and disable sequential scan
/*+ HashJoin(o u) NoSeqScan(o) */
SELECT o.id, u.email, SUM(li.price * li.quantity) as total
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN line_items li ON li.order_id = o.id
WHERE o.created_at >= NOW() - INTERVAL '30 days'
GROUP BY o.id, u.email;

Architecture Decisions

• CTE vs Subquery: Use CTEs for readability in analytical queries. Use subqueries or LATERAL joins for row-by-row dependencies. PostgreSQL 12+ inlines non-recursive CTEs by default, removing materialization overhead.
• Partitioning: Apply range or list partitioning when tables exceed 50M rows and queries consistently filter on partition keys. Ensure partition pruning is triggered by verifying Subplans Removed in EXPLAIN output.
• Connection Pooling: Pair optimized queries with connection poolers (PgBouncer, ProxySQL). Plan stability reduces connection churn and prevents thread starvation during plan recompilation.

Pitfall Guide

1. Confusing EXPLAIN with EXPLAIN ANALYZE

EXPLAIN shows estimated costs. EXPLAIN ANALYZE executes the query and reports actual runtime metrics. Estimates diverge from reality when statistics are stale or data is skewed. Always validate plans with ANALYZE in staging environments with production-scale data.

2. Indexing Without Selectivity Validation

Adding indexes to low-selectivity columns (e.g., status, is_active) forces the planner to choose sequential scans anyway. The optimizer will ignore indexes when fetching > 5-10% of table rows. Validate selectivity using n_distinct in pg_stats before creating indexes.

3. Applying Functions to Indexed Columns

WHERE LOWER(email) = 'user@example.com' disables index usage. The planner cannot use B-tree indexes on transformed columns. Use functional indexes (CREATE INDEX idx_users_lower_email ON users (LOWER(email))) or store normalized values at write time.

4. Ignoring Statistics Drift in High-Velocity Tables

Tables with frequent inserts, updates, or deletes accumulate stale statistics. The planner may underestimate row counts, choosing nested loops over hash joins. Schedule ANALYZE after bulk operations or configure autovacuum_analyze_scale_factor to 0.01 for volatile tables.

5. Assuming ORMs Generate Planner-Friendly SQL

ORMs prioritize developer ergonomics over execution efficiency. They often generate redundant joins, implicit casts, or N+1 patterns that bypass index usage. Use raw SQL for complex analytical queries, or enable ORM query logging to inspect generated statements against EXPLAIN output.

6. Overusing CTEs in Transactional Workloads

CTEs introduce materialization barriers in older database versions. Even in modern engines, recursive or non-inlined CTEs force intermediate result caching, increasing memory pressure. Replace CTEs with LATERAL joins or temporary tables when processing > 100K rows.

7. Hardcoding Planner Parameters Without Workload Profiling

Tuning work_mem, random_page_cost, or effective_cache_size without baseline metrics causes plan instability. Changes that improve one query may degrade another. Use pg_stat_statements to identify top consumers, then adjust parameters per workload class, not globally.

Best Practices

• Maintain a query plan registry with versioned EXPLAIN ANALYZE outputs
• Automate plan regression alerts when actual vs estimated row divergence exceeds 3x
• Test query changes against production-like data volumes; dev environments misrepresent cardinality
• Monitor pg_stat_user_indexes to drop unused indexes and reduce write amplification
• Document plan stabilization strategies for compliance and audit trails

Production Bundle

Action Checklist

• Baseline critical queries with EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
• Refresh statistics on high-churn tables before optimization cycles
• Remove functions/transforms from WHERE clauses on indexed columns
• Validate selectivity and drop indexes with < 15% hit rate
• Configure pg_stat_statements and track top 20 resource consumers
• Implement plan stability controls for latency-sensitive endpoints
• Schedule quarterly plan regression audits with production-scale test data

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
High-volume OLTP with simple filters	B-tree indexes + plan_cache_mode = force_custom_plan	Prevents plan caching overhead on variable parameters	Reduces CPU by 40-60%
Analytical queries on > 50M rows	Partitioning + hash joins + increased work_mem	Enables partition pruning and parallel execution	Lowers I/O costs by 3-5x
ORM-generated complex joins	Raw SQL rewrite + CTE inlining + statistics refresh	Eliminates redundant joins and materialization barriers	Cuts memory footprint by 70%
Frequent bulk inserts/updates	Deferred ANALYZE + autovacuum tuning + covering indexes	Balances write throughput with planner accuracy	Prevents plan regression during peak loads

Configuration Template

# postgresql.conf - Query Planner Optimization Profile
shared_buffers = '4GB'
effective_cache_size = '12GB'
work_mem = '64MB'
maintenance_work_mem = '1GB'
random_page_cost = 1.1
effective_io_concurrency = 200
default_statistics_target = 100
plan_cache_mode = auto

# Statistics & Autovacuum
autovacuum = on
autovacuum_max_workers = 4
autovacuum_naptime = 30s
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.02
autovacuum_analyze_scale_factor = 0.01

# Query Monitoring
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
track_activities = on
track_counts = on
track_io_timing = on

Quick Start Guide

1. Connect & Capture: Run EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) against your slowest query. Save the JSON output to a version-controlled directory.
2. Refresh & Validate: Execute ANALYZE VERBOSE <table_name> on all tables in the query. Verify n_distinct and histogram bounds in pg_stats.
3. Rewrite & Index: Remove functions from filter columns. Add covering indexes only for selectivity < 15%. Re-run EXPLAIN ANALYZE and confirm join algorithm shift and reduced logical reads.
4. Stabilize & Monitor: Enable pg_stat_statements. Set plan_cache_mode based on query variability. Schedule automated plan regression checks using a CI/CD pipeline or cron job.