In this post we are going to cover EXPLAIN and query tuning in PostgreSQL: how to read a plan, the scan types, the join algorithms, wrong estimates, work_mem, pg_stat_statements and pagination. These are 7 real PostgreSQL interview questions, asked the way they come up on the job.
Tuning a query without looking at its plan is guessing. EXPLAIN shows what Postgres decided to do and what it expected, and EXPLAIN ANALYZE shows what really happened. Most slow queries come down to one of a few patterns, and each one has a clear signature in the plan.
Every query below is complete, so you can copy it, run it on the sample data and get exactly the output shown under it.
Part 6 of the Real Time PostgreSQL Interview Questions and Answers series on TechBrothersIT.
- Let's create some sample data
- Question 1: A query is slow, and your lead asks: did you look at the plan? What is the difference between EXPLAIN and EXPLAIN ANALYZE, and how do you read the output?
- Question 2: In query plans you see Seq Scan, Index Scan, Bitmap Heap Scan and Index Only Scan. When does Postgres choose each one?
- Question 3: A plan shows Nested Loop in one query, Hash Join in another, and Merge Join in a third, for the same two tables. How does each join work, and when does Postgres pick it?
- Question 4: In EXPLAIN ANALYZE, a node says rows 346, but actual rows 30,000. Why is the estimate so far off, and why does it matter?
- Question 5: A report that sorts 200,000 rows is slow. In the plan you see: Sort Method external merge, Disk. What does that mean, and what do you change?
- Question 6: The application is slow, but nobody knows which queries are responsible. How do you find the queries that cost the database the most time?
- Question 7: The orders API is fast on page 1, but page 5,000 takes seconds. The query uses LIMIT 20 with a large OFFSET. Why does it get slower on every page, and how do you fix it?
- Key points to remember
- The full SQL script
Let's create some sample data
For query plans we need tables with real size. This post uses orders_big with 200,000 orders, customers_big with 5,000 customers, and events with 100,000 rows that were loaded but never analyzed. We also switch parallel query off for the session, so the plans stay short and easy to read. The script below creates everything this post needs. It was written for and run on PostgreSQL 16.
SET max_parallel_workers_per_gather = 0; -- keep the plans simple: no parallel workers in this post
CREATE TABLE orders_big AS
SELECT g AS order_id,
(g * 7 % 5000) + 1 AS customer_id,
CASE WHEN g % 100 = 0 THEN 'pending'
WHEN g % 3 = 0 THEN 'shipped'
ELSE 'delivered' END AS status,
DATE '2025-01-01' + (g % 500) AS order_date,
(g % 900) + 10 AS amount
FROM generate_series(1, 200000) AS g;
ALTER TABLE orders_big ADD PRIMARY KEY (order_id);
CREATE INDEX idx_orders_date ON orders_big (order_date);
CREATE INDEX idx_orders_customer ON orders_big (customer_id);
CREATE TABLE customers_big AS
SELECT g AS customer_id, 'Customer ' || g AS name,
(ARRAY['East', 'West', 'North', 'South'])[1 + g % 4] AS region
FROM generate_series(1, 5000) AS g;
ALTER TABLE customers_big ADD PRIMARY KEY (customer_id);
VACUUM ANALYZE orders_big;
VACUUM ANALYZE customers_big;
-- loaded, but never analyzed (autovacuum is switched off for this one table)
CREATE TABLE events WITH (autovacuum_enabled = false) AS
SELECT g AS event_id,
CASE WHEN g % 10 < 3 THEN 'error' ELSE 'info' END AS event_type,
g % 1000 AS device_id
FROM generate_series(1, 100000) AS g;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- needs shared_preload_libraries = 'pg_stat_statements'Question 1: A query is slow, and your lead asks: did you look at the plan? What is the difference between EXPLAIN and EXPLAIN ANALYZE, and how do you read the output?
EXPLAIN shows the plan Postgres intends to use, with estimated cost and rows, and does not run the query. EXPLAIN ANALYZE really runs it and adds the actual rows and time. Read a plan from the most indented line outward.
Every line of a plan is a node, and it carries four numbers. The first cost is the startup cost: the work before the first row comes out. The second is the total cost. Costs are in the planner's own units, not milliseconds. Rows is how many rows the planner expects. And width is the average size of a row in bytes.
(actual rows=5000 loops=1)Here is a plain EXPLAIN. Nothing was executed. These are only the planner's estimates.
EXPLAIN
SELECT * FROM customers_big;Seq Scan on customers_big (cost=0.00..82.00 rows=5000 width=22)
Now a real query with a join, run with ANALYZE. Start at the most indented lines: the two scans. Orders is filtered to pending, customers is loaded into a hash table. Then the Hash Join combines them, and the aggregate on top counts per region. On every line, compare the estimated rows with the actual rows.
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT c.region, COUNT(*) AS orders
FROM orders_big o
JOIN customers_big c
ON c.customer_id = o.customer_id
WHERE o.status = 'pending'
GROUP BY c.region;HashAggregate (cost=4131.60..4131.64 rows=4 width=13) (actual rows=1 loops=1)
Group Key: c.region
Batches: 1 Memory Usage: 24kB
-> Hash Join (cost=144.50..4121.70 rows=1980 width=5) (actual rows=2000 loops=1)
Hash Cond: (o.customer_id = c.customer_id)
-> Seq Scan on orders_big o (cost=0.00..3972.00 rows=1980 width=4) (actual rows=2000 loops=1)
Filter: (status = 'pending'::text)
Rows Removed by Filter: 198000
-> Hash (cost=82.00..82.00 rows=5000 width=9) (actual rows=5000 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 279kB
-> Seq Scan on customers_big c (cost=0.00..82.00 rows=5000 width=9) (actual rows=5000 loops=1)One warning. EXPLAIN ANALYZE really executes the statement. For an UPDATE or a DELETE, wrap it in BEGIN and ROLLBACK, so you see the plan but keep your data.
Question 2: In query plans you see Seq Scan, Index Scan, Bitmap Heap Scan and Index Only Scan. When does Postgres choose each one?
It depends on how many rows the filter matches. A few rows: Index Scan. A medium number: Bitmap Heap Scan, which sorts the page visits first. Most of the table: Seq Scan. And if the index has every needed column: Index Only Scan.
Think of a slider from one row to the whole table. For one row, an Index Scan walks the index and jumps straight to the row. For a few hundred rows, jumping one by one gets expensive. A Bitmap Heap Scan first collects all the matching pages in a bitmap, then reads each page once, in order. And when most rows match, the index only adds work. A Seq Scan reads the table from start to end.
best for very few rows
best for a medium number of rows
best when most rows match
One row, by primary key: an Index Scan.
EXPLAIN (COSTS OFF)
SELECT * FROM orders_big
WHERE order_id = 4242;Index Scan using orders_big_pkey on orders_big Index Cond: (order_id = 4242)
One day of orders, about 400 rows: a Bitmap Heap Scan, with a Bitmap Index Scan under it.
EXPLAIN (COSTS OFF)
SELECT * FROM orders_big
WHERE order_date = DATE '2025-06-01';Bitmap Heap Scan on orders_big
Recheck Cond: (order_date = '2025-06-01'::date)
-> Bitmap Index Scan on idx_orders_date
Index Cond: (order_date = '2025-06-01'::date)And a wide date range that matches most of the table: a Seq Scan, even though the same index is right there.
EXPLAIN (COSTS OFF)
SELECT * FROM orders_big
WHERE order_date >= DATE '2025-03-01';Seq Scan on orders_big Filter: (order_date >= '2025-03-01'::date)
Last one. Select only the indexed column, and Postgres does not need the table at all: an Index Only Scan.
EXPLAIN (COSTS OFF)
SELECT order_date FROM orders_big
WHERE order_date = DATE '2025-06-01';Index Only Scan using idx_orders_date on orders_big Index Cond: (order_date = '2025-06-01'::date)
So a Seq Scan in a plan is not automatically a problem. Ask how many rows the query needs. If it needs most of the table, the sequential scan is the right answer.
Question 3: A plan shows Nested Loop in one query, Hash Join in another, and Merge Join in a third, for the same two tables. How does each join work, and when does Postgres pick it?
Nested Loop: for each outer row, look up the inner table. Best for few outer rows with an index. Hash Join: build a hash table from the smaller input, probe it with the bigger one. Best for large, unsorted sets. Merge Join: walk two sorted inputs side by side.
Three ways to join the same tables. A nested loop takes each row from the outer side and searches the inner side, usually through an index. Wonderful for six rows, terrible for a million. A hash join reads the small table once into a hash table in memory, then scans the big table and probes it. A merge join needs both sides sorted on the join key, and then zips them together in one pass.
Six orders joined to their customers. A Nested Loop: an index scan finds the six orders, and for each one, an index lookup finds its customer.
EXPLAIN (COSTS OFF)
SELECT o.order_id, c.name
FROM orders_big o
JOIN customers_big c ON c.customer_id = o.customer_id
WHERE o.order_id BETWEEN 100 AND 105;Nested Loop
-> Index Scan using orders_big_pkey on orders_big o
Index Cond: ((order_id >= 100) AND (order_id <= 105))
-> Index Scan using customers_big_pkey on customers_big c
Index Cond: (customer_id = o.customer_id)Remove the filter, so all 200,000 orders are joined. Now it is a Hash Join. The hash is built on customers, the smaller table.
EXPLAIN (COSTS OFF)
SELECT o.order_id, c.name
FROM orders_big o
JOIN customers_big c ON c.customer_id = o.customer_id;Hash Join
Hash Cond: (o.customer_id = c.customer_id)
-> Seq Scan on orders_big o
-> Hash
-> Seq Scan on customers_big cAnd ask for the result sorted by customer ID. Both tables have an index on that column, so both can be read in order. Postgres picks a Merge Join, and gets the ORDER BY for free.
EXPLAIN (COSTS OFF)
SELECT o.order_id, c.name
FROM orders_big o
JOIN customers_big c ON c.customer_id = o.customer_id
ORDER BY c.customer_id;Merge Join Merge Cond: (o.customer_id = c.customer_id) -> Index Scan using idx_orders_customer on orders_big o -> Index Scan using customers_big_pkey on customers_big c
In tuning, the classic problem is a Nested Loop that was chosen because the planner expected five rows and got five hundred thousand. That is a statistics problem, and it is the next question.
Slow Nested Loop with a huge actual row count = a bad row estimate.
Question 4: In EXPLAIN ANALYZE, a node says rows 346, but actual rows 30,000. Why is the estimate so far off, and why does it matter?
The planner never looks at the data. It looks at statistics about the data. If they are missing or stale, the estimate is a guess, and a wrong guess leads to the wrong scan or join. Run ANALYZE to refresh them.
Here is the chain of cause and effect. The table was bulk loaded, and nobody analyzed it. With no statistics, the planner falls back to a default guess for the filter. It expects a few hundred rows, but 30,000 come out. And every decision above that node, which join, how much memory, is built on that wrong number.
The events table was loaded and never analyzed. The planner expects 346 rows. The scan really returns 30,000. That is almost a hundred times more.
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT * FROM events
WHERE event_type = 'error';Seq Scan on events (cost=0.00..1440.00 rows=346 width=40) (actual rows=30000 loops=1) Filter: (event_type = 'error'::text) Rows Removed by Filter: 70000
Run ANALYZE on the table, and explain the same query again. Now the estimate is within a few rows of the real 30,000.
ANALYZE events;EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT * FROM events
WHERE event_type = 'error';Seq Scan on events (cost=0.00..1826.00 rows=29840 width=13) (actual rows=30000 loops=1) Filter: (event_type = 'error'::text) Rows Removed by Filter: 70000
Autovacuum normally runs ANALYZE for you, but not immediately after a big load, and never on temporary tables. So analyze by hand at the end of an ETL job. And when two columns are related, like city and zip code, create extended statistics so the planner knows it.
After a bulk load: ANALYZE. Related columns: CREATE STATISTICS.
Question 5: A report that sorts 200,000 rows is slow. In the plan you see: Sort Method external merge, Disk. What does that mean, and what do you change?
The sort did not fit in work_mem, so Postgres wrote it to temporary files on disk. Give that query more work_mem and the same sort runs in memory. But work_mem is per sort or hash operation, so raise it for a session, not for everyone.
work_mem is the memory one sort or one hash may use. The default is only 4 megabytes. This sort needs about 16. So Postgres splits the data into sorted runs on disk, and merges the files. That is the external merge. With enough memory, the whole thing is one quicksort in RAM, with no temporary files.
With the default setting, here is the proof in the plan: Sort Method external merge, and the size of the temporary file on disk.
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM orders_big
ORDER BY amount, order_date;Sort (actual rows=200000 loops=1) Sort Key: amount, order_date Sort Method: external merge Disk: 7184kB -> Seq Scan on orders_big (actual rows=200000 loops=1)
Now raise work_mem for this session only, and run it again. Sort Method quicksort, Memory. No disk.
SET work_mem = '64MB';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM orders_big
ORDER BY amount, order_date;Sort (actual rows=200000 loops=1) Sort Key: amount, order_date Sort Method: quicksort Memory: 16551kB -> Seq Scan on orders_big (actual rows=200000 loops=1)
Be careful with the global setting. One query can run several sorts and hashes, and hundreds of connections can do that at the same time. Set it with SET LOCAL inside a transaction, or for a reporting role. And often the better fix is an index that returns the rows already sorted.
SET LOCAL work_mem or ALTER ROLE ... SET work_memQuestion 6: The application is slow, but nobody knows which queries are responsible. How do you find the queries that cost the database the most time?
Use the pg_stat_statements extension. It groups queries that differ only in their constants, and keeps counters for each one: calls, total time, mean time and rows. Sort by total time, not by the slowest single run.
It works like a counter board. Every statement that finishes is normalized: the constants are replaced by placeholders. So a thousand lookups with different IDs become one line. For each line, Postgres adds up the calls and the time. Now you can see that a fast query, called a million times, costs more than one slow report.
WHERE order_id = 1WHERE order_id = 2WHERE order_id = 3| query | calls | total_ms |
|---|---|---|
| ... WHERE order_id = $1 | 1,000,000 | 48,000 |
| SELECT ... big report | 12 | 9,600 |
Let's try it. Reset the counters, then run a few queries: two counts, three lookups by ID, and one aggregate.
SELECT COUNT(*) FROM orders_big WHERE amount > 500;
SELECT COUNT(*) FROM orders_big WHERE amount > 700;
SELECT * FROM orders_big WHERE order_id = 1;
SELECT * FROM orders_big WHERE order_id = 2;
SELECT * FROM orders_big WHERE order_id = 3;
SELECT status, SUM(amount) FROM orders_big GROUP BY status;Now ask pg_stat_statements for the top queries by total time. The three lookups are one line with three calls, and the constant became dollar one. The two counts are also one line. And the heaviest statements are on top.
SELECT left(query, 44) AS query,
calls,
round(total_exec_time::numeric, 1) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
rows
FROM pg_stat_statements
WHERE query LIKE '%orders_big%'
AND query NOT LIKE '%pg_stat%'
ORDER BY total_exec_time DESC
LIMIT 5;| query | calls | total_ms | mean_ms | rows |
|---|---|---|---|---|
| SELECT status, SUM(amount) FROM orders_big G | 1 | 22.1 | 22.08 | 3 |
| SELECT COUNT(*) FROM orders_big WHERE amount | 2 | 21.5 | 10.74 | 2 |
| SELECT * FROM orders_big WHERE order_id = $1 | 3 | 0.0 | 0.01 | 3 |
The extension must be listed in shared_preload_libraries, so it needs one restart to enable. For single slow statements, also set log_min_duration_statement, and the auto_explain module can write their plans to the log.
Enable once: shared_preload_libraries = 'pg_stat_statements' + restart.
Question 7: The orders API is fast on page 1, but page 5,000 takes seconds. The query uses LIMIT 20 with a large OFFSET. Why does it get slower on every page, and how do you fix it?
OFFSET does not jump. Postgres must read and throw away every row before the offset. Use keyset pagination: remember the last key of the previous page and ask for rows after it, so the index goes straight there.
To return 20 rows at offset 100,000, Postgres walks the index from the start and produces 100,020 rows. Then it discards the first 100,000. The deeper the page, the more work is thrown away. With keyset pagination, the query says: give me 20 rows after order 100,000. The index jumps to that key and reads exactly 20.
Here is page 5,001 with OFFSET. Look at the index scan: actual rows 100,020, to return 20.
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT order_id, order_date, amount
FROM orders_big
ORDER BY order_id
LIMIT 20 OFFSET 100000;Limit (actual rows=20 loops=1) -> Index Scan using orders_big_pkey on orders_big (actual rows=100020 loops=1)
And the same page with a key. The application passes the last order ID it showed. The index scan reads 20 rows. That is all.
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT order_id, order_date, amount
FROM orders_big
WHERE order_id > 100000
ORDER BY order_id
LIMIT 20;Limit (actual rows=20 loops=1)
-> Index Scan using orders_big_pkey on orders_big (actual rows=20 loops=1)
Index Cond: (order_id > 100000)Two conditions for keyset pagination. The sort key must be unique, so add the primary key as a tiebreaker. And you lose the jump to any page number: the API offers next and previous. For most feeds and exports, that is exactly what users need.
Key points to remember
- EXPLAIN estimates, EXPLAIN ANALYZE runs the query. Read from the inside out
- Few rows: Index Scan. Some rows: Bitmap Heap Scan. Most rows: Seq Scan
- Small lookups: Nested Loop. Big sets: Hash Join. Sorted inputs: Merge Join
- Estimate far from actual? Run ANALYZE. Sort on disk? Look at work_mem
- Find slow queries with pg_stat_statements, and page with a key, not OFFSET
The full SQL script
Here is everything in one script: the sample data and every query from this post, in order.
Show the full SQL script (sample data and every query)
-- ========================================================================
-- PostgreSQL Interview Q&A #6 - EXPLAIN and query tuning
-- TechBrothersIT - run on PostgreSQL 16
-- ========================================================================
-- Sample data
SET max_parallel_workers_per_gather = 0; -- keep the plans simple: no parallel workers in this post
CREATE TABLE orders_big AS
SELECT g AS order_id,
(g * 7 % 5000) + 1 AS customer_id,
CASE WHEN g % 100 = 0 THEN 'pending'
WHEN g % 3 = 0 THEN 'shipped'
ELSE 'delivered' END AS status,
DATE '2025-01-01' + (g % 500) AS order_date,
(g % 900) + 10 AS amount
FROM generate_series(1, 200000) AS g;
ALTER TABLE orders_big ADD PRIMARY KEY (order_id);
CREATE INDEX idx_orders_date ON orders_big (order_date);
CREATE INDEX idx_orders_customer ON orders_big (customer_id);
CREATE TABLE customers_big AS
SELECT g AS customer_id, 'Customer ' || g AS name,
(ARRAY['East', 'West', 'North', 'South'])[1 + g % 4] AS region
FROM generate_series(1, 5000) AS g;
ALTER TABLE customers_big ADD PRIMARY KEY (customer_id);
VACUUM ANALYZE orders_big;
VACUUM ANALYZE customers_big;
-- loaded, but never analyzed (autovacuum is switched off for this one table)
CREATE TABLE events WITH (autovacuum_enabled = false) AS
SELECT g AS event_id,
CASE WHEN g % 10 < 3 THEN 'error' ELSE 'info' END AS event_type,
g % 1000 AS device_id
FROM generate_series(1, 100000) AS g;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- needs shared_preload_libraries = 'pg_stat_statements'
-- Step 1 (question 1): explain only
EXPLAIN
SELECT * FROM customers_big;
-- Step 2 (question 1): explain analyze join
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT c.region, COUNT(*) AS orders
FROM orders_big o
JOIN customers_big c
ON c.customer_id = o.customer_id
WHERE o.status = 'pending'
GROUP BY c.region;
-- Step 3 (question 2): scan one row
EXPLAIN (COSTS OFF)
SELECT * FROM orders_big
WHERE order_id = 4242;
-- Step 4 (question 2): scan one day
EXPLAIN (COSTS OFF)
SELECT * FROM orders_big
WHERE order_date = DATE '2025-06-01';
-- Step 5 (question 2): scan most rows
EXPLAIN (COSTS OFF)
SELECT * FROM orders_big
WHERE order_date >= DATE '2025-03-01';
-- Step 6 (question 2): scan index only
EXPLAIN (COSTS OFF)
SELECT order_date FROM orders_big
WHERE order_date = DATE '2025-06-01';
-- Step 7 (question 3): join nested loop
EXPLAIN (COSTS OFF)
SELECT o.order_id, c.name
FROM orders_big o
JOIN customers_big c ON c.customer_id = o.customer_id
WHERE o.order_id BETWEEN 100 AND 105;
-- Step 8 (question 3): join hash
EXPLAIN (COSTS OFF)
SELECT o.order_id, c.name
FROM orders_big o
JOIN customers_big c ON c.customer_id = o.customer_id;
-- Step 9 (question 3): join merge
EXPLAIN (COSTS OFF)
SELECT o.order_id, c.name
FROM orders_big o
JOIN customers_big c ON c.customer_id = o.customer_id
ORDER BY c.customer_id;
-- Step 10 (question 4): estimate before
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT * FROM events
WHERE event_type = 'error';
-- Step 11 (question 4): run analyze
ANALYZE events;
-- Step 12 (question 4): estimate after
EXPLAIN (ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT * FROM events
WHERE event_type = 'error';
-- Step 13 (question 5): sort on disk
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM orders_big
ORDER BY amount, order_date;
-- Step 14 (question 5): sort in memory
SET work_mem = '64MB';
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM orders_big
ORDER BY amount, order_date;
-- Step 15 (question 6): some workload
SELECT COUNT(*) FROM orders_big WHERE amount > 500;
SELECT COUNT(*) FROM orders_big WHERE amount > 700;
SELECT * FROM orders_big WHERE order_id = 1;
SELECT * FROM orders_big WHERE order_id = 2;
SELECT * FROM orders_big WHERE order_id = 3;
SELECT status, SUM(amount) FROM orders_big GROUP BY status;
-- Step 16 (question 6): top queries
SELECT left(query, 44) AS query,
calls,
round(total_exec_time::numeric, 1) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
rows
FROM pg_stat_statements
WHERE query LIKE '%orders_big%'
AND query NOT LIKE '%pg_stat%'
ORDER BY total_exec_time DESC
LIMIT 5;
-- Step 17 (question 7): page with offset
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT order_id, order_date, amount
FROM orders_big
ORDER BY order_id
LIMIT 20 OFFSET 100000;
-- Step 18 (question 7): page with key
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT order_id, order_date, amount
FROM orders_big
WHERE order_id > 100000
ORDER BY order_id
LIMIT 20;
Part 7: Transactions, MVCC and locks
The sample data on this page is invented for teaching. Every output table and query plan is the real result of the query above it, run on PostgreSQL 16 over that sample data.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.