Query execution plans

A query execution plan is the step-by-step program a database builds to answer one SQL statement: which tables to read, through which indexes, in what order to join them and with which algorithm. EXPLAIN shows you that plan with the optimiser’s estimates; EXPLAIN ANALYZE runs it and shows what really happened. Reading the two side by side is how you find out why a query is slow instead of guessing.

Picture it first: the maps app’s route

Before your cab moves, the maps app considers several routes: the highway, the ring road, the shortcut through the market. It can’t drive all three, so it estimates each from historical traffic data and picks the lowest predicted time. Most days the prediction is close. On the evening of a cricket match, the historical data says the ring road near the stadium is clear, the app sends you that way, and you sit in traffic for 40 minutes. The algorithm wasn’t wrong; its input was.

Map it back:

• Routes are candidate plans: which index, which join order, which join algorithm.
• Historical traffic data is table statistics: row counts, common values, histograms.
• The predicted time is the plan’s estimated cost; the actual drive is what EXPLAIN ANALYZE measures.
• Match-day traffic is stale statistics, or two conditions that are related (stadium road, match day) that the app treated as independent.

When a query is slow, the first question is whether the database took a bad route because its picture of the data was wrong.

From SQL text to rows

PostgreSQL handles every statement in four stages (the path of a query):

1. Parse. Check the syntax and build a parse tree. A typo fails here.
2. Analyse and rewrite. Resolve table and column names against the catalog and expand views into their underlying queries. A view has no plan of its own; it is inlined here.
3. Plan. Generate candidate ways to run the query, estimate the cost of each, keep the cheapest.
4. Execute. Walk the chosen plan tree and stream rows back to the client.

MySQL has the same shape: parser, optimiser, executor, with the storage engine underneath.

Why does the planner need costs at all? Because SQL says what you want, not how to get it, and the number of “hows” explodes. Each table can be read by a sequential scan or any usable index. A three-table join has 3! = 6 join orders, and each of its two joins can use one of three algorithms, so before counting scan choices there are already 6 × 3 × 3 = 54 plans. Six tables have 720 join orders. PostgreSQL searches this space nearly exhaustively for queries with fewer than geqo_threshold (12) tables, and switches to a genetic heuristic above that.

It can’t run the candidates to see which is fastest, so it predicts. Each one gets a cost in arbitrary units: reading a page sequentially costs 1.0 (seq_page_cost), a random page 4.0 (random_page_cost), processing one row 0.01 (cpu_tuple_cost). The inputs that matter most are row estimates, how many rows each step will produce, and those come from statistics. With good estimates, the cheapest plan on paper is usually the fastest in reality.

EXPLAIN, ANALYZE and BUFFERS

EXPLAIN runs the first three stages and prints the plan with estimates only. It is instant and safe on any statement.

EXPLAIN ANALYZE also executes the statement and prints, next to every estimate, what actually happened: real time, real rows and how many times each node ran. Because it really runs, an UPDATE really updates and a 40-minute query really takes 40 minutes. Wrap writes in a transaction you roll back:

BEGIN;
EXPLAIN ANALYZE
UPDATE orders SET status = 'cancelled'
WHERE status = 'pending' AND created_at < now() - interval '1 hour';
ROLLBACK;   -- the rows stay locked until here, so keep it short on a busy primary

BUFFERS adds page counts per node: shared hit (found in PostgreSQL’s cache), read (fetched from the operating system or disk), dirtied and written, and temp blocks for sorts and hashes that spilled to disk. Buffers are often more telling than time: time swings with cache warmth and load, while page counts measure the work itself. Since PostgreSQL 18, EXPLAIN ANALYZE includes buffers automatically; on 17 and earlier, write EXPLAIN (ANALYZE, BUFFERS). Version 18 also prints row counts with two decimals (rows=10.00), because they are per-loop averages, and an Index Searches line under index scans.

Other options worth knowing: VERBOSE (output columns), SETTINGS (non-default planner settings), FORMAT JSON (for plan visualisers), TIMING OFF (cheaper when you only need row counts), SERIALIZE (the cost of converting results to text, which plain ANALYZE ignores) and GENERIC_PLAN (PostgreSQL 16+, plans a query that still has $1 placeholders).

Reading the output

A plan is a tree printed as indented lines. Each -> starts a node, and the lines indented beneath it are its children. Five rules cover most plans:

1. Execution starts at the deepest indent. Leaves are scans that read tables; parents consume their children’s rows. Read bottom-up, inside-out.
2. cost=startup..total is in planner units, not milliseconds. Startup is the cost before the first row can come out (a Sort must read everything first); total assumes the node runs to completion. A parent’s cost includes its children’s.
3. rows is the estimate of rows the node emits after its own filters, not the rows it examined. width is the average row size in bytes.
4. actual time=first..last rows=R loops=L: times are milliseconds, and both time and rows are averages per loop. A node showing rows=1.00 loops=48000 produced 48,000 rows in total, so multiply by loops before comparing.
5. Detail lines explain the work: Index Cond (used to search the index), Filter with Rows Removed by Filter (rows read and thrown away), Hash Cond, Sort Method, Buffers.

The leaves are scan nodes, and one line each is enough here:

Why the planner picks one over another (selectivity, random versus sequential I/O, correlation) is the subject of index scan vs full table scan.

A worked example: the city revenue report

A food-delivery platform keeps the last 90 days of orders in PostgreSQL 18, about 200,000 orders a day; older orders move to the warehouse.

CREATE TABLE restaurants (
  id      bigint PRIMARY KEY,
  name    text NOT NULL,
  city_id int  NOT NULL,
  zone_id int  NOT NULL
);                                     -- 200,000 rows, 2,300 pages

CREATE TABLE orders (
  id            bigint PRIMARY KEY,
  customer_id   bigint      NOT NULL,
  restaurant_id bigint      NOT NULL REFERENCES restaurants,
  city_id       int         NOT NULL,
  zone_id       int         NOT NULL,
  status        text        NOT NULL,
  total_paise   int         NOT NULL,
  created_at    timestamptz NOT NULL
);                                     -- 18 million rows
CREATE INDEX ON orders (created_at);   -- orders_created_at_idx
CREATE INDEX ON orders (zone_id);      -- orders_zone_id_idx

CREATE TABLE order_items (
  order_id bigint NOT NULL REFERENCES orders,
  dish_id  bigint NOT NULL,
  qty      int    NOT NULL,
  PRIMARY KEY (order_id, dish_id)
);

Ops wants the ten Bengaluru restaurants with the most revenue in the last 24 hours:

SET max_parallel_workers_per_gather = 0;   -- only to keep the plan short

EXPLAIN ANALYZE
SELECT r.id, r.name, count(*) AS order_count, sum(o.total_paise) AS revenue_paise
FROM orders o
JOIN restaurants r ON r.id = o.restaurant_id
WHERE r.city_id = 7                         -- Bengaluru
  AND o.created_at >= now() - interval '1 day'
GROUP BY r.id
ORDER BY revenue_paise DESC
LIMIT 10;

Limit  (cost=15288.42..15288.44 rows=10 width=54) (actual time=90.863..90.867 rows=10.00 loops=1)
  Buffers: shared hit=4480 read=1034
  ->  Sort  (cost=15288.42..15320.52 rows=12840 width=54) (actual time=90.861..90.864 rows=10.00 loops=1)
        Sort Key: (sum(o.total_paise)) DESC
        Sort Method: top-N heapsort  Memory: 26kB
        Buffers: shared hit=4480 read=1034
        ->  HashAggregate  (cost=14882.06..15010.46 rows=12840 width=54) (actual time=86.902..89.117 rows=9214.00 loops=1)
              Group Key: r.id
              Batches: 1  Memory Usage: 2577kB
              Buffers: shared hit=4480 read=1034
              ->  Hash Join  (cost=5097.57..14721.41 rows=21420 width=42) (actual time=17.264..78.390 rows=25931.00 loops=1)
                    Hash Cond: (o.restaurant_id = r.id)
                    Buffers: shared hit=4480 read=1034
                    ->  Index Scan using orders_created_at_idx on orders o  (cost=0.57..6912.40 rows=178500 width=12) (actual time=0.019..48.517 rows=196412.00 loops=1)
                          Index Cond: (created_at >= (now() - '1 day'::interval))
                          Index Searches: 1
                          Buffers: shared hit=2180 read=1034
                    ->  Hash  (cost=4800.00..4800.00 rows=23800 width=30) (actual time=17.210..17.211 rows=24000.00 loops=1)
                          Buckets: 32768  Batches: 1  Memory Usage: 1838kB
                          Buffers: shared hit=2300
                          ->  Seq Scan on restaurants r  (cost=0.00..4800.00 rows=23800 width=30) (actual time=0.011..14.902 rows=24000.00 loops=1)
                                Filter: (city_id = 7)
                                Rows Removed by Filter: 176000
                                Buffers: shared hit=2300
Planning:
  Buffers: shared hit=24
Planning Time: 0.412 ms
Execution Time: 91.304 ms

Read it from the deepest line up (the numbers are illustrative but shaped exactly as PostgreSQL 18 prints them):

1. Seq Scan on restaurants reads all 2,300 pages (all cache hits) and keeps the 24,000 Bengaluru rows, discarding 176,000. Throwing away 88% of a table sounds bad, but this one is small and the scan takes 15 ms. Rows Removed by Filter only matters on big tables. The cost checks out against the constants: 2,300 pages × 1.0 + 200,000 rows × 0.01 + 200,000 filter checks × 0.0025 = 4,800.
2. Hash loads those rows into an in-memory hash table keyed on id: one batch, 1,838 kB, comfortably under the default limit of work_mem × hash_mem_multiplier = 4 MB × 2 = 8 MB.
3. Index Scan on orders finds the last day’s orders through orders_created_at_idx: estimated 178,500, actual 196,412, within 10%. It read 1,034 pages from outside PostgreSQL’s cache, 1,034 × 8 kB ≈ 8 MB. Orders are inserted in time order, so one day’s rows sit on neighbouring pages, which is why an index scan over 196,000 rows is cheap here.
4. Hash Join streams each order through the hash table: 196,412 probes yield 25,931 Bengaluru orders. Its startup time (17.3 ms) is the hash build; it finishes at 78.4 ms.
5. HashAggregate groups by r.id: 9,214 restaurants had orders. The estimate of 12,840 is 1.4× high, which is harmless.
6. Sort uses a top-N heapsort, keeping only the best 10 rows in 26 kB rather than sorting all 9,214.
7. Limit returns 10 rows. Execution takes 91.3 ms; planning 0.4 ms.

This is a healthy plan. Every estimate is within 1.5× of reality, nothing spilled to disk, and over half the time (48.5 of 91 ms) goes to reading the 196,412 orders, which is the real work. To make it much faster you would change the work itself, for example by keeping hourly revenue per restaurant in a rollup table.

Join algorithms

Every join node combines two inputs: the outer (first child) and the inner (second child). PostgreSQL has three join algorithms, and choosing among them comes down to how many rows each side has, whether there is an index on the join key, and whether the inputs are already sorted.

Nested loop

For each outer row, run the inner side once. With an index on the inner join key, each run is a probe of three or four pages, so 50 outer rows cost about 50 × 4 = 200 page visits, which nothing beats for small inputs. The first joined row comes out almost immediately, which is why nested loops pair well with LIMIT. Without an index, every loop rescans the inner side: 10,000 × 10,000 = 100 million row comparisons. PostgreSQL may add a Materialize node to keep the inner rows in memory, or a Memoize node (version 14+) to cache results for repeated keys, but neither rescues a nested loop whose outer side is far bigger than planned. It is also the only algorithm that handles non-equality joins such as a.starts_at < b.ends_at.

Hash join

Read one input (the build side, under the Hash node), usually the smaller, and build a hash table on the join key. Then stream the other (the probe side) and look each row up. The work is one pass over each input whatever their sizes, which makes it the workhorse for joining large unsorted sets on equality. The price is memory and startup: nothing comes out until the hash table is built, and the table may use work_mem × hash_mem_multiplier, 8 MB by default. If the build side is bigger, PostgreSQL splits both inputs into batches by hash value, writes all but the first to temporary files and joins batch by batch. Batches: 16 in the plan means fifteen-sixteenths of both inputs made a round trip through temporary files.

Merge join

Sort both inputs on the join key, then walk them in step, like merging two sorted lists, emitting matches as the keys line up. If both inputs already come out of indexes in key order there is no sort at all, and the join streams with almost no memory. Joining a range of orders to order_items on order_id, with both read through their primary keys, is the textbook case. If the planner has to sort two big inputs first, the sorts dominate and may spill to disk. The output is sorted on the join key, which can also satisfy an ORDER BY for free.

Sorts, aggregates and memory

Two more node families appear in almost every report query.

Sort. Sort Method tells you where it ran. quicksort Memory: 74kB fit in work_mem. top-N heapsort kept only the first N rows for a LIMIT. external merge Disk: 51200kB means the data didn’t fit: it was written to temporary files in sorted runs and merged back, and the temp read/written buffer counts show how much I/O that cost. Incremental Sort appears when the input is already sorted on a prefix of the sort keys.

Aggregate. HashAggregate keeps a hash table of groups: fast, unordered, memory-hungry, and since PostgreSQL 13 able to spill to disk in batches instead of growing without limit. GroupAggregate needs input sorted by the group key and holds one group at a time: little memory and sorted output, but it may need a Sort beneath it. Many groups and little memory push the planner towards sort plus GroupAggregate.

The memory knob. work_mem (default 4 MB) is a limit per sort or hash operation, not per query or connection. A query with three hashes and two sorts can use five times work_mem, and hundreds of connections may do so at once. So raise it for the session that runs the heavy report (SET work_mem = '256MB'), not globally: 200 connections × 5 operations × 256 MB is about 250 GB in the worst case.

Estimates vs actuals: the first thing to check

Every planner decision rests on row estimates. So the most useful habit is to scan an EXPLAIN ANALYZE for nodes where estimated rows and actual rows disagree, find the lowest such node in the tree, and ask why. Within 2–3× is normal. Ten times or more, especially an underestimate beneath a join, usually explains the bad choice above it. Errors also compound: a join’s estimate is built from its inputs’ estimates, so two 10× misses under one join can become 100×.

Here is a common shape. A zone dashboard filters orders by city_id = 31 AND zone_id = 412, where zone 412 is in Mysuru. The statistics say Mysuru is 1% of orders and zone 412 is 0.27% (48,000 of 18 million). The planner assumes the columns are independent and multiplies: 18,000,000 × 0.01 × 0.00267 ≈ 480 rows. But every zone-412 order is a Mysuru order, so the true answer is 48,000, a 100× underestimate.

Nested Loop  (cost=725.98..103468.12 rows=480 width=38) (actual time=4.102..262.871 rows=48000.00 loops=1)
  Buffers: shared hit=231192
  ->  Bitmap Heap Scan on orders o  (cost=724.56..99413.83 rows=480 width=16) (actual time=4.080..41.716 rows=48000.00 loops=1)
        Recheck Cond: (zone_id = 412)
        Filter: (city_id = 31)
        Heap Blocks: exact=39150
        Buffers: shared hit=39192
        ->  Bitmap Index Scan on orders_zone_id_idx  (cost=0.00..712.56 rows=48000 width=0) (actual time=3.402..3.402 rows=48000.00 loops=1)
              Index Cond: (zone_id = 412)
              Index Searches: 1
              Buffers: shared hit=42
  ->  Index Scan using restaurants_pkey on restaurants r  (cost=0.42..8.44 rows=1 width=30) (actual time=0.004..0.004 rows=1.00 loops=48000)
        Index Cond: (id = o.restaurant_id)
        Index Searches: 48000
        Buffers: shared hit=192000

Read it bottom-up. The index estimate for zone_id = 412 is right: 48,000. The Bitmap Heap Scan then applies city_id = 31 and its estimate collapses to 480, yet there is no Rows Removed by Filter line, because the filter removed nothing. Planning for 480 outer rows, a nested loop doing 480 primary-key probes looked slightly cheaper than reading all 200,000 restaurants into a hash table. It actually did 48,000 probes and touched 192,000 buffers on the inner side alone.

Fix the estimate, not the join:

CREATE STATISTICS orders_zone_city (dependencies) ON zone_id, city_id FROM orders;
ANALYZE orders;

Now the planner knows zone determines city, the scan estimate becomes 47,600, and it chooses a hash join that reads restaurants once.

Where the estimates come from

ANALYZE, run by you or by autovacuum in the background, reads a random sample of each table and stores per-column statistics; pg_class keeps each table’s row and page counts. Read them through the pg_stats view:

SELECT attname, null_frac, n_distinct, most_common_vals, most_common_freqs, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname IN ('status', 'zone_id', 'created_at');

• null_frac: the share of NULLs.
• n_distinct: distinct values. A negative number is a fraction of the row count (−1 means unique), so it scales as the table grows.
• most_common_vals and most_common_freqs (the MCV list): the most frequent values with their frequencies, so status = 'delivered' gets its sampled frequency, say 0.91, rather than a guess.
• histogram_bounds: values that split the remaining, non-MCV values into buckets of equal population. Range predicates are estimated by counting buckets.
• correlation: how closely the physical row order follows the column’s sorted order, from −1 to +1. Near ±1, like created_at on an append-only table, index range scans become cheap, as in the worked example.

How much is collected is set by default_statistics_target, 100 by default: up to 100 MCVs and 100 histogram buckets per column, from a sample of 300 × target = 30,000 rows, whether the table holds 30 thousand rows or 3 billion. For a skewed column, raise it per column (ALTER TABLE orders ALTER COLUMN zone_id SET STATISTICS 1000) and re-analyse.

Staleness. Autovacuum re-analyses a table after 50 + 10% of its rows have changed (autovacuum_analyze_threshold plus autovacuum_analyze_scale_factor). For the 18-million-row orders table that is about 1.8 million changed rows between refreshes. Run ANALYZE yourself after bulk loads, large deletes and migrations, and lower the scale factor on big, fast-changing tables.

Correlated columns. Per-column statistics can’t see that zone determines city. CREATE STATISTICS collects multi-column statistics for the columns you name, in three kinds:

• dependencies: one column largely determines another, like zone → city. Used for equality and IN conditions with constants.
• ndistinct: distinct counts for column combinations, which fixes group estimates for GROUP BY city_id, zone_id.
• mcv: the most common combinations of values, for correlations that aren’t strict dependencies, such as cuisine and city.

Create them only where a misestimate has actually hurt: each object adds work to every ANALYZE and to planning.

MySQL: EXPLAIN and EXPLAIN ANALYZE

MySQL’s classic EXPLAIN prints one row per table in join order rather than a tree. For the same city report (some columns trimmed; numbers illustrative):

+----+-------+--------+--------------------+---------+----------------------+--------+----------+--------------------------------------------------------+
| id | table | type   | key                | key_len | ref                  | rows   | filtered | Extra                                                  |
+----+-------+--------+--------------------+---------+----------------------+--------+----------+--------------------------------------------------------+
|  1 | o     | range  | idx_orders_created | 5       | NULL                 | 191820 |   100.00 | Using index condition; Using temporary; Using filesort |
|  1 | r     | eq_ref | PRIMARY            | 8       | food.o.restaurant_id |      1 |    12.00 | Using where                                            |
+----+-------+--------+--------------------+---------+----------------------+--------+----------+--------------------------------------------------------+

• type, the access type, from best to worst: system, const (at most one row, via the primary key or a unique index, read once), eq_ref (one row per row of the previous table, via a unique key), ref (several rows via a non-unique index), range (an index range), index (the whole index scanned), ALL (a full table scan). ALL on a big table that isn’t the first in the join order is the classic red flag.
• key is the index actually chosen; NULL means none. possible_keys lists the candidates.
• rows estimates rows examined, and rows × filtered estimates rows passed on to the next table: 191,820 orders, then for each one, 1 restaurant × 12% passing the Bengaluru check.
• Extra: Using index (covering index, no row lookup), Using where (a condition checked after reading the row), Using index condition (a condition pushed down into the index), Using filesort (an explicit sort) and Using temporary (a temporary table, here for grouping and then ordering by an aggregate).

EXPLAIN FORMAT=TREE (8.0.16+) prints a tree much like PostgreSQL’s, and EXPLAIN ANALYZE (8.0.18+) runs the query and adds actual time, rows and loops per iterator:

-> Limit: 10 row(s)  (actual time=402.6..402.6 rows=10 loops=1)
    -> Sort: revenue_paise DESC, limit input to 10 row(s) per chunk  (actual time=402.6..402.6 rows=10 loops=1)
        -> Table scan on <temporary>  (actual time=395.1..398.9 rows=9214 loops=1)
            -> Aggregate using temporary table  (actual time=395.1..395.1 rows=9214 loops=1)
                -> Nested loop inner join  (cost=153804 rows=23018) (actual time=0.09..352.4 rows=25931 loops=1)
                    -> Index range scan on o using idx_orders_created over ('2026-10-26 13:22:22' <= created_at), with index condition: (o.created_at >= <cache>((now() - interval 1 day)))  (cost=86319 rows=191820) (actual time=0.06..201.3 rows=196412 loops=1)
                    -> Filter: (r.city_id = 7)  (cost=0.25 rows=0.12) (actual time=0.001..0.001 rows=0.13 loops=196412)
                        -> Single-row index lookup on r using PRIMARY (id=o.restaurant_id)  (cost=0.25 rows=1) (actual time=0.001..0.001 rows=1 loops=196412)

MySQL took a different route: a nested loop with one primary-key lookup per order (loops=196412). In MySQL 8.0, hash joins are used only when no index can serve an equality join, and there is no merge join. The same reading rules apply: deepest first, multiply per-loop rows by loops, compare estimates with actuals. ANALYZE TABLE refreshes InnoDB’s sampled index statistics, and MySQL 8.0 can also keep histograms on columns (ANALYZE TABLE orders UPDATE HISTOGRAM ON city_id).

Plan smells and their fixes

The prepared-statement smell catches many teams. Drivers and ORMs prepare statements, and PostgreSQL plans the first five executions with the real parameter values (custom plans). It then builds a generic plan that ignores the values and keeps it if its estimated cost is not much worse (PREPARE). That suits uniform queries and hurts skewed ones: WHERE status = $1 wants an index for 'pending' (0.02% of rows) and a sequential scan for 'delivered' (91%), but a generic plan has one shape for both. Setting plan_cache_mode = force_custom_plan for that role or session makes PostgreSQL replan with real values every time, trading planning time for the right plan.

Finding the queries worth explaining

Don’t explain queries at random. Rank them first:

• pg_stat_statements (added to shared_preload_libraries, which needs a restart) groups statements by normalised text, with constants replaced by $1, and tracks calls, total_exec_time, mean_exec_time, rows and buffers. Sort by total time: a 4 ms query called 2,000 times a second costs 8 seconds of database time every second, far more than a 20-second nightly report. Indexes in practice has a ready-made query.
• auto_explain logs the plan of any statement slower than auto_explain.log_min_duration, so you see the plan that ran at 3 a.m. with real parameters. With log_analyze on it records actual rows too, but every statement then pays for per-node timing, so turn log_timing off or sample with auto_explain.sample_rate.
• MySQL: the slow query log (off by default; long_query_time defaults to 10 seconds) summarised with mysqldumpslow, and performance_schema.events_statements_summary_by_digest, which groups normalised statements the way pg_stat_statements does.

Many slow pages are not one slow query but hundreds of fast ones: that is the N+1 query problem, and pg_stat_statements shows it as a cheap statement with an enormous calls count.

Cheat sheet

How to say it in the interview

“When a query is slow I don’t guess. I find it in pg_stat_statements by total time, then run EXPLAIN ANALYZE with buffers on production-shaped data, inside a rolled-back transaction if it writes. I read the plan bottom-up, and the first thing I check is where estimated and actual rows diverge, because the planner picks scans and join algorithms from those estimates. If two correlated filters make it expect 480 rows instead of 48,000, it picks a nested loop that does 48,000 index probes where a hash join would read the small table once. So I fix the estimate with ANALYZE, a higher statistics target or extended statistics, or add the index the plan is missing, then verify with the same EXPLAIN and the pg_stat_statements mean.”

What’s the difference between EXPLAIN and EXPLAIN ANALYZE? EXPLAIN only plans and prints estimates. EXPLAIN ANALYZE executes the statement and adds actual time, rows and loops per node, plus buffers by default since PostgreSQL 18. Because it executes, writes go inside BEGIN … ROLLBACK, and it takes as long as the query.

When does a hash join beat a nested loop? When the outer input is large. A nested loop costs roughly outer rows × one index probe; a hash join costs one pass over each side. For 50 outer rows, 50 probes win; for 50,000, hashing the other side once wins, as long as the hash table fits in work_mem × hash_mem_multiplier.

The planner isn’t using my index. Why? Usually it estimates that many rows match, so a sequential or bitmap scan is cheaper; or a function or cast hides the column; or the statistics are stale. Compare estimated and actual rows on that scan before forcing anything.

Why is a query fast in psql but slow from the application? Different parameter values, or a generic plan. Applications use prepared statements, and after five executions PostgreSQL may switch to a generic plan that ignores the values, which hurts skewed columns. Compare with EXPLAIN (GENERIC_PLAN) and consider plan_cache_mode = force_custom_plan for that workload.

How do you find which queries to optimise in production? pg_stat_statements ranked by total execution time (calls × mean), and auto_explain for the plans of slow statements with real parameters. In MySQL, the slow query log and the statement-digest tables in performance_schema.

Where to go next

• Index scan vs full table scan: why the planner picks each scan node, and when ignoring your index is right.
• Database indexes: the B+tree structures behind Index Scan and Index Only Scan.
• Composite index design: turning a big Rows Removed by Filter into an index condition.
• Indexes in practice: real before-and-after plans from production index fixes.
• The N+1 query problem: when every plan is fine but there are 500 of them per page.
• PostgreSQL: the engine whose plans this page reads.
• Busy database: when the database is doing work that belongs somewhere else.
• Vacuum and bloat: why buffer counts grow when row counts don’t.

References and further reading

1. PostgreSQL 18 documentation: Using EXPLAINpostgresql.orgPlan-tree structure, cost=startup..total, rows and width, actual time per loop × loops, Rows Removed by Filter, Index Searches, hash and sort details, and the BEGIN … ROLLBACK pattern for writes.
2. PostgreSQL 18 documentation: EXPLAINpostgresql.orgEvery option (ANALYZE, BUFFERS, TIMING, SERIALIZE, GENERIC_PLAN, FORMAT) and what shared, local and temp buffer counts mean.
3. PostgreSQL 18 release notespostgresql.orgBUFFERS included automatically in EXPLAIN ANALYZE, fractional row counts, and per-node index search counts.
4. PostgreSQL 18 documentation: Statistics used by the plannerpostgresql.orgreltuples and relpages, the statistics target, and the three kinds of extended statistics (dependencies, ndistinct, mcv).
5. PostgreSQL 18 documentation: pg_statspostgresql.orgDefinitions of null_frac, n_distinct, most-common values and frequencies, histogram_bounds and correlation.
6. PostgreSQL 18 documentation: Query planning settingspostgresql.orgCost constants (seq_page_cost 1.0, random_page_cost 4.0, cpu_tuple_cost 0.01), default_statistics_target 100, geqo_threshold 12 and plan_cache_mode.
7. PostgreSQL 18 documentation: pg_stat_statementspostgresql.orgStatements grouped by normalised text, calls, total and mean execution time, and the shared_preload_libraries requirement.
8. PostgreSQL 18 documentation: auto_explainpostgresql.orgLogging plans of slow statements with log_min_duration, and the overhead warning for log_analyze and log_timing.
9. MySQL 8.0 Reference Manual: EXPLAIN output formatdev.mysql.comThe type column from system and const down to ALL, rows × filtered, and Extra values such as Using index, Using filesort and Using temporary.
10. MySQL 8.0 Reference Manual: EXPLAIN statementdev.mysql.comFORMAT=TREE (8.0.16) and EXPLAIN ANALYZE (8.0.18), with estimated cost and actual time, rows and loops per iterator.