- postgresql
- databases
- performance
- mongodb
The Index Is There and the Plan Still Reads 1,000 Pages
How Postgres indexes work inside, why column order matters, and how the query planner makes its choices. Tested on Postgres 19 and compared with MongoDB's ESR rule.
Muhammad Ahsan Farooq
· 24 min read
Imagine you add an index to a slow query, and the query is still slow. You open the query plan and it even says Index Scan. So the index is being used. Yet the query reads about a thousand pages of data to return twenty rows. How can that be?
I'm a backend engineer. I run Postgres and MongoDB behind Node/NestJS APIs at scale, and this is the kind of slow query that reaches me most often. The slow ones usually have an index already, so the advice "just add an index" has nothing left to offer.
This article is the explanation I wish I had when I started. It assumes you know basic SQL and what a table is, and nothing more. We begin with a small example and a slow query. Then we look at what an index really is, and use that picture to see why the first index we try doesn't help and what to do instead. After that we meet four more ideas that decide whether a query is fast: covering indexes, partial indexes, the cost of updates, and the planner's row estimates. We finish with pagination, a new Postgres 19 tool, and the way MongoDB follows the same rule.
About the numbers. Every plan below is real output from one machine running PostgreSQL 19 RC1 (8 vCPU, 15 GB RAM, default shared_buffers = 128MB, 2 million made-up rows). These are not production numbers. Times will differ on your hardware. Buffer counts are more stable, so focus on those. Postgres 19 is still a release candidate. RC1 came out on 2026-10-15, and the final release is planned for 2026-11-19 (release announcement). When I'm guessing instead of measuring, I say so.
The road map
Here is the path we'll take. Each step builds on the one before it.
- Our example: one table, one question and one slow plan.
- What an index is, using a book as the picture.
- Why an index can be used and still be slow, and the one rule that fixes it.
- Making a good index cheaper to read, with covering and partial indexes.
- What indexes cost when rows are updated.
- What happens when the planner guesses wrong.
- Paging through results without slowing down.
- Postgres 19's new I/O view and the same ideas in MongoDB.
- A checklist for your next slow query.
If you remember only one thing from this article, make it this: an index being used is not the same as an index being good. Count the pages a query reads.
Our example: an orders table and one slow question
Let's make everything concrete. Picture an online shop platform that serves many separate customers. We call each customer a tenant. All their orders live in one big orders table, and every order carries a tenant_id. Everything in this article uses this one table.
The table
Here is the table, followed by a script that fills it with test data:
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
tenant_id int NOT NULL,
status text NOT NULL,
created_at timestamptz NOT NULL,
total numeric(10,2) NOT NULL,
city text NOT NULL,
zip text NOT NULL,
note text
);
SELECT setseed(0.42);
INSERT INTO orders (tenant_id, status, created_at, total, city, zip)
SELECT (random()*999)::int + 1,
CASE WHEN r < 0.90 THEN 'paid' WHEN r < 0.98 THEN 'shipped' ELSE 'pending' END,
timestamptz '2026-01-01' + (g * interval '7 seconds'),
round((random()*500)::numeric, 2),
'city_' || (z / 10), -- 100 cities
'zip_' || z -- 1,000 zips; zip determines city
FROM (SELECT g, random() AS r, (random()*999)::int AS z
FROM generate_series(1, 2000000) g) s;
VACUUM (ANALYZE) orders;
The script creates 2 million orders for 1,000 tenants. Most orders (90%) are paid, 8% are shipped and 2% are pending. Notice one more detail: each zip belongs to exactly one city. It looks boring now, but it matters later, in the section about the planner's guesses.
The question
Now imagine the app has a page where a customer sees their most recent shipped orders. Behind that page sits one query: the latest 20 shipped orders for one tenant.
SELECT id, created_at, total FROM orders
WHERE tenant_id = 42 AND status = 'shipped'
ORDER BY created_at DESC LIMIT 20;
How Postgres plans to answer it
The query looks simple, but how does Postgres find the answer? Before it runs a query, Postgres makes a plan. A plan is a list of steps: which table to read, in what way, and how to sort. We can ask to see it with EXPLAIN. If we write EXPLAIN ANALYZE, Postgres also runs the query and adds what really happened, such as how many rows each step returned and how long it took. Let's try it on our query, before creating any index.
One extra line is worth knowing about. Since Postgres 18, EXPLAIN ANALYZE also prints buffer counts (PG18 release notes). A buffer is one 8 KB page of data that Postgres had to read, either from memory or from disk. Buffers will be our main measure of work in this article: fewer buffers means less work. Here is the plan:
Limit (actual rows=20.00 loops=1)
Buffers: shared hit=13345 read=7350
-> Gather Merge (actual rows=20.00 loops=1)
-> Sort (actual rows=15.00 loops=3)
Sort Key: created_at DESC
Sort Method: top-N heapsort Memory: 27kB
-> Parallel Seq Scan on orders (actual rows=52.67 loops=3)
Filter: ((tenant_id = 42) AND (status = 'shipped'::text))
Rows Removed by Filter: 666614
Execution Time: 33.853 ms
Let's read it from the bottom up. Parallel Seq Scan means Postgres read the table from start to finish, with several workers splitting the pages between them. For every row it checked the Filter: is tenant_id 42 and status 'shipped'? Rows Removed by Filter is shown per loop, so multiply by 3. That means almost all 2 million rows were read and thrown away. Only then did Postgres sort the few matches and keep the newest 20.
The cost shows up in the Buffers line: about 20,700 pages touched to return 20 rows.
That is the problem. We need a way to jump straight to the rows of tenant 42 instead of reading everything. That tool is an index, and the next section shows what one really is.
What an index is, and what it looks like inside
Think of the index at the back of a book. If you want every page that mentions a word, you don't read the whole book. You look the word up in the index, which is sorted alphabetically, and it gives you the page numbers. A database index works the same way. It is a separate, sorted copy of some columns, and each entry points to the row in the table where the full data lives. Because the entries are sorted, Postgres can find a value quickly. It can also read a range of values in order.
Postgres stores this sorted structure as a B-tree, which is a tree of pages. The top page (the root) tells Postgres which lower page to visit next. The pages in the middle do the same job. The pages at the bottom (the leaves) hold the real entries, in order. To find one value, Postgres walks from the root down to a leaf. It is easier to believe this once you have seen it, so let's look at a real one.
(I won't compare B-trees with LSM-trees here. I covered that in LSM-Trees Explained.)
A look at a real index
The pageinspect extension (docs) lets you read index pages directly. Here we inspect the index behind the primary key of our orders table:
CREATE EXTENSION IF NOT EXISTS pageinspect;
SELECT root, level, fastroot, fastlevel FROM bt_metap('orders_pkey');
SELECT blkno, type, live_items, free_size, btpo_level
FROM bt_page_stats('orders_pkey', (SELECT root FROM bt_metap('orders_pkey')));
SELECT itemoffset, ctid, itemlen, data FROM bt_page_items('orders_pkey', 1) LIMIT 4;
root | level | fastroot | fastlevel
412 | 2 | 412 | 2
blkno | type | live_items | free_size | btpo_level
412 | r | 19 | 7776 | 2
itemoffset | ctid | itemlen | data
1 | (3,1) | 16 | 6f 01 00 00 00 00 00 00 <- high key (0x16f = 367)
2 | (0,1) | 16 | 01 00 00 00 00 00 00 00 <- key 1 → heap block 0, item 1
3 | (0,2) | 16 | 02 00 00 00 00 00 00 00
4 | (0,3) | 16 | 03 00 00 00 00 00 00 00
Here is how to read this. The root page (block 412) has 19 links to the pages below it. Below that is one more layer of internal pages, and then the leaf pages, which hold 367 entries each. So the tree has three levels, and any key out of 2 million is just three page reads away. This is why an index is so much cheaper than reading the whole table. Here is the same thing as a picture:
[root 412: 19 downlinks]
/ | \
[internal] [internal] ... [internal]
/ | \
[leaf 1] → [leaf 2] → [leaf 3] → … (right-links; each leaf starts with its high key)
│
└─ (key, heap TID) → table row
A few details to remember
The Postgres source explains the design in the nbtree README, and the B-tree docs cover it too. Three details from them will matter later in this article:
- Every index entry points to a row in the table by its location (the heap TID). When two entries have the same key, this location breaks the tie, so every entry in the tree is unique.
- Deduplication saves space. When many entries share the same key, Postgres can store the key once, together with a list of row locations. It does this lazily, only when a page is about to split.
- Indexes with
INCLUDEcolumns can never use deduplication. Remember this one. We'll meetINCLUDEsoon.
Column order matters
Now we are ready to build an index for our query. The query filters on tenant_id and status, and it sorts by created_at. A multi-column index can hold all three columns. The question is in which order to list them, and it turns out that this order decides almost everything.
A natural first attempt
Here is the order I see most often in real code. The reasoning goes: "the query sorts by created_at, so I'll put that first." Let's create that index and run the query again:
CREATE INDEX orders_bad_idx ON orders (created_at, tenant_id, status);
Limit (actual rows=20.00 loops=1)
Buffers: shared hit=26 read=1020 written=697
-> Index Scan Backward using orders_bad_idx on orders (actual rows=20.00 loops=1)
Index Cond: ((tenant_id = 42) AND (status = 'shipped'::text))
Index Searches: 1
Execution Time: 9.037 ms
This looks like a success. The plan says Index Scan, and both of our filters appear under Index Cond, which means the index handles them. The execution time dropped from 34 ms to 9 ms, and the buffers dropped from about 20,700 to 1,046. But 1,046 pages to return 20 rows is still far too much. Why?
The reason is the column order. A multi-column index is sorted like a phone book: first by the first column, and only among equal values of that column, by the second column, and so on. A phone book sorted by last name and then first name makes it easy to find "Smith, John". But it does not help you find every "John", because the Johns are scattered across all the last names.
Our index starts with created_at, so it is sorted by time first, and the entries of tenant 42 are scattered all over it. Postgres starts at the newest entry and walks backwards through time. It checks tenant_id and status on every entry, and stops only when it has found 20 matches. The multicolumn index docs give the exact rule. Equality conditions on the first columns, and a range on the first column, limit how much of the index is scanned. Conditions on columns further right are still checked inside the index, but they don't always shrink the part that is scanned. pgMustard has a good write-up of this "index scan, still slow" problem (pgMustard).
We can even predict the number 1,046. The condition tenant_id = 42 AND status = 'shipped' matches about 1 row in 12,500 (1 tenant in 1,000, times 8% shipped). So the 2 million rows hold about 160 matches, spread evenly over time. To find the newest 20, Postgres must walk back through about an eighth of the index, around 250,000 entries. A leaf page holds a couple of hundred entries, so that is roughly 1,000 pages. That is what the plan read.
So the index was used, but its column order did not match the query. What does a matching order look like?
Equality first, then sort, then range
The fix is to list the columns we compare with = first, and the sort column after them. Then all the entries for (tenant 42, 'shipped') sit right next to each other, already sorted by created_at. Postgres can jump to that spot and read the first 20 entries. (The INCLUDE part of this index is a separate idea. It gets its own section next, so ignore it for now.)
DROP INDEX orders_bad_idx;
CREATE INDEX orders_tenant_status_created_idx
ON orders (tenant_id, status, created_at DESC) INCLUDE (total, id);
VACUUM (ANALYZE) orders;
Limit (actual rows=20.00 loops=1)
Buffers: shared hit=1 read=3
-> Index Only Scan using orders_tenant_status_created_idx on orders (actual rows=20.00 loops=1)
Index Cond: ((tenant_id = 42) AND (status = 'shipped'::text))
Heap Fetches: 0
Index Searches: 1
Execution Time: 0.051 ms
Four buffers instead of 1,046, and no sort step. Postgres jumped to (42, 'shipped'), read 20 entries that were already in created_at DESC order, and stopped.
This gives us the rule: equality columns first, then the sort column, then any range column. Markus Winand gives the same advice: "index for equality first—then for ranges". He also rejects the popular myth that the most selective column should go first (Use The Index, Luke: ranges, column order). Think of the column order as a contract: the index is only fast for queries whose conditions match it.
What if the first column is missing? Skip scan in Postgres 18
A fair question is what happens when a query has no condition on the first column of the index. Postgres 18 added B-tree skip scan for this case (PG18 release notes). It lets the planner use (tenant_id, status, created_at) even when the query has no tenant_id condition, by searching the index once for each distinct tenant_id. The new Index Searches line shows how many searches it made. Here is a query with no tenant_id at all:
SELECT count(*) FROM orders
WHERE status = 'pending'
AND created_at >= timestamptz '2026-03-01' AND created_at < timestamptz '2026-03-02';
-> Index Only Scan using orders_tenant_status_created_idx on orders (actual rows=271.00 loops=1)
Index Cond: ((status = 'pending'::text) AND (created_at >= ...) AND (created_at < ...))
Index Searches: 1001
Buffers: shared hit=1920 read=1097
Execution Time: 7.207 ms
Skip scan worked, but it needed 1,001 searches (one per tenant) and about 3,000 buffers. (I ran this before creating the partial index from the next section, so the planner couldn't use it.) With that partial index, the same query read 4 buffers. So skip scan is a safety net for when an index is missing. It does not replace a good index.
The docs say skip scan is mostly chosen when the first column has few distinct values. 1,000 tenants is on the high side, yet it was still used. My guess is that the alternative was scanning the whole table. I didn't test that, so treat it as a guess.
Making a good index cheaper to read
Our fixed index reads only 4 buffers, and part of the reason is a detail we skipped over: the INCLUDE (total, id) at the end. This section explains it, and then adds a second idea that works in the opposite direction. The first idea puts extra columns into the index so that Postgres can skip the table. The second makes an index smaller by leaving out rows we never look for. Both have a price, so we'll also see what each one costs.
Idea 1: a covering index
A covering index holds every column a query needs, so Postgres never has to visit the table. That is what INCLUDE (total, id) does for us. You can see the effect in how I got there. My first version had only INCLUDE (total). That gave an Index Scan that touched 23 buffers, because id still had to come from the table. Adding id too turned it into an Index Only Scan at 4 buffers. (The fact that id is the primary key does not put it into this index. It has to be listed.)
There is one catch. Postgres can skip a table page only if it knows that every row on that page is visible to all transactions. It tracks this in the visibility map (index-only scan docs), and VACUUM keeps that map up to date. That is why I ran VACUUM after creating the index, and why Heap Fetches: 0 is the line to check in the plan. Postgres 19 also lets ordinary table scans update the map. Until now, only VACUUM and COPY FREEZE could (PG19 release notes). I expect this to help index-only scans on recently written data. That is my guess, not something the notes say.
What a covering index costs
Covering indexes aren't free. This one is 112 MB, compared with 43 MB for the primary key and 161 MB for the table. They also can't use deduplication, the space-saving trick from the B-tree section. And, as the next section shows, updating an included column is never a HOT update, which is a cheap kind of update. So we pay for faster reads with disk space and slower updates.
The second idea attacks the size problem from the other side.
Idea 2: a partial index
Suppose a background job keeps polling for pending orders. They are only 2% of the table, so why index the other 98%? A partial index stores only the rows that match a condition:
CREATE INDEX orders_pending_idx ON orders (created_at) WHERE status = 'pending';
SELECT id FROM orders
WHERE status = 'pending' AND created_at < timestamptz '2026-02-01'
ORDER BY created_at LIMIT 100;
Limit (actual rows=100.00 loops=1)
Buffers: shared read=44
-> Index Scan using orders_pending_idx on orders (actual rows=100.00 loops=1)
Index Cond: (created_at < '2026-02-01 00:00:00+05'::timestamp with time zone)
The index is only 888 kB, so it is cheap to store and cheap to read. There is one rule to follow: the query must repeat status = 'pending', so the planner can see that the index applies. The partial index docs list skipping very common values as a main use. This is also the right shape for the ready rows of a Postgres job queue.
Both ideas make reads cheaper. But every index has to be kept up to date when the table changes, and that is the cost we look at next.
The price of indexes: updates
So far we have only read data. Writes pay for indexes too. Each time a row changes, Postgres may have to change the indexes as well. To see when it can avoid that, we need a short detour into how Postgres updates a row.
How an update works
In Postgres, an update does not change a row in place. It writes a new copy of the row, and the old copy stays for a while, because other transactions may still need it. Normally the new copy also needs a new entry in every index, which is expensive. A heap-only tuple (HOT) update is a cheaper kind that skips the index work. It only works when two things are true. First, no column used by any index has changed (BRIN indexes are an exception). Second, the page that holds the old row has room for the new copy (HOT docs). Lowering a table's fillfactor leaves empty room on each page for this.
Measuring n_tup_hot_upd
Let's measure this. The script below uses a small table with a covering index. We update a column that no index uses, then a column that the covering index includes, and compare the HOT counters after each update:
CREATE TABLE inc_demo (id int PRIMARY KEY, tenant_id int, total numeric, note text)
WITH (fillfactor = 70);
INSERT INTO inc_demo SELECT g, g % 100, 1, 'x' FROM generate_series(1, 100000) g;
CREATE INDEX inc_demo_tenant_idx ON inc_demo (tenant_id) INCLUDE (total);
VACUUM ANALYZE inc_demo;
-- Variant 1: update a column that is in no index
SELECT pg_stat_reset_single_table_counters('inc_demo'::regclass);
UPDATE inc_demo SET note = 'w' WHERE id % 10 = 1;
-- wait a moment for stats, then:
SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'inc_demo';
-- Variant 2: update the INCLUDE column, same rows
SELECT pg_stat_reset_single_table_counters('inc_demo'::regclass);
UPDATE inc_demo SET total = 2 WHERE id % 10 = 1;
-- wait a moment for stats, then:
SELECT n_tup_upd, n_tup_hot_upd FROM pg_stat_user_tables WHERE relname = 'inc_demo';
What the numbers show
The table tells the story. Updating note, which no index uses, was HOT every time. Updating total was never HOT, even though it is not part of the search key. A column in an INCLUDE list counts as used by an index. That is the price of covering indexes that I promised earlier. If a column changes on almost every request, think twice before covering it.
Fillfactor has a limit too. It reserves a fixed share of every page. If one update touches every row on a page, the free space is not enough for all the new copies, so some of those updates will not be HOT. Updates spread across many pages usually fit. This follows from the HOT rules above. I did not measure it here, so check n_tup_hot_upd on your own workload. Crunchy Data's post on HOT and fillfactor explains how to monitor it (Crunchy Data).
That completes the picture of what indexes cost: disk space and slower updates. But all of this assumes that Postgres actually picks the index we built. That choice belongs to the planner, and the planner works from guesses.
When the planner's guess is wrong
Until now, we decided which indexes exist. Postgres decides which one to use, and how to run the whole query, with a part of the system called the planner. The planner can't know in advance how many rows a condition will match, so it makes an estimate. It bases the estimate on statistics that ANALYZE collects about each column. Most of the time the estimates are good. When they are badly wrong, the planner can pick a poor plan, even if the right index exists. Let's look at a case where that happens.
An estimate that goes wrong
Remember that each zip belongs to exactly one city? Here is where that matters. The planner assumes that conditions on different columns are independent, unless you tell it otherwise (planner statistics docs). Take city = 'city_42' AND zip = 'zip_421'. Because zip decides city, the second condition adds nothing: the pair is exactly as selective as the zip alone. The planner doesn't know that. It multiplies the two chances anyway and gets a number that is far too small:
SELECT count(*) FROM orders WHERE city = 'city_42' AND zip = 'zip_421';
-> Parallel Seq Scan on orders (cost=0.00..33119.00 rows=8 width=0) (actual rows=661.33 loops=3)
Filter: ((city = 'city_42'::text) AND (zip = 'zip_421'::text))
The planner estimated 8 rows per worker. The real number was about 661, so the estimate was roughly 80 times too low.
How do we tell the planner that these two columns depend on each other?
The fix: CREATE STATISTICS
CREATE STATISTICS tells ANALYZE to collect statistics across several columns together. There are three kinds. dependencies helps with equality filters like this one. ndistinct helps estimate GROUP BY results. mcv stores the most common combinations of values. Let's create the first two for our pair of columns:
CREATE STATISTICS orders_city_zip_stats (dependencies, ndistinct) ON city, zip FROM orders;
ANALYZE orders;
-> Parallel Seq Scan on orders (cost=0.00..33119.00 rows=828 width=0) (actual rows=661.33 loops=3)
The new estimate is 828 per worker, against a real 661. That is close enough. pg_stats_ext shows that Postgres found the dependency zip → city with a strength of 1.0.
A better estimate is nice, but what matters is whether it changes what the query does.
A wrong guess that changed the plan
A wrong estimate on its own is harmless. It matters when it changes the plan. To show that, let's join the filtered orders to a small lookup table with 1,000 rows:
CREATE TABLE zip_info AS
SELECT 'zip_' || z AS zip, 'region_' || (z % 7) AS region FROM generate_series(0, 999) z;
ALTER TABLE zip_info ADD PRIMARY KEY (zip);
ANALYZE zip_info;
SELECT z.region, count(*) FROM orders o JOIN zip_info z USING (zip)
WHERE o.city = 'city_42' AND o.zip IN ('zip_420', 'zip_421', 'zip_422')
GROUP BY z.region;
Without the statistics object, the planner expects only 25 matching orders (shortened plan):
-> Nested Loop (cost=0.28..34189.65 rows=25 width=9) (actual rows=2008.00 loops=3)
-> Parallel Seq Scan on orders o (... rows=25 ...) (actual rows=2008.00 loops=3)
-> Index Scan using zip_info_pkey on zip_info z (actual rows=1.00 loops=6024)
Index Searches: 6024
With the statistics object, it expects about 2,500 (shortened plan):
-> Nested Loop (cost=0.29..34523.30 rows=2488 width=9) (actual rows=2008.00 loops=3)
-> Memoize (cost=0.29..0.31 rows=1 width=16) (actual rows=1.00 loops=6024)
Cache Key: o.zip
Estimates: capacity=997 distinct keys=997 lookups=2488 hit percent=59.93%
Hits: 2244 Misses: 3 Evictions: 0 Overflows: 0 Memory Usage: 1kB
-> Index Scan using zip_info_pkey on zip_info z (actual rows=1.00 loops=9)
Once the planner expected thousands of rows instead of 25, it added a Memoize step, which caches lookup results. The 6,024 index lookups became 9. The Estimates: line on Memoize is new in Postgres 19 (PG19 release notes). To be honest, the total time was about the same in this run (roughly 43 ms vs 46 ms), because the lookup table is tiny and already in memory. The point is that the plan changed because the estimate changed. On a large or cold lookup table, those 6,024 lookups are where the time would go.
The ndistinct kind helped in the same way for GROUP BY city, zip. The estimate dropped from 100,000 groups to 1,000 (the real number is 1,000), and the plan switched from a single-process HashAggregate to a parallel partial aggregate.
One thing I couldn't explain: mcv
When I first created the object with all three kinds, (dependencies, ndistinct, mcv), the estimate for zip_421 stayed at 9 rows per worker. That zip was not in the 100-entry list of most common values. Without mcv, I got 828. I haven't confirmed why by reading the Postgres source. My guess is that the mcv statistics take over the conditions they cover, and then underestimate pairs that are not in the list. I saw this once, on made-up data. Treat it as "test it on your data", not as a rule. If you leave out the list of kinds, the CREATE STATISTICS docs say all kinds are used, so check which ones really improve your estimates.
One more note for upgrades: pg_upgrade has kept normal planner statistics since Postgres 18, but not extended statistics (PG18 notes). Postgres 19 adds pg_restore_extended_stats() and pg_clear_extended_stats().
Paging through results
All our queries so far returned the first 20 rows. Real screens also have a second page, a third page, and so on. The usual way to get page 100 is OFFSET. It looks innocent, but it hides a cost that grows with every page. Let's measure it.
OFFSET versus a cursor
We compare two ways to get the page that comes after one million rows. The first uses OFFSET. The second uses a cursor, also called keyset pagination. Instead of counting rows to skip, we remember the last row of the previous page and ask for the rows after it. To show the effect clearly, this test uses a global feed index with no tenant filter. The tenant version comes later in this section.
CREATE INDEX orders_feed_idx ON orders (created_at DESC, id DESC);
-- OFFSET
SELECT id, created_at FROM orders
ORDER BY created_at DESC, id DESC OFFSET 1000000 LIMIT 20;
-- Keyset: the cursor is the last (created_at, id) of the previous page
PREPARE next_page(timestamptz, bigint) AS
SELECT id, created_at FROM orders
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC LIMIT 20;
-- OFFSET
Limit (actual rows=20.00 loops=1)
Buffers: shared hit=1 read=3834 written=1682
-> Index Only Scan using orders_feed_idx on orders (actual rows=1000020.00 loops=1)
Execution Time: 87.025 ms
-- keyset
Limit (actual rows=20.00 loops=1)
Buffers: shared hit=3 read=1 written=1
-> Index Only Scan using orders_feed_idx on orders (actual rows=20.00 loops=1)
Index Cond: (ROW(created_at, id) < ROW('2026-03-23 00:26:47+05'::timestamp with time zone, '1000001'::bigint))
Execution Time: 0.054 ms
The difference is huge. OFFSET has to read a million index entries and throw them away, so it gets slower with every page. The cursor version jumps straight to where the last page ended and reads 20 entries, however deep we are. id is a unique tie-breaker, so the order is always well defined even when timestamps repeat. Winand calls this the "seek method" (OFFSET is bad, no-offset). Franck Pachot recently explained why the row comparison (created_at, id) < ($1, $2) beats the hand-written created_at < $1 OR (created_at = $1 AND id < $2). The row form becomes an Index Cond, while the OR form is only a Filter (Pachot, Oct 2026).
A trap: the cursor's data type
A real multi-tenant feed also filters by tenant and status. So the equality columns must come before the cursor columns, which is the column-order rule again:
CREATE INDEX orders_tenant_feed_idx
ON orders (tenant_id, status, created_at DESC, id DESC);
Even with the right index, there is a trap. Pachot shows it, and I reproduced it. If the cursor's id reaches Postgres as numeric instead of bigint, only the first cursor column stays in the index condition:
Index Cond: ((tenant_id = 42) AND (status = 'paid'::text) AND (created_at <= '2026-01-29 13:18:21+05'::timestamp with time zone))
Filter: (ROW(created_at, (id)::numeric) < ROW('2026-01-29 13:18:21+05'::timestamp with time zone, '352443'::numeric))
Rows Removed by Filter: 1
My data has few ties on created_at, so only one row was filtered out. If many rows share a timestamp, it could be many more.
To avoid this in a NestJS service, I keep both cursor values as strings from start to finish and cast them in SQL:
import { Injectable } from '@nestjs/common';
import { Pool } from 'pg';
// Both values stay strings end to end. node-pg parses timestamptz into a JS Date,
// which only has millisecond precision, so we read it back as text instead.
// It returns bigint as a string by default, so `id` is already safe.
type Cursor = { createdAt: string; id: string };
@Injectable()
export class OrdersRepository {
constructor(private readonly pool: Pool) {}
async page(tenantId: number, cursor?: Cursor, limit = 20) {
const params: unknown[] = [tenantId, limit];
let seek = '';
if (cursor) {
params.push(cursor.createdAt, cursor.id);
// cast the value, not the column
seek = 'AND (created_at, id) < ($3::timestamptz, $4::bigint)';
}
// The alias is created_at_text, not created_at, so ORDER BY still sorts by
// the real column and can use the index.
const { rows } = await this.pool.query(
`SELECT id, created_at::text AS created_at_text, total
FROM orders
WHERE tenant_id = $1 AND status = 'paid' ${seek}
ORDER BY created_at DESC, id DESC
LIMIT $2`,
params,
);
const last = rows.at(-1);
return {
rows,
next: last ? { createdAt: last.created_at_text, id: last.id } : null,
};
}
}
Reading the timestamp as text matters on real data. A JavaScript Date would cut microseconds down to milliseconds, which could skip or repeat rows at a page boundary. My made-up timestamps are 7 seconds apart, so they would never show this bug.
I haven't checked whether pg, Prisma or TypeORM ever send a bigint cursor as numeric by themselves. That is unverified. Run EXPLAIN on what your ORM really sends, and look for a Filter: on the second cursor column.
Seeing where the time goes: EXPLAIN IO in Postgres 19
Throughout this article we counted buffers. But a buffer can come from memory or from disk, and the difference in speed is huge. A plain buffer count can't tell the two apart. Postgres 19 adds a view that can.
What the new IO lines show
Postgres 17 added read streams, which read data ahead of time. Postgres 18 added asynchronous I/O. Postgres 19 adds an IO option to EXPLAIN ANALYZE that shows what these did (PG19 release notes, EXPLAIN docs). It shows the prefetch queue (average and maximum distance, capacity) and the I/O requests (count, average size in blocks, waits, average number running at once). It needs ANALYZE: EXPLAIN (IO) SELECT 1 fails with EXPLAIN option IO requires ANALYZE.
Cache vs disk: same "read", different waits
To see the difference, I removed the table from shared buffers with pg_buffercache_evict_relation() (pg_buffercache), cleared the operating system's page cache, and ran a scan. Then I ran the same scan again right away:
CREATE EXTENSION IF NOT EXISTS pg_buffercache;
SELECT * FROM pg_buffercache_evict_relation('orders');
EXPLAIN (ANALYZE, BUFFERS, IO, COSTS OFF)
SELECT count(*) FROM orders WHERE note IS NOT NULL;
-- cold
-> Parallel Seq Scan on orders (actual time=222.471..222.472 rows=0.00 loops=3)
Prefetch: avg=82.71 max=91 capacity=94
I/O: count=1347 waits=955 size=15.31 in-progress=5.28
Buffers: shared read=20619
Execution Time: 233.176 ms
-- immediately again: shared_buffers (128MB) is smaller than the table (161MB), but the OS cache is warm
Prefetch: avg=54.32 max=64 capacity=94
I/O: count=1355 waits=18 size=15.01 in-progress=4.21
Buffers: shared hit=282 read=20337
Execution Time: 32.661 ms
Both runs show about 20K pages "read" in BUFFERS. Only waits shows that the first run really waited on storage (955 of 1,347 requests) and the second did not (18). A size of about 15 blocks means Postgres combined reads into bigger requests.
The lesson for indexes is the one this article began with: the cheapest I/O is the I/O an index lets you skip. A covering index can answer a range query from index pages alone. A bitmap heap scan must fetch every matching table block, and each fetch is a chance to wait on storage.
Pachot's article goes further into reading these counters. It includes a Little's Law estimate of the time per request, which he labels as calculated, not measured (Pachot, Sep 2026).
What Postgres 19 doesn't do yet
In my runs, Index Scan and Index Only Scan nodes showed no IO lines. Read streams cover sequential scans, bitmap heap scans and TID range scans. Index prefetching is not in Postgres 19. Its patch is waiting for Postgres 20 (commitfest 4351). Storage-level I/O timing in EXPLAIN IO is also a candidate for Postgres 20 (commitfest 5526).
The same rule in MongoDB: ESR
If you use MongoDB, you have already met the main idea of this article under a different name. Everything we learned about column order applies there too.
The index in MongoDB
MongoDB calls this the ESR guideline: equality fields first, then sort fields, then range fields. If the range is very selective, putting the range before the sort (ERS) can be faster, at the cost of sorting in memory (MongoDB ESR guideline).
Here is the same endpoint in MongoDB, with a range on total added:
db.orders.find(
{ tenantId: 42, status: 'shipped', total: { $gte: 100 } },
{ _id: 1, createdAt: 1, total: 1 }
).sort({ createdAt: -1 }).limit(20);
// E, E, S, R
db.orders.createIndex({ tenantId: 1, status: 1, createdAt: -1, total: 1 });
The Postgres version:
CREATE INDEX orders_esr_idx ON orders (tenant_id, status, created_at DESC, total);
SELECT id, created_at, total FROM orders
WHERE tenant_id = 42 AND status = 'shipped' AND total >= 100
ORDER BY created_at DESC LIMIT 20;
In Postgres terms, total >= 100 is a condition to the right of the sort column. Postgres checks it inside the index, but it does not narrow the scan. ESR makes the same trade on purpose, so the index can return rows already sorted.
Where Postgres and MongoDB differ
$in/IN. MongoDB expands a$inlist with fewer than 201 items and merges the results in index order (SORT_MERGE), so it acts like equality. With 201 or more items it acts like a range, and the docs warn that this limit may change. Postgres 17 made B-tree searches forINlists of constants more efficient (PG17 notes). In both databases, check the plan for a sort step.- Negation and regex. MongoDB treats
$ne,$ninand$regexas range operators. - Covered queries. MongoDB's covered query and Postgres's
Index Only Scanare the same idea. In Postgres,Heap Fetches: 0also depends on the visibility map.
Putting it together: a checklist
We covered a lot. When you face a slow query, go through these steps in order. Each one points back to something we saw in this article.
- Run
EXPLAIN (ANALYZE, BUFFERS). Read the buffer counts before the times. - Compare the estimated
rows=with the real rows × loops on every step. - For each
Index Cond, check that the conditions are on the first columns of the index, not just present. - Order multi-column indexes as equality, then sort, then range. Don't put the "most selective" column first.
- Watch
Index Searches. A big number means skip scan or manyINlookups. - Use
INCLUDEonly for columns that rarely change. AfterVACUUM, confirmHeap Fetches: 0. - Use partial indexes for small, busy subsets of rows. Repeat the condition in your query.
- Track
n_tup_hot_upd / n_tup_upd. Lowerfillfactoronly if updates are spread across many pages. - If columns depend on each other, add
CREATE STATISTICSwith the kinds that fix your estimate. Check again after upgrades. - Replace deep
OFFSETwith a row-comparison cursor and a unique tie-breaker. Cast the cursor values (not the columns) and keep timestamps as text. - On Postgres 19, add
IOto tell cache reads from storage waits. - In MongoDB, follow ESR and check
explain()for in-memory sorts.
References
- PostgreSQL 19 RC1 announcement
- Release notes: 19, 18, 17
- PostgreSQL 19 docs: EXPLAIN, B-tree, multicolumn indexes, index-only scans, partial indexes, HOT, planner statistics, CREATE STATISTICS, pageinspect, pg_buffercache
- nbtree README
- Commitfest: 4351 (index prefetching), 5526 (storage I/O in EXPLAIN IO)
- MongoDB ESR guideline
- Use The Index, Luke: column order, ranges, fetch next page, no-offset
- Franck Pachot: pagination and row comparison, EXPLAIN ANALYZE IO in PostgreSQL 19
- pgMustard: an index scan doesn't mean it's fast
- Crunchy Data: HOT updates and fill factor
- My earlier post: LSM-Trees Explained