Skip to content

Click to use (opens in a new tab)

What is a Query Execution Plan

Introduction to Query Execution Plans

A query execution plan (also called an explain plan) is the sequence of steps the database engine uses to execute a SQL query. It describes how tables are accessed (full scan or index), in which order joins are performed, and where filtering, sorting, and aggregation happen. Reading execution plans is the single most effective way to understand and fix slow queries.

How to View an Execution Plan

Most databases expose the plan through an EXPLAIN command.

EXPLAIN SELECT o.order_id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'shipped';

PostgreSQL can also execute the query and report actual timings:

EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'shipped';

Key Elements of an Execution Plan

  • Access method: whether a table is read with a sequential/full table scan or through an index (index scan, index seek, index-only scan).
  • Join strategy: nested loop join, hash join, or merge join, and the order in which tables are joined.
  • Estimated rows and cost: the optimizer's estimate of how many rows each step produces and how expensive the step is.
  • Sort and aggregation steps: operations such as ORDER BY, GROUP BY, and DISTINCT that may spill to disk on large data sets.

Why Execution Plans Matter

The query optimizer chooses a plan based on table statistics. When statistics are stale or an index is missing, the optimizer may pick a poor plan — for example, a full table scan over millions of rows instead of an index lookup. Comparing estimated rows in the plan against actual rows is the fastest way to spot such problems.

Common Optimizations Guided by Plans

  • Add or adjust indexes so frequent filters and join keys avoid full table scans.
  • Rewrite queries to avoid functions on indexed columns, which prevent index usage.
  • Refresh table statistics (ANALYZE in PostgreSQL, ANALYZE TABLE in MySQL) so the optimizer has accurate row estimates.
  • Reduce the data set early with selective WHERE conditions before joining.

Analyzing Plans with Chat2DB

Chat2DB (opens in a new tab) can run EXPLAIN for you and use AI to interpret the result: it highlights full table scans, suggests missing indexes, and proposes rewritten SQL, turning a raw execution plan into concrete optimization steps.