Skip to content
notes.vinhnq.com

Search

Database & ORMMid24 min read

How SQL runs and how to read a PostgreSQL execution plan

Review 19 cards
On this page

Query optimization series: 1. How SQL runs and reading execution plans · 2. PostgreSQL index lab on an airline dataset · 3. Join algorithms and partitioning

The same correct query can take 0.5 ms or 900 ms depending on how the database decides to run it, and that decision lives in the execution plan. By the end you’ll be able to read a PostgreSQL plan, find its most expensive node and tell which production queries are worth optimizing. Background reading: Indexes, EXPLAIN, N+1 and pagination (B+trees, clustered vs secondary, composite, covering, sargable predicates, MySQL’s EXPLAIN); that post uses MySQL, this series uses PostgreSQL 16.

1. Two ways to optimize a query

Key idea: direct optimization changes the query or the indexes; indirect optimization changes the data or the way it’s accessed. Always try direct first.

The indirect route includes archiving old data, denormalizing, partitioning, caching, read replicas and defragmenting (VACUUM, OPTIMIZE TABLE).

2. The path a query takes

Key idea: a query goes connection → parser → analyzer/rewriter → planner → executor → storage. The planner is where fast or slow gets decided.

flowchart LR
  C["Client / JDBC"] --> N["Connection<br/>protocol, auth"]
  N --> P["Parser<br/>text → parse tree"]
  P --> A["Analyzer + Rewriter<br/>check tables/columns, expand views"]
  A --> O["Planner<br/>generate and score plans"]
  O --> E["Executor<br/>run the plan tree"]
  E --> S["Access methods<br/>heap, B-tree, GIN"]
  S --> B["Buffer manager<br/>shared_buffers ↔ disk"]
  E -.-> T["Transaction / lock<br/>MVCC, WAL"]
  1. Connection: PostgreSQL forks a process per connection, so opening one is expensive. That’s why you always pool (HikariCP in Spring Boot, PgBouncer on the database side).
  2. The parser checks syntax and produces a parse tree. The analyzer/rewriter looks up the catalog to make sure tables, columns and types exist, replaces views with their definitions and applies row-level security.
  3. The planner enumerates ways to run the query (which scan, which join order, which algorithm), estimates the cost of each from statistics and picks the cheapest. You never see the rejected plans.
  4. The executor runs the plan tree in a “pull” fashion: a parent node asks its children for rows.
  5. Access methods and the buffer manager read 8 KB pages from shared_buffers (PostgreSQL’s own cache), falling back to the OS/disk. Writes go through the WAL (a log written before the data itself); MVCC (keeping several versions of a row) and the lock manager keep things ACID.

The difference that matters when reading plans:

  • PostgreSQL stores a table as an unordered heap, and every index points into the heap by row address (TID).
  • InnoDB stores the table inside the clustered index on the PK, and secondary indexes hold the PK value.
MySQL: server layer, storage engine and the removed query cache

MySQL follows the same idea but separates the server layer (connector → parser → preprocessor → optimizer → executor) from the storage engine (InnoDB and others).

3. The logical order of clauses

Key idea: logically, FROM runs first and SELECT almost last. This isn’t the physical order: the planner may reorder things as long as the result is identical.

flowchart LR
  F["FROM / JOIN"] --> ON["ON"] --> W["WHERE"] --> G["GROUP BY"] --> H["HAVING"] --> S["SELECT"] --> D["DISTINCT"] --> O["ORDER BY"] --> L["LIMIT"]
  • An alias defined in SELECT can’t be used in WHERE, because SELECT hasn’t run yet; it can be used in ORDER BY.
  • WHERE filters rows before grouping, HAVING filters groups after grouping.
  • LIMIT runs last, so ORDER BY ... LIMIT 10 still needs the rows in order before it can cut.

The planner may push predicates down into the scan, reorder joins, or use an index to skip a sort. The execution plan is the physical order.

4. How the planner chooses

Key idea: PostgreSQL has a cost-based optimizer: cost = unit cost × quantity. The quantities (how many rows each step returns) come from statistics, so wrong statistics mean a wrong plan.

Unit costs are abstract units:

Setting Default Meaning
seq_page_cost 1 read one page sequentially
random_page_cost 4 read one page randomly
cpu_tuple_cost 0.01 process one row (plus a few smaller CPU costs)

Statistics are collected by ANALYZE (or autovacuum, the automatic cleanup process) into pg_stats:

  • n_distinct: the number of distinct values.
  • MCV: the most common values and their frequencies.
  • Histogram: the distribution of the remaining values.
  • correlation: how closely the physical row order follows the value order.
Reading correlation in pg_stats, and lowering random_page_cost on SSDs
SELECT attname, n_distinct, correlation FROM pg_stats
WHERE tablename = 'bookings0' AND attname = 'book_date';
-- n_distinct = -1 (every value distinct), correlation ≈ -0.007

A correlation near 0 means rows with neighbouring book_date values are scattered across the disk, so following the index jumps between pages constantly. That’s why, in part 2, a query over a whole year of data picks a Seq Scan.

5. EXPLAIN and its options

Key idea: EXPLAIN only prints the expected plan; add ANALYZE and the statement really runs and reports real numbers; add BUFFERS and you see how many pages came from cache and how many from disk.

EXPLAIN SELECT ...;                                 -- estimates only, does not run the query
EXPLAIN (ANALYZE) SELECT ...;                       -- runs it, adds real time, rows and loops
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;              -- adds pages read from cache (hit) and from OS/disk (read)
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT ...; -- named fields, feed it to a tool such as explain.dalibo.com
  • FORMAT YAML/JSON is friendlier for beginners because every number has a field name (Startup Cost, Plan Rows, Actual Loops…).
  • Since PostgreSQL 18, EXPLAIN ANALYZE includes BUFFERS by default.
  • For plans over millions of rows, TIMING OFF cuts the clock overhead while keeping row counts.

Every plan in this post is real output from my own run in Docker on a simulated airline dataset: 2.1 million bookings, 3.1 million tickets (the setup script is in part 2). Your timings will differ; the ratios between the options are what’s worth remembering.

Prepared statements and generic plans: a query that suddenly slows down with no change

6. Scan types

Key idea: a scan is how rows are fetched from one table. For most of the table a Seq Scan is cheapest, for few rows an Index Scan, for a medium number a Bitmap scan; an Index Only Scan skips the table but depends on VACUUM.

Scan type What it does Good when Cost
Seq Scan / Parallel Seq Scan reads every page in physical order and tests each row; big tables are split across workers you need most of the table, or it’s small reads the whole table even for one row
Index Scan walks down the B-tree, then for each matching entry jumps to the heap for the row few rows, or index order is needed one random read per row
Index Only Scan answers from the index when it holds every column needed covering index on a vacuumed table still reads the heap for pages not all-visible
Bitmap Index Scan + Bitmap Heap Scan marks heap pages that may hold matches in a bitmap, then reads them in physical order, each once a medium number of rows, combining indexes (BitmapAnd/BitmapOr) index order is lost, an ORDER BY needs a sort on top

Measurements on the airline dataset:

  • Seq Scan looking up one ticket with no index: almost 30,000 pages, 3.1 million rows thrown away, 98 ms.
  • Index Scan for the same lookup: 4 pages (three B-tree levels plus one heap page), about 0.5 ms.
  • Bitmap Heap Scan fetching the 5,404 bookings of one day: 4,458 heap pages, 17 ms.

A seq scan isn’t bad per se: it reads sequentially and never bounces between an index and the table. In a plan, tell Index Cond (used to navigate the index) apart from Filter (checked after the row has been fetched).

An Index Only Scan has a hidden condition. PostgreSQL indexes carry no MVCC information, so the executor checks the visibility map (a map of pages whose rows are visible to every transaction, called all-visible). For pages that aren’t all-visible it still reads the heap, counted as Heap Fetches.

Real output of the four scan types, Recheck Cond and lossy bitmaps

Seq Scan: looking up one ticket in a table with no index. 1,033,333 rows discarded × 3 processes = 3.1 million rows.

Gather  (cost=1000.00..46777.93 rows=1 width=46) (actual time=97.307..97.876 rows=0 loops=1)
  Workers Launched: 2
  Buffers: shared hit=2080 read=27552
  ->  Parallel Seq Scan on tickets0  (cost=0.00..45777.83 rows=1 width=46) (actual time=93.640..93.640 rows=0 loops=3)
        Filter: (ticket_no = '0005435703703'::bpchar)
        Rows Removed by Filter: 1033333

Index Scan: four pages instead of 30,000.

Index Scan using tickets0_ticket_no_idx on tickets0  (cost=0.43..8.45 rows=1 width=46) (actual time=0.517..0.518 rows=1 loops=1)
  Index Cond: (ticket_no = '0005434508964'::bpchar)
  Buffers: shared hit=1 read=3

Index Only Scan: update the 5,404 rows of one day and run the same query before and after VACUUM.

-- Right after the UPDATE
Index Only Scan using bookings0_date_ref_amount on bookings0 (actual time=0.016..2.334 rows=881 loops=1)
  Heap Fetches: 1762
  Buffers: shared hit=1820
-- After VACUUM bookings0
Index Only Scan using bookings0_date_ref_amount on bookings0 (actual time=0.015..0.242 rows=881 loops=1)
  Heap Fetches: 0
  Buffers: shared hit=58

Bitmap Index Scan + Bitmap Heap Scan: random I/O becomes nearly sequential.

Bitmap Heap Scan on bookings0  (actual time=0.608..17.179 rows=5404 loops=1)
  Recheck Cond: ((book_date >= '2017-03-01') AND (book_date < '2017-03-02'))
  Heap Blocks: exact=4458
  ->  Bitmap Index Scan on bookings0_book_date_idx  (actual time=0.330..0.330 rows=5404 loops=1)

Recheck Cond is there because when the bitmap outgrows work_mem (the memory for each sort/hash operation) it becomes lossy, remembering pages rather than rows (Heap Blocks: lossy=...), so the condition must be re-checked.

7. Reading an execution plan

Key idea: a plan is a tree; the most deeply indented node runs first and feeds rows to its parent. Read it inside out, and always multiply actual time by loops before deciding which node is the most expensive.

For each node, ask three questions: how does it get its data, how many rows were estimated, how many came back.

EXPLAIN (ANALYZE, BUFFERS)
SELECT b.book_ref, t.ticket_no
FROM bookings b JOIN tickets t ON t.book_ref = b.book_ref
WHERE b.book_date >= '2017-03-01 10:00' AND b.book_date < '2017-03-01 10:10';
Gather  (cost=1000.43..27693.57 rows=53 width=21) (actual time=1.280..54.726 rows=50 loops=1)
  Workers Launched: 2
  Buffers: shared hit=132 read=13394
  ->  Nested Loop  (cost=0.43..26688.27 rows=22 width=21) (actual time=5.045..48.992 rows=17 loops=3)
        ->  Parallel Seq Scan on bookings b  (cost=0.00..26501.00 rows=15 width=7) (actual time=4.967..48.562 rows=12 loops=3)
              Filter: ((book_date >= '2017-03-01 10:00') AND (book_date < '2017-03-01 10:10'))
              Rows Removed by Filter: 699988
              Buffers: shared hit=74 read=13302
        ->  Index Scan using tickets_book_ref_idx on tickets t  (cost=0.43..12.46 rows=2 width=21) (actual time=0.031..0.032 rows=1 loops=37)
              Index Cond: (book_ref = b.book_ref)
              Buffers: shared hit=58 read=92
Execution Time: 54.745 ms
  1. Parallel Seq Scan on bookings: three processes (loops=3), each discarding ~700,000 rows to find ~12, accounting for nearly all of the 13,400 pages. bookings has no index on book_date.
  2. Index Scan on tickets runs 37 times, ~0.03 ms each, thanks to the index on tickets.book_ref.
  3. Nested Loop combines both sides, and Gather collects the workers’ output.

Conclusion: an index on bookings(book_date) is what would help; the tickets side is fine.

A checklist for reading a plan:

  1. Which node is most expensive once you multiply by loops?
  2. How far is the estimated rows from the actual count? More than 10× at a low node usually explains why the nodes above picked the wrong algorithm.
  3. Which condition sits in Filter when it should be in Index Cond?
  4. Is there a Sort/Hash with temp written, or a large Heap Fetches?
  5. Is the second run much faster (read turned into hit)? If so, the real question is whether the hot data fits in RAM.
Reference table: what each field in a plan means
Field Meaning
cost=0.43..8.45 Startup cost (before the first row; a Sort has a high one) .. total cost, including child nodes
estimated rows= / width= Expected rows per execution / average row size in bytes
actual time=a..b ms to the first row .. to the last row, per loop
actual rows=, loops= Average rows per loop / how many times the node ran
Rows Removed by Filter Rows read and then dropped: wasted reads, a candidate for an index
shared hit / read Pages served from shared_buffers / pages read from the OS or disk
temp read/written A Sort or Hash exceeded work_mem and spilled to disk

8. Finding the queries worth optimizing

Key idea: rank by total time, not by the slowest single run. A query that runs 5,000 times a minute at 20 ms deserves more attention than one that runs once a day at 2 seconds.

pg_stat_statements aggregates statistics per normalized statement (constants become $1):

-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements' (restart required)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT left(query, 60) AS query, calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows, shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
Other sources for slow queries: logs, auto_explain, MySQL, Hibernate
  • log_min_duration_statement = 500ms logs slow statements with their parameters.
  • The auto_explain extension logs the plan of slow statements. Valuable, because the plan during an incident may differ from the one you get when re-running later.
  • MySQL has the slow query log and sys.statements_with_runtimes_in_95th_percentile.
  • On the application side: APM/tracing, hibernate.generate_statistics, hibernate.session.events.log.LOG_QUERIES_SLOWER_THAN_MS, or datasource-proxy to count queries per request.

9. Case study: an API that slowed down after a data import

Key idea: when a query suddenly slows down after a data load with no deploy, suspect stale statistics first. ANALYZE to stop the bleeding, then fix the data so the problem doesn’t come back.

Context (an illustrative scenario based on a common kind of incident). The “customer booking history” API had a p95 of about 40 ms (95% of requests were faster than that). After importing 3 million bookings from a legacy system, p95 went to 2.5 seconds; there had been no deploy. Constraints: no downtime, fix it the same day.

Investigation.

  • pg_stat_statements put ... WHERE t.passenger_id = $1 ORDER BY b.book_date DESC LIMIT 20 on top by total time, with the mean up from 3 ms to 1,800 ms.
  • The plan captured by auto_explain: the tickets node estimated rows=1 but returned rows=48000. The legacy system wrote '0000 000000' for every passenger without an ID document.
  • Believing there was one row, the planner chose a nested loop followed by a sort; in reality that was 48,000 iterations.
  • pg_stat_user_tables.last_autoanalyze for tickets predated the import.

Options and decision. Forcing a plan (disabling nested loops, hints) works instantly but affects every other query and hides a data problem. A new index might not even be chosen while the estimate is still wrong. The team:

  1. Ran ANALYZE tickets right away (a few seconds, doesn’t block writes) to stop the bleeding.
  2. Shipped a migration turning the placeholder into NULL.
  3. Added ANALYZE to the end of the import job.
  4. Added an alert when the mean_exec_time of a top query triples compared with the previous week.

Exercises

Exercise 1: Read the nested loop plan

Task: using the plan in section 7, which node is the most expensive, what is the total time of Index Scan on tickets, and how would the plan most likely change after adding an index on bookings(book_date)?

Solution to Exercise 1
  • The most expensive node is Parallel Seq Scan on bookings (~48.5 ms per process, 13,302 pages).
  • Index Scan on tickets costs about 0.032 × 37 ≈ 1.2 ms.
  • With an index on book_date, the bookings side becomes an Index Scan over a few pages for ~37 rows, Gather disappears because parallelism isn’t needed, and the total drops below 1 ms.
  • The tickets side stays an Index Scan inside the nested loop.

Exercise 2: Optimize one query in your own project

Task (the course homework, paraphrased): pick a real query from your project, optimize it, present the solution and explain why it’s faster.

Solution to Exercise 2: a template
  1. The SQL, its real parameters and how often it’s called.
  2. The plan before the change, marking the most expensive node and any misestimate.
  3. A hypothesis: missing index, non-sargable predicate (a condition that can’t use an index), bad estimate, sort spilling to disk.
  4. One change at a time.
  5. The plan after the change with the same parameters, comparing Execution Time and Buffers.
  6. The cost: index size, write speed, whether other queries changed plans.

Remember

  • Can you list the stages a query passes through, and what the planner relies on to choose a plan?
  • Can you explain why a SELECT alias can’t be used in WHERE, and why the physical plan may still differ from the logical order?
  • Do you know when the planner picks Seq Scan, Index Scan, Index Only Scan or Bitmap Heap Scan, and why high Heap Fetches points to missing VACUUM?
  • Can you read a plan: find the most expensive node after multiplying by loops, and compare estimated with actual rows?
  • Do you know the first reflex when a query slows down after an import, and why not to force a plan?

Interview questions

Junior

What’s the difference between EXPLAIN and EXPLAIN ANALYZE?

EXPLAIN only prints the expected plan and estimated costs without running the statement; EXPLAIN ANALYZE really runs it.

EXPLAIN ANALYZE adds real time, row counts and loop counts, so writes must be wrapped in BEGIN ... ROLLBACK. Add BUFFERS to see pages read from cache versus disk.

Why can’t you use a SELECT alias in WHERE?

Because in the logical order WHERE is evaluated before SELECT, so the alias doesn’t exist yet.

The logical order is FROM/JOIN → ON → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. ORDER BY comes after SELECT, so it can use the alias. The physical plan may differ from this order as long as the result is the same.

Is a Seq Scan always a bad sign?

No. When you need most of the table, or the table is small, reading sequentially beats walking an index and jumping randomly into the table.

It’s worrying when a Seq Scan on a large table returns very few rows compared with Rows Removed by Filter.

Mid

How do Index Scan, Index Only Scan and Bitmap Heap Scan differ?

They differ in how they touch the table: row by row, not at all, or grouped by page.

  • An Index Scan walks the index and fetches each row from the heap in index order; good for few rows or when order is needed.
  • An Index Only Scan answers from the index and reads the heap only for pages not marked all-visible.
  • A bitmap scan collects the pages to read into a bitmap and reads them in physical order; good for a medium number of rows and able to combine indexes, but it loses ordering.
What do cost=0.43..8.45 and actual time=0.5..0.6 rows=1 loops=37 mean?

Cost is an estimate in abstract units; actual time and rows are real measurements for one loop.

Cost: 0.43 before the first row, 8.45 for the whole node including its children. Actual time is in ms per loop; the node ran 37 times, so it took about 22 ms in total and returned about 37 rows.

Why is a big gap between estimated and actual rows the most important signal in a plan?

Because every planner decision (scan type, join algorithm and order, parallelism) is based on row estimates.

An error at a low node spreads through the whole tree: expecting one row, the planner picks a nested loop, and 50,000 actual rows turn into 50,000 lookups. Fix it with ANALYZE, a higher statistics target, or CREATE STATISTICS.

Senior

A query is fast in psql but slow from the Java application. What could be going on?

Usually a different plan (a generic plan, a parameter type cast), or the time isn’t spent in the database at all.

  • The prepared statement switched to a generic plan that’s bad for common values (check with EXPLAIN EXECUTE, try plan_cache_mode).
  • The parameter type differs from the column type, forcing a cast that disables the index.
  • The app pulls the whole result set into memory because of the fetch size.
  • The time is actually spent waiting for locks, waiting for a pooled connection, on the network or in ORM mapping.

Use auto_explain and tracing to tell them apart.

Would you turn off enable_seqscan or lower random_page_cost to make the planner use your index?

enable_seqscan = off: never in production. Lowering random_page_cost: yes on SSDs, and re-measure.

  • enable_seqscan = off is only for experiments, to see what the alternative plan costs.
  • Lowering random_page_cost to 1.1–1.5 is reasonable on SSDs with data in RAM; set it at database level and re-measure the main queries.
  • When the planner gets a single query wrong, the cause is usually a bad estimate or an index that doesn’t match the query; fix that first.

Useful phrases for interviews

  • “Before adding anything, I’d run EXPLAIN with ANALYZE and BUFFERS on production-sized data.”
  • “I read the plan inside out, starting from the deepest node, and I multiply the actual time by the number of loops.”
  • “The estimate says one row but the actual count is fifty thousand, so the statistics are the first thing I’d look at.”
  • “A sequential scan isn’t necessarily bad; for most of the table it’s the cheapest way to read it.”
  • “It says index-only scan, but the heap fetches are high, which tells me the visibility map is out of date.”
  • “I’d rank queries by total execution time in pg_stat_statements, not by the slowest single call.”
  • “Forcing a plan would stop the bleeding, but I’d rather fix the estimate or the data so it doesn’t come back.”
  • “Remember that EXPLAIN ANALYZE actually runs the statement, so for a DELETE I wrap it in a transaction and roll back.”

Notes based on the Advanced Backend course (Ronin Engineer), rewritten in my own words.