Reading a SQL execution plan: how to find why a query is slow (and fix it) across engines

Read a SQL execution plan to find why a query is slow: scan vs seek, estimated vs actual rows, spills and missing indexes — across five engines.

Author: Łukasz Żochowski

  • #sql
  • #performance
  • #postgresql
  • #sqlserver
  • #optimization

A slow query rarely tells you why it is slow. The execution plan does. It is the step-by-step recipe the database’s optimizer chose for your SQL — which tables it touches, in what order, which indexes it used (or ignored), and where the time actually went. Learning to read it is the single highest-leverage skill in SQL performance work, and the fundamentals transfer across every engine.

This guide shows you how to generate a plan in PostgreSQL, SQL Server, MySQL/MariaDB, Oracle and SQLite, and — more importantly — what to look at once you have one.

Why is my SQL query slow? Read the plan, not the query

Staring at the SQL text and guessing is the most common and least productive way to tune a query. The optimizer has already made its decisions; your job is to inspect them. A plan answers three questions that the query text cannot:

  1. Access method — did the engine seek to the rows it needed, or scan a whole table/index to find them?
  2. Estimates vs reality — did the optimizer expect 10 rows and get 10 million? Bad estimates cause bad plans.
  3. Expensive operations — sorts, hash joins and aggregates that spilled to disk, or a nested loop executed millions of times.

Almost every slow query traces back to one of these three. Keep them in mind as you read.

How do you generate an execution plan in each engine?

There is a crucial split: estimate-only commands do not run your query, while “analyze” variants execute it and report real numbers. On a DELETE or UPDATE, that difference is not academic.

PostgreSQL — EXPLAIN estimates; EXPLAIN ANALYZE runs it and adds actual timings. Add BUFFERS to see I/O:

EXPLAIN SELECT ...;                       -- plan + estimates, does NOT run
EXPLAIN ANALYZE SELECT ...;               -- runs it: actual time + rows
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;    -- + shared/read buffer accounting

(PostgreSQL docs: EXPLAIN, Using EXPLAIN. From PostgreSQL 18, BUFFERS is on by default with ANALYZE.)

SQL Server — the estimated plan never runs the query; the actual plan does:

SET SHOWPLAN_XML ON;      -- estimated plan as XML, does NOT run the query
SET STATISTICS XML ON;    -- runs each statement, returns actual plan + metrics

In SSMS, press Ctrl+L for the estimated plan or Ctrl+M (“Include Actual Execution Plan”) then run (SET STATISTICS XML, Display an actual execution plan).

MySQL 8.0.18+ — EXPLAIN estimates; EXPLAIN ANALYZE runs the query and prints a tree with estimated and actual numbers side by side:

EXPLAIN SELECT ...;              -- estimate (add FORMAT=TREE or FORMAT=JSON)
EXPLAIN ANALYZE SELECT ...;      -- runs it: actual time, rows, loops per node

EXPLAIN ANALYZE was added in 8.0.18 (MySQL: EXPLAIN ANALYZE blog, EXPLAIN reference). MariaDB spells the executing form ANALYZE SELECT ... and adds an r_rows (actual) column next to rows (estimated).

Oracle — two steps for the estimate; a hint plus DISPLAY_CURSOR for actuals:

-- Estimate only:
EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Actuals (run with the hint, then display the cursor):
SELECT /*+ GATHER_PLAN_STATISTICS */ ... FROM ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));

The actuals form gives you side-by-side E-Rows (estimated) and A-Rows (actual) per step — the most valuable thing in an Oracle plan (Oracle Optimizer blog: how to generate a useful plan, use-the-index-luke: Oracle EXPLAIN PLAN).

SQLite — lightweight and estimate-only:

EXPLAIN QUERY PLAN SELECT ...;

(SQLite: EXPLAIN QUERY PLAN)

What’s the difference between a scan and a seek?

This is the most fundamental distinction in any plan. A seek uses an index (or a key) to jump straight to the rows that match — work proportional to the rows returned. A scan reads an entire table or index and discards what doesn’t match — work proportional to the table size.

  • SQL Server: Index Seek / Clustered Index Seek (targeted, usually good) vs Index Scan, Clustered Index Scan, Table Scan (reads everything).
  • PostgreSQL: Index Scan / Index Only Scan / Bitmap Index Scan vs Seq Scan (sequential = full table).
  • Oracle: INDEX RANGE SCAN/UNIQUE SCAN vs TABLE ACCESS FULL.
  • SQLite: SEARCH ... USING INDEX (and USING COVERING INDEX) vs SCAN (full table).

A scan is not automatically bad. When a query genuinely needs most of the table — a report over all of last year — a scan is the right choice and an index seek would be slower. A scan is a problem when you expected a seek: a selective WHERE id = 42 that scans a million-row table means the index is missing, unusable (e.g. a function wraps the column: WHERE UPPER(email) = ...), or the optimizer mis-estimated and chose wrong.

Estimated vs actual rows: the number that explains most bad plans

The optimizer picks its strategy from estimates. If the estimate is wildly wrong, every downstream decision — join type, join order, memory grant — is built on a bad assumption. That is why the estimated-vs-actual comparison is the first thing experienced tuners look at:

  • PostgreSQL EXPLAIN ANALYZE: rows= (estimate) vs actual ... rows=.
  • Oracle: E-Rows vs A-Rows.
  • SQL Server actual plan: “Estimated Number of Rows” vs “Actual Number of Rows” on each operator.
  • MariaDB ANALYZE: rows vs r_rows.

A gap of one or two orders of magnitude is the smoking gun. Common causes: stale statistics (fix with ANALYZE / UPDATE STATISTICS / DBMS_STATS), correlated columns the optimizer assumes are independent, or non-sargable predicates. Here a Postgres estimate is off by 1000x:

Seq Scan on orders  (cost=0.00..18334.00 rows=1000 width=52)
                    (actual time=0.015..512.3 rows=1000000 loops=1)

Expected 1,000 rows, got 1,000,000 — and it chose a sequential scan to boot. That mismatch is your lead.

What are spills, sorts and hashes telling you?

When an operator needs more working memory than it is granted, it spills to disk — and disk is orders of magnitude slower than memory. Plans say so explicitly:

  • PostgreSQL shows the sort method: Sort Method: quicksort Memory: 25kB (good) versus Sort Method: external merge Disk: 10240kB (spilled). Hash joins report Batches: greater than 1 when they spill.
  • SQL Server flags a spill with a yellow warning triangle on the Sort or Hash operator in the actual plan (“operator used tempdb to spill data”).

A spilling sort usually means either the query sorts far more than it needs (sort before you filter?) or the engine under-estimated row counts and granted too little memory — which loops you right back to the estimate problem above. The fix is often an index that provides the rows already ordered, removing the sort entirely.

What does the plan say about missing indexes?

Some engines suggest indexes directly. SQL Server prints a green “Missing Index” hint in the actual plan with a ready-made CREATE INDEX statement; PostgreSQL has no built-in suggester but a Seq Scan with a selective filter on an unindexed column is the same signal. Treat suggestions as a starting point, never a blind CREATE:

  • The suggested index may duplicate an existing one with slightly different column order.
  • Each index you add slows every INSERT/UPDATE/DELETE and costs storage.
  • The real fix is sometimes to rewrite a non-sargable predicate (WHERE UPPER(email) = ... → store normalized, or add a functional index) so an existing index becomes usable.

How do you read plans consistently across different engines?

The concepts above are universal, but the commands, output format and keywords are not — Seq Scan, TABLE ACCESS FULL and SCAN all mean the same thing, and the actual-vs-estimated numbers live in a different column in every tool. If you work across engines, that translation tax is real — and it is not limited to query plans: pagination, identifier quoting and data types differ across engines just as much.

Disclosure: this blog is published by LumaSoft (LUMA sp. z o.o.), and I work on the tool mentioned here.

SQL Sage for DBeaver is a plugin that reads the query plan inside DBeaver and is dialect-aware across the same five engines — PostgreSQL, MySQL/MariaDB, Oracle, SQL Server and SQLite — so the plan it shows and explains matches the engine you are connected to. A few facts stated plainly: it is Windows-only (Linux and macOS are not supported), it is keyless (you sign in with your own AI account, no API key), and it is read-only by default. It is not affiliated with DBeaver, Oracle or Microsoft.

The takeaway: a checklist for any plan

Whatever engine and whatever tool, read every plan the same way:

  1. Generate the actual plan (EXPLAIN ANALYZE / actual execution plan / DISPLAY_CURSOR), not just the estimate — real numbers beat guesses.
  2. Check estimated vs actual rows. A large gap is usually the root cause; suspect stale statistics first.
  3. Find the scans. Ask whether each one is justified by how many rows the query truly needs.
  4. Look for spills — disk sorts and multi-batch hashes mean memory pressure or over-sorting.
  5. Treat missing-index hints as a hypothesis, not a command.

Do this a dozen times and plan-reading stops being arcane. The query stops being a mystery and becomes a recipe you can read — and fix.

← Back to the blog