What is a Query Plan

A query plan is the step-by-step execution strategy the database chooses for a SQL query. When you write SELECT * FROM orders WHERE customer_id = 42 ORDER BY created_at, you describe what you want. The query planner decides how to get it — which index to use, whether to scan the table, how to sort, and in what order to join tables.

How it works

The query planner takes a parsed SQL statement and generates multiple candidate plans. Each plan is a tree of operations: table scans, index lookups, hash joins, merge joins, sorts, and filters. The planner estimates the cost of each plan using statistics — row counts, value distributions, index cardinality — stored in system catalogs.

The plan with the lowest estimated cost wins. Common operations include:

• Sequential scan — read every page in the table. Cheap for small tables or when most rows match.
• Index scan — traverse a B-tree index to find matching rows. Efficient when few rows match.
• Index-only scan — the index contains all requested columns, so the table itself is never read.
• Nested loop join — for each row in the outer table, look up matching rows in the inner table. Good when the inner table has an index.
• Hash join — build a hash table from the smaller table, probe it with the larger table. Good for large unsorted joins.

You can see the plan with EXPLAIN (estimated) or EXPLAIN ANALYZE (actual execution with timings). The output shows each operation, its estimated cost, row counts, and — with ANALYZE — actual time spent. This is the primary tool for diagnosing slow queries.

When the planner chooses badly — usually because statistics are stale — queries run orders of magnitude slower than they should. Running ANALYZE updates the statistics.

Why it matters

SQL is declarative: you say what, not how. The query planner is the component that bridges that gap. Understanding query plans is how you diagnose slow queries, decide which indexes to create, and verify that the database is executing your query the way you expect.

See How Query Execution Works for the full pipeline from SQL text to result set.