Read a query plan

What you will be able to do

Find out why a query is slow before you change anything. Ask PostgreSQL how it runs the query, read its answer step by step, and check whether its guesses were right.

Before you startWhat to know and to have readyAbout 50 minutes

What you need to know

• WHERE, ORDER BY, LIMIT and joins, from Select and filter and Join tables in the SQL course.
• The ticket list endpoint and its 41 queries per page, from Safe queries.
• scripts.bench, the median and the p95, from Connection management.

What you need on your computer

• The project at the end of Module 3: python -m pytest prints 79 passed. If your project is broken, copy the snapshot end-of-m03, as its README.md says.
• PostgreSQL running, with the large data loaded. If you are not sure, run python -m scripts.load --size large --reset. It takes a few seconds on a quiet computer and up to a minute on a busy one. It takes about 15 seconds more if it must make the data again.

This lesson adds one file, scripts/explain.py. All times in this module come from one computer with nothing else busy: an Apple M4 with 16 GB, and PostgreSQL 18.6 in Docker. Your times will differ; the shape of the results stays the same. Tested on macOS (Apple silicon). The Windows commands are not tested.

Two ways to find a recipe

You want the recipe for lemon cake in a thick cookbook. You can turn every page from the first to the last and look at each title. Or you can open the index at the back, find “lemon cake, page 212”, and turn straight to it.

Both ways find the recipe: the first reads 400 pages, the second reads two. But for “every recipe with eggs”, page by page can be faster, because most recipes have eggs. A database makes the same choice for every query, and it can tell you which way it chose.

The problem: a slow page, and no idea why

At the end of Module 3, the ticket list works, but it is slow. On the large data (300,000 tickets), scripts.bench measured a median of 375 ms per page, with 41 queries per page. That was 5 passes of 200 requests, 4 at a time. Amira wants it fast. Tomás has an answer ready: “Add an index on every column.”

Mei stops him. “Which query is slow? What does PostgreSQL do with it?” Nobody knows. An index on every column is a guess, and Index deliberately shows its cost. First, look.

The idea: the query plan

PostgreSQL does not run your SQL text directly. First, a part called the planner thinks of several ways to get the rows. It can read the whole table, use an index, or join the tables in another order. It estimates the cost of each way, then picks the cheapest. The way it picks is the query plan: a list of steps, where each step hands its rows to the next one.

EXPLAIN before a query prints the plan without running the query. You see the steps and the planner’s guesses. EXPLAIN ANALYZE runs the query for real and prints the plan again, now with what really happened: the rows each step found and its time. Add BUFFERS, and it also counts the pages of 8 kB that it read. In the cookbook, the plan says “page by page” or “by the index”, and ANALYZE counts the pages you really turned.

Here is the smallest useful example: the ticket list query on the small data, 200 tickets. The project’s new script, scripts/explain.py (you copy it in the guided practice below), puts EXPLAIN (ANALYZE, BUFFERS) in front of the repository’s real query, with its parameters.

python -m scripts.explain queue

Limit  (cost=14.12..14.14 rows=8 width=199) (actual time=0.114..0.120 rows=8.00 loops=1)
  Buffers: shared hit=17
  ->  Sort  (cost=14.12..14.14 rows=8 width=199) (actual time=0.113..0.114 rows=8.00 loops=1)
        Sort Key: created_at DESC, ticket_id DESC
        Sort Method: quicksort  Memory: 27kB
        Buffers: shared hit=17
        ->  Seq Scan on tickets t  (cost=0.00..14.00 rows=8 width=199) (actual time=0.024..0.058 rows=8.00 loops=1)
              Filter: ((status = 'open'::text) AND (team = 'billing'::text))
              Rows Removed by Filter: 192
              Buffers: shared hit=11
Planning:
  Buffers: shared hit=148 dirtied=3
Planning Time: 0.607 ms
Execution Time: 0.161 ms

The plan is a tree. Each -> is a step, and a step gives its rows to the step above it. So the most indented line runs first. Read a plan from the bottom up:

The plan of the ticket list on the small data, in the order the steps run. EXPLAIN prints the same three steps upside down: the innermost line runs first.

Text description of the diagram

The tickets table has 200 rows. Step 1, a Seq Scan on tickets, reads all 200 rows. Its filter, status = ‘open’ AND team = ‘billing’, keeps 8 rows and removes 192. The planner expected 8 rows and found 8. Step 2, a Sort, puts the 8 rows in order by created_at DESC, then ticket_id DESC. It must see every row before it can give out the first one. Step 3, a Limit, returns the first rows, at most 20; here, all 8. The 8 rows go to the API.

Each step line has two brackets:

• (cost=… rows=8 width=199) is the plan, made before the query ran. rows is the planner’s estimate of the rows that the step returns. cost is in the planner’s own units, not in milliseconds. Use it only to compare the steps of one plan.
• (actual time=0.024..0.058 rows=8.00 loops=1) is what really happened. rows is the rows found. time is in milliseconds: the first row came at 0.024 ms, the last at 0.058 ms. loops says how many times the step ran.

The indented lines under a step give its details. Filter is the condition that the step checked on each row, and Rows Removed by Filter counts the rows that failed it. Buffers: shared hit=11 says that the scan read 11 pages, all found in PostgreSQL’s memory (“hit”). A page that came from the disk shows as read.

In plain words: read all 200 tickets, keep the 8 open billing ones, sort them, return them. On 200 rows, that is the right plan: reading a whole small table is cheaper than any index.

Larkfield’s slow page, step by step

Now the same query on the large data. This plan is from the course’s computer:

Limit  (cost=20075.88..20078.21 rows=20 width=201) (actual time=10.036..10.833 rows=20.00 loops=1)
  Buffers: shared hit=2577 read=14698
  ->  Gather Merge  (cost=20075.88..20263.39 rows=1610 width=201) (actual time=10.036..10.831 rows=20.00 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        Buffers: shared hit=2577 read=14698
        ->  Sort  (cost=19075.86..19077.53 rows=671 width=201) (actual time=9.154..9.155 rows=14.33 loops=3)
              Sort Key: created_at DESC, ticket_id DESC
              Sort Method: top-N heapsort  Memory: 35kB
              Buffers: shared hit=2577 read=14698
              Worker 0:  Sort Method: top-N heapsort  Memory: 35kB
              Worker 1:  Sort Method: top-N heapsort  Memory: 35kB
              ->  Parallel Seq Scan on tickets t  (cost=0.00..19058.00 rows=671 width=201) (actual time=0.283..8.997 rows=553.00 loops=3)
                    Filter: ((status = 'open'::text) AND (team = 'billing'::text))
                    Rows Removed by Filter: 99447
                    Buffers: shared hit=2485 read=14698
Planning Time: 0.030 ms
Execution Time: 10.841 ms

Read it from the bottom up, as before:

1. Parallel Seq Scan: a sequential scan reads the table page by page, from start to end. “Parallel” means that three processes share the work: two Workers and the main process, so loops=3. On average, each process kept 553 rows and removed 99,447. Together they read 300,000 rows to keep 553 × 3 = 1,659 open billing tickets.
2. Sort: each process sorts its own rows. top-N heapsort means that it keeps only the best 20 in memory while it reads, which is cheap.
3. Gather Merge: it merges the three sorted lists into one, still in order.
4. Limit: it takes the first 20 rows and stops.

The plan read 17,275 pages (hit plus read): about 135 MB, the whole table, to return 20 rows. The median of 5 runs was 11.0 ms. The planner expected 1,610 rows (on Gather Merge) and found 1,659: a good guess. Your plan can differ a little: the Sort can sit above a Gather instead. The scan and its rows stay the same.

Is 11 ms the problem? The page sends 41 queries, so look at the other two kinds too. For each ticket on the page, the code asks for its latest AI run. The plan of that query for ticket T-399000, from the same run:

Limit  (cost=13822.57..13822.58 rows=1 width=23) (actual time=7.403..8.122 rows=1.00 loops=1)
  Buffers: shared hit=1974 read=8506 written=5
  ->  Sort  (cost=13822.57..13822.58 rows=2 width=23) (actual time=7.402..8.122 rows=1.00 loops=1)
        Sort Key: created_at DESC
        Sort Method: quicksort  Memory: 25kB
        Buffers: shared hit=1974 read=8506 written=5
        ->  Gather  (cost=1000.00..13822.56 rows=2 width=23) (actual time=7.336..8.119 rows=1.00 loops=1)
              Workers Planned: 2
              Workers Launched: 2
              Buffers: shared hit=1974 read=8506 written=5
              ->  Parallel Seq Scan on ai_runs  (cost=0.00..12822.36 rows=1 width=23) (actual time=6.643..6.650 rows=0.33 loops=3)
                    Filter: (ticket_id = 'T-399000'::text)
                    Rows Removed by Filter: 149911
                    Buffers: shared hit=1974 read=8506 written=5
Planning Time: 0.025 ms
Execution Time: 8.128 ms

Another full scan: all 449,734 AI runs, to find the one run of one ticket. It took 8.3 ms (median of 5), and the page runs it 20 times. The third kind of query, the customer’s name, looks different:

Index Scan using customers_pkey on customers  (cost=0.29..8.30 rows=1 width=12) (actual time=0.002..0.002 rows=1.00 loops=1)
  Index Cond: (customer_id = 'C-3835'::text)
  Index Searches: 1
  Buffers: shared hit=3

An Index Scan is the cookbook’s index at the back. The primary key of customers is an index already. So PostgreSQL looked up C-3835 in it, read 3 pages, and went straight to the row. Index Cond is the condition that the index answered. It took 0.004 ms (median of 5).

Now add up one page. The list takes 11.0 ms, the latest runs 20 × 8.3 ms, and the names 20 × 0.004 ms. That is about 177 ms of database work, and 166 ms of it is the 20 scans of ai_runs. Tomás would have started with the list query. The plans say that the biggest cost hides in a query that looks tiny.

Explore: one page, with and without the right index

A preview of the next lesson: no index, and the index that the next lesson keeps. Use it to practise reading the steps.

Explore

The plan of one page of the ticket list, on 300,000 tickets

Time

11.0 ms

Pages read (8 kB each)

17,275

Rows looked at

300,000

Index size

no index

No index: three processes read every row of tickets, about 100,000 each. They keep the 1,659 open billing tickets, sort them, and the Limit returns the newest 20.

Open billing tickets (status=open&team=billing), no index: the steps in the order they run

#	Step	Rows expected	Rows found
1	Seq Scan Reads all 300,000 rows of tickets in 3 processes, and keeps the 1,659 that match the filter.	1,603	1,659
2	Sort Each process sorts its own rows by created_at DESC, ticket_id DESC. It must see every row before the first one can go out.	–	–
3	Gather Merge Merges the sorted rows of the 3 processes into one ordered list.	–	–
4	Limit Returns the first 20 rows and stops.	–	–

The steps and rows are from EXPLAIN (ANALYZE, BUFFERS) on the large data (300,000 tickets). The time is the median of 5 runs on the course's computer, with nothing else busy; your times will differ. "Rows expected" is the planner's estimate before the query runs; it moves a little from run to run.

The ticket list query of Module 3 (SELECT * … LIMIT 20) on the large data. The steps and rows are from EXPLAIN (ANALYZE, BUFFERS); the times are medians of 5 runs on the course's computer.

Text description of the explorer

The explorer has two controls. The first picks the index on tickets: no index, or the index (status, team, created_at, ticket_id). The second picks the page: open billing tickets or closed shipping tickets. A table shows the steps in the order they run, with the rows the planner expected and the rows it found. A readout shows the time, the pages read, the rows looked at and the index size.

No index, open billing: a Seq Scan in 3 processes reads 300,000 rows and keeps 1,659 (expected 1,603). Each process sorts its rows, a Gather Merge merges them, and the Limit returns 20. 11.0 ms, 17,275 pages.

No index, closed shipping: the same steps; the scan keeps 76,160 rows (expected 76,190). 16.1 ms, 17,275 pages.

With the index, open billing: one Index Scan reads the first 20 entries of the index, already in order, and the Limit returns them. 20 rows looked at, 0.013 ms, 17 pages. The index is 17.1 MB.

With the index, closed shipping: the same steps. 20 rows looked at, 0.012 ms, 24 pages.

With no index, the database looks at all 300,000 rows for either page. With the right index, it looks at 20. Also compare the two row columns: with the index, the scan found 20 rows, but the planner expected 1,711. That is not a bad guess. The estimate is for the whole range of the index, and the scan stopped early because the Limit had enough rows.

Joins in a plan

The list of Module 3 gets the customer names with 20 separate queries. A join would get them in one query. A plan shows a join as a step with two inputs. Two joins in psql, from the author’s run (your times differ; the Buffers lines and the last lines are left out):

EXPLAIN ANALYZE
SELECT t.ticket_id, c.name, t.subject
FROM tickets t JOIN customers c ON c.customer_id = t.customer_id
WHERE t.status = 'open' AND t.team = 'billing';

 Gather  (cost=1696.00..21562.52 rows=1667 width=46) (actual time=2.815..17.836 rows=1659.00 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Hash Join  (cost=696.00..20395.82 rows=695 width=46) (actual time=3.099..15.056 rows=553.00 loops=3)
         Hash Cond: (t.customer_id = c.customer_id)
         ->  Parallel Seq Scan on tickets t  (cost=0.00..19698.00 rows=695 width=41) (actual time=0.317..11.973 rows=553.00 loops=3)
               Filter: ((status = 'open'::text) AND (team = 'billing'::text))
               Rows Removed by Filter: 99447
         ->  Hash  (cost=446.00..446.00 rows=20000 width=19) (actual time=2.671..2.671 rows=20000.00 loops=3)
               Buckets: 32768  Batches: 1  Memory Usage: 1270kB
               ->  Seq Scan on customers c  (cost=0.00..446.00 rows=20000 width=19) (actual time=0.006..0.948 rows=20000.00 loops=3)

A Hash Join has two inputs. First, the Hash step reads all 20,000 customers into a lookup table in memory, keyed by customer_id. Then each of the 1,659 tickets looks up its customer in that table. A hash join is good when many rows meet many rows.

Now ask for one ticket:

EXPLAIN ANALYZE
SELECT t.ticket_id, c.name, t.subject
FROM tickets t JOIN customers c ON c.customer_id = t.customer_id
WHERE t.ticket_id = 'T-399000';

 Nested Loop  (cost=0.71..16.75 rows=1 width=46) (actual time=0.016..0.016 rows=1.00 loops=1)
   ->  Index Scan using tickets_pkey on tickets t  (cost=0.42..8.44 rows=1 width=41) (actual time=0.011..0.011 rows=1.00 loops=1)
         Index Cond: (ticket_id = 'T-399000'::text)
   ->  Index Scan using customers_pkey on customers c  (cost=0.29..8.30 rows=1 width=19) (actual time=0.004..0.004 rows=1.00 loops=1)
         Index Cond: (customer_id = t.customer_id)

A Nested Loop takes each row of its first input, and looks up the matching rows in the second input. It is good when the first input is small and the second has an index. It is bad when the first input has thousands of rows and the second has no index: then each row costs one full scan. Bound result sizes meets this case. A third kind, the Merge Join, walks two inputs that are already sorted on the join key.

The planner chose two different joins for the same two tables, because it expected 1,667 rows in one case and 1 in the other. So, are its estimates right?

Estimated rows and actual rows

The planner does not count rows before it plans; that would cost as much as the query. It uses statistics: a summary of each column that ANALYZE makes from a sample of rows. The statistics hold the most common values with their share of the rows, and a rough picture of the other values. From them, the planner estimates the rows of each step.

The course measured four estimates on the large data. Parallel workers were off, so each number is for the whole table:

Query on tickets	Rows expected	Rows found	Why
customer_id = 'C-3835'	4,640	4,322	A common value: it is in the sample, with its share. Close.
customer_id = 'C-0042'	17	5	A rare value: the planner uses an average for the values it did not see.
status = 'open' AND created_at < '2026-01-01'	1,691	1,009	The planner multiplies the two shares, as if the conditions were independent. Open tickets are mostly recent, so they are not.
status IN ('resolved', 'closed') AND closed_at IS NULL	11,465	0	A CHECK rule makes this impossible, but the planner does not read CHECK rules.

The numbers move a little between runs, because ANALYZE takes a new sample each time. A gap of 2 or 3 times rarely matters. A gap of 10 times or more can make the planner pick the wrong way, for example a nested loop over thousands of rows. So compare the two rows= values of each step, from the bottom up, and multiply by loops.

Guided practice: measure first, then read the plans

1. In ~/projects/ticket-api, with the virtual environment active, copy the script of this lesson from the snapshot end-of-m04:

   macOS / Linux

   S=../nextia-learning-labs/C20/snapshots/end-of-m04
   cp "$S/scripts/explain.py" scripts/

   Windows (PowerShell)

   $S = "..\nextia-learning-labs\C20\snapshots\end-of-m04"
   Copy-Item "$S\scripts\explain.py" scripts\

   Open it. It builds the query with the repository’s own list_query, so it explains the query that the API really sends.

2. Record the starting point of this module. Close other busy programs. Then run the benchmark on the large data, and save every number in a file:

   python -m scripts.bench --json bench-before.json

   It takes about half a minute. Keep bench-before.json: the last lesson of this module compares with it.

3. Explain the ticket list: python -m scripts.explain queue. Find the scan, the rows it removed and the pages it read.

4. Explain the latest AI run of one ticket: python -m scripts.explain latest-run --ticket T-399000.

5. Explain the messages of the same ticket: python -m scripts.explain messages --ticket T-399000. The ticket page uses this query.

6. Show only the plan, without running the query: python -m scripts.explain queue --no-analyze.

7. Open psql with docker compose exec db psql -U tickets -d tickets. Run the two joins of this lesson, then the key lookup EXPLAIN ANALYZE SELECT name FROM customers WHERE customer_id = 'C-3835';. Quit with \q.

Show the expected result

Step 2 prints three lines. One run on the course’s computer printed:

200 requests, 4 at a time, pool on (max 10)
median 438.7 ms | p95 500.8 ms | slowest 574.7 ms | 9.0 requests/s
errors: 0 {200: 200} | database queries per request: 41

Your median is a few hundred milliseconds. It depends on your computer and on what else runs: on a busy computer, the author got 558 ms. That is why you compare only runs from your own computer, under the same conditions. Two lines WARNING ticket_api: API_KEY is not set can come first. They only say that /v1/classify has no key on your computer.

Steps 3 and 4 show the two full scans of this lesson: Parallel Seq Scan on tickets t with Rows Removed by Filter: 99447, and Parallel Seq Scan on ai_runs with Rows Removed by Filter: 149911. Step 5 shows a third one:

        ->  Parallel Seq Scan on messages  (cost=0.00..15385.30 rows=1 width=98) (actual time=12.755..12.758 rows=1.00 loops=3)
              Filter: (ticket_id = 'T-399000'::text)
              Rows Removed by Filter: 230098

It reads all 690,297 messages, about 230,099 per process, to find the 3 messages of one ticket. The course run took 9.4 ms. Step 6 prints the steps of step 3 with only the first bracket on each line. Step 7 prints the plans of this lesson.

Now you know where the time goes: three queries read whole tables to find a few rows. The next lesson fixes them, one index at a time.

Independent variation: one big customer, one small customer

python -m scripts.explain customer --customer C-3835 explains the list of one customer’s tickets. C-3835 is the business customer, with 4,322 tickets; C-0042 has 5. Predict first: which of the two queries is faster, and by how much? Then run both, and compare the scan lines and the times.

Show an example answer

The scan lines of the author’s run:

              ->  Parallel Seq Scan on tickets t  (cost=0.00..19385.50 rows=1783 width=201) (actual time=0.107..13.372 rows=1440.67 loops=3)
                    Filter: (customer_id = 'C-3835'::text)
                    Rows Removed by Filter: 98559

              ->  Parallel Seq Scan on tickets t  (cost=0.00..19385.50 rows=7 width=201) (actual time=7.770..16.782 rows=1.67 loops=3)
                    Filter: (customer_id = 'C-0042'::text)
                    Rows Removed by Filter: 99998

The two times were close. The course measured a similar query, with fewer columns: 11.2 ms for C-3835 and 11.6 ms for C-0042. Most people predict that 5 tickets are much faster. They are not, because a sequential scan reads every row either way: the size of the table sets the work, not the number of matches. The estimates (1,783 and 7 per process) follow the patterns of the table above.

Common mistake

The planner still thinks the table looks like last week

This is a constructed incident. Imagine a payment outage: in one hour, 50,000 new open billing tickets arrive. You can make it happen safely inside a transaction, because ROLLBACK removes everything again. In psql:

SET max_parallel_workers_per_gather = 0;
BEGIN;
INSERT INTO tickets (ticket_id, customer_id, subject, body, channel, team, priority)
SELECT 'T-' || (800000 + i), 'C-0001', 'Payment page is down', 'I cannot pay.', 'web_form', 'billing', 1
FROM generate_series(1, 50000) AS i;
EXPLAIN ANALYZE SELECT count(*) FROM tickets WHERE status = 'open' AND team = 'billing';

Symptom: the scan line of the plan says:

   ->  Seq Scan on tickets  (cost=0.00..22323.00 rows=1667 width=0) (actual time=0.006..24.913 rows=51659.00 loops=1)

The planner expected 1,667 rows and found 51,659: 31 times more. Here the plan is a full scan either way. But the planner would plan a join of these rows for 1,667 rows, and that join can be very slow for 51,659.

Cause: the statistics are older than the new rows. PostgreSQL’s background worker, autovacuum, runs ANALYZE again after many changes, but not at once.

Diagnose: look for a big gap between expected and actual rows on the first step that reads the table. Then check when PostgreSQL last made the statistics: SELECT last_analyze, last_autoanalyze FROM pg_stat_user_tables WHERE relname = 'tickets';.

Fix: collect new statistics, and explain again:

ANALYZE tickets;
EXPLAIN ANALYZE SELECT count(*) FROM tickets WHERE status = 'open' AND team = 'billing';

   ->  Seq Scan on tickets  (cost=0.00..23073.00 rows=19589 width=0) (actual time=0.007..23.342 rows=51659.00 loops=1)

That is better, but still 2.6 times too few. The planner multiplies the two shares, as if the conditions were independent, and the new tickets are all open and billing. Extended statistics tell the planner that two columns go together:

CREATE STATISTICS tickets_status_team (mcv) ON status, team FROM tickets;
ANALYZE tickets;
EXPLAIN ANALYZE SELECT count(*) FROM tickets WHERE status = 'open' AND team = 'billing';

   ->  Seq Scan on tickets  (cost=0.00..23073.00 rows=52593 width=0) (actual time=0.007..23.867 rows=51659.00 loops=1)

Now the planner expects 52,593 and finds 51,659. Clean up with ROLLBACK;: it removes the tickets, the statistics object and the new statistics. Then run VACUUM ANALYZE tickets; to free the space that the removed rows used, and quit. Your estimates differ a little, because ANALYZE samples rows.

When to read a plan, and what it costs

Read a plan when a query is slower than you expect: before you change the query or add an index, and again after the change. Read it on data like the real data: the plan for 200 rows said nothing about 300,000. Know the limits:

• EXPLAIN ANALYZE runs the query. For a SELECT, nothing changes. For an INSERT, UPDATE or DELETE, the change really happens. So put it between BEGIN and ROLLBACK, as the next lesson does with a DELETE.
• One run is one sample. The first run can read pages from the disk, and later runs find them in memory. The course’s plan times are medians of 5 warm runs.
• The plan shows only the database. The network, Python and the wait for a connection are not in it. That is why the project has both scripts.explain and scripts.bench.
• EXPLAIN alone only estimates. It is safe and fast, so use it on a query that takes minutes.

See it end to endVector search over an open document collectionCase study

The arXiv case study reads the plans of a filtered vector search: for a rare category, the planner leaves the vector index and searches exactly.

Knowledge check

Check your understanding

A plan line says (actual time=0.283..8.997 rows=553.00 loops=3). How many rows did this step find in total, and why is it not 553?

Show the answer

1,659. rows is the average per loop. loops=3 says that the step ran three times: in two worker processes and in the main process of a parallel scan. 553 × 3 = 1,659. Always multiply rows by loops before you compare it with another number.

Check your understanding

The list query reads 300,000 tickets to return 20, and the latest-run query reads 449,734 AI runs to return 1. Which one costs the ticket list page more, and how do you know?

Show the answer

The latest-run query. One run of it takes 8.3 ms, a little less than the list query’s 11.0 ms. But the page runs it 20 times: about 166 ms against 11 ms. The plan gives the time of one run, and the code says how many times it runs. You need both to find the biggest cost.

Check your understanding

The planner expects 17 tickets for C-0042 and finds 5. Should you worry? Give a case where a gap between expected and actual rows would matter.

Show the answer

Not here: 17 and 5 are both small, and any plan for so few rows is fast. A gap matters when it is large and changes the planner’s choice. For example, after an import of 50,000 tickets, the planner expects 1,667 rows and picks a nested loop. The lookup then runs 51,659 times. The fix is ANALYZE, and extended statistics when two columns go together.

Recap

• A query plan is the list of steps that PostgreSQL chooses for a query. EXPLAIN shows it; EXPLAIN ANALYZE runs the query and adds the real rows and times; BUFFERS adds the pages.
• Read a plan from the most indented line up, and compare the estimated rows= with the actual rows= × loops.
• A Seq Scan reads the whole table; an Index Scan goes through an index. A Hash Join builds a lookup table from one input; a Nested Loop looks in the second input once per row of the first.
• On the large data, three queries read whole tables. The list took 11.0 ms, the latest AI run 8.3 ms (20 times per page), the messages 9.4 ms.
• Estimates come from sampled statistics. Fix old ones with ANALYZE, and related columns with extended statistics.
• You saved bench-before.json. Next: one index for each slow query, and its cost.

Next: Index deliberately