Core concepts for reading plan trees, understanding cost estimation, and diagnosing join strategies.
Explore section →
Understand Every
Query Execution Plan
Systematic guides for backend engineers, DBAs, and platform teams who need to diagnose
slow queries, tune indexes, and extract real performance gains from PostgreSQL.
PostgreSQL's EXPLAIN and EXPLAIN ANALYZE commands expose exactly how the query planner
resolves your SQL — which access paths it chose, where time was spent, and where estimates diverged from reality.
Reading them accurately is the difference between guessing and knowing why a query is slow.
This site provides systematic diagnostic frameworks for every major execution plan pattern:
sequential scan vs index scan trade-offs, hash join memory spill detection,
index-only scan validation via covering indexes, parallel worker allocation, filter
pushdown verification, sort node spill conditions, materialized view refresh strategy, and more.
It also covers the layers most tuning guides skip — how ORMs like Django,
ActiveRecord, and SQLAlchemy translate object access into N+1 query storms, and how the
runtime environment (connection poolers, prepared-statement plan pinning, and session parameter
drift) changes the plan the server actually executes. Each guide includes real annotated
EXPLAIN ANALYZE output, step-by-step remediation workflows, and the common
pitfalls that send engineers in the wrong direction.
Whether you're chasing a p95 latency regression, designing a partial index, hunting an N+1 query behind an ORM, or diagnosing why the planner ignores your carefully-crafted B-tree — the answers are in the plan.
Start Here
New to execution plan analysis? These are the highest-leverage pages to read first — each one equips you with a diagnostic tool you can apply immediately.
- Sequential vs Index Scans — understand when the planner chooses each access path and how to influence the decision
- Cost Estimation Models — how PostgreSQL assigns cost units and why row-estimate accuracy is the root cause of most bad plans
- Identifying Plan Bottlenecks — a systematic method for locating the hot node in any EXPLAIN output
- Covering Index Design — eliminate heap fetches entirely with index-only scans
- Hash Join Mechanics — diagnose memory spill and Batches > 1 before they hit production
- Filter Pushdown Mechanics — distinguish Filter from Index Cond in plan output and understand why it matters
- N+1 Query Detection — spot the ORM query storm that a single EXPLAIN can't reveal, and collapse it into one join
- Prepared Statement Plan Pinning — why a query goes generic after five executions and misfires on skewed parameters
- Materialized View Strategy — precompute expensive aggregates and understand why the planner never rewrites to them automatically
Explore the Guides
Systematic workflows for designing, optimizing, and maintaining indexes to eliminate I/O bottlenecks.
- B-Tree Index Optimization
- Covering Index Design
- Partial Index Implementation
- Specialized Index Types (GIN/GiST)
- Materialized View Strategy
Frameworks for identifying bottlenecks, understanding parallel execution, and validating tuning outcomes.
- Filter Pushdown Mechanics
- Identifying Plan Bottlenecks
- Parallel Query Execution
- Sort and Hash Node Analysis
Diagnose the plan-level cost of ORM abstractions — N+1 query storms, lazy-load artifacts, and the EXPLAIN output that Django, ActiveRecord, and SQLAlchemy generate.
Explore section →How connection pooling, prepared-statement plan pinning, and session-level parameter drift change the plan the server actually executes.
Explore section →