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.

Most advice about slow queries ends at "add an index." I'm a backend engineer. I run Postgres and MongoDB behind Node/NestJS APIs at scale. The slow queries that reach me usually already have an index. The plan even says Index Scan. It is still slow.

This article explains why, step by step. You will see what an index looks like inside, why the order of columns matters, what extra index features cost, how a wrong guess by the planner changes a query, and how to paginate without slowing down. It also covers a new Postgres 19 tool, EXPLAIN (ANALYZE, IO). At the end, I show that MongoDB's ESR rule is the same idea.

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 short version

• An index can be used and still be slow. Check how many pages the query reads (the Buffers line), not just the index name in the plan.
• Put equality columns first, then the sort column, then range columns.
• Covering and partial indexes can make reads much cheaper, but they cost disk space and can make updates more expensive.
• If the planner guesses the row count wrong, it can pick a bad plan. CREATE STATISTICS can help.
• Use cursor-based pagination instead of OFFSET.
• MongoDB has the same rule. It is called ESR.

The query every multi-tenant API has

The endpoint and the schema

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;

That gives 1,000 tenants (customers), 90% paid orders, 8% shipped, 2% pending, and a zip column that always decides city. That last detail matters later.

The endpoint: "latest 20 shipped orders for this tenant."

SELECT id, created_at, total FROM orders
WHERE tenant_id = 42 AND status = 'shipped'
ORDER BY created_at DESC LIMIT 20;

The first EXPLAIN (ANALYZE, BUFFERS)

Since Postgres 18, EXPLAIN ANALYZE shows buffer counts automatically (PG18 release notes). A buffer is one 8 KB page of data, so fewer buffers means less work. With no helpful index:

 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

Rows Removed by Filter is shown per loop, so multiply by 3. Postgres read the whole table to return 20 rows. That is about 20,700 buffers.

What an index looks like inside

Before fixing the query, it helps to see what an index really is. (I won't compare B-trees with LSM-trees here. I covered that in LSM-Trees Explained.)

Root, internal pages and leaves: three reads to find any key

The pageinspect extension (docs) lets you look at index pages directly:

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

The primary key over 2 million rows has three levels: a root page with 19 links down, a layer of internal pages, and leaf pages with 367 entries each. Any key is three page reads away.

                 [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

How searches survive page splits

The nbtree README in the Postgres source explains the design. Each page has a link to its right neighbor and a "high key", which is the upper limit of the keys that page may hold. Suppose a search lands on a page and its key is above that limit. That means the page split while the search was going down the tree, so the search just follows the link to the right. This is how readers avoid holding locks while they walk down the tree.

A few more details from the README and the B-tree docs will matter later:

• The row's location in the table (its heap TID) breaks ties, so every entry in the tree is unique.
• When a leaf page splits, Postgres shortens the key it sends up to the parent. It keeps only the columns needed to tell the two halves apart. INCLUDE columns are always dropped from these keys, which keeps the upper pages small.
• Deduplication stores repeated keys as one compact list. It runs lazily, only when a page is about to split. Since Postgres 14, bottom-up deletion also cleans out old row versions first.
• Indexes with INCLUDE columns can never use deduplication. Remember this one.

Column order matters

An index that starts with the sort column

Here is an index I have seen in real code. Someone thought, "the query sorts by created_at, so I'll put that first":

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

The plan says Index Scan, and both conditions appear under Index Cond. It looks fine. But it read 1,046 buffers to return 20 rows.

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 checked inside the index, but they don't always shrink the part that is scanned. Here, Postgres walks backwards through created_at. It checks tenant_id and status on every entry until it finds 20 matches. pgMustard has a good write-up of this "index scan, still slow" problem (pgMustard).

The number 1,046 is not a mystery. 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.

Equality first, then sort, then range

The fix is to put the equality columns first, then the sort column:

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. Postgres jumps to (42, 'shipped'), reads 20 entries that are already in created_at DESC order, and stops. There is no sort step.

Markus Winand gives the same advice: "index for equality first—then for ranges". He also rejects the myth that the most selective column should go first (Use The Index, Luke: ranges, column order).

Skip scan in Postgres 18: a safety net, not a plan

Postgres 18 added B-tree skip scan (PG18 release notes). It lets the planner use (tenant_id, status, created_at) even when the query has no tenant_id condition. It does this by searching the index once for each distinct tenant_id. The new Index Searches line shows this:

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

That is 1,001 searches and about 3,000 buffers. (I ran this before creating the partial index from the next section, so the planner could not use it.) With the partial index, the same query read 4 buffers. Skip scan helps 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.

Covering and partial indexes, and what they cost

Covering indexes: INCLUDE and Heap Fetches: 0

A covering index holds every column the query needs, so Postgres never has to visit the table. 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 to INCLUDE 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.

Index-only scans also depend on the visibility map. This map records which table pages hold only rows that every transaction can see. Postgres can skip a table page only if the map marks it that way (index-only scan docs). That is why I ran VACUUM after creating the index, and why Heap Fetches: 0 is the line to check. 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.

The costs: no deduplication, no HOT updates

Covering indexes aren't free. This one is 112 MB, compared with 43 MB for the primary key and 161 MB for the table. Indexes with INCLUDE can't use deduplication. And, as the next section shows, updating an included column is never a HOT update, which is a cheap kind of update.

Partial indexes for the small, busy part of a table

pending orders are 2% of the table, and they are the only ones a background job reads. A partial index stores only those rows:

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. The partial index docs list skipping very common values as a main use. The query must repeat status = 'pending' so the planner can see that the index applies. This is also the right shape for the ready rows of a Postgres job queue.

Cheap updates: HOT and fillfactor

When an update can be HOT

In Postgres, every update writes a new copy of the row. A heap-only tuple (HOT) update is a cheaper kind, because it skips writing new index entries. 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

A small table with a covering index makes the effect easy to see:

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';

Update on inc_demo (fillfactor 70, 100,000 rows)	Rows updated	HOT updates
note (in no index), 1 row in 10	10,000	10,000
total (an INCLUDE column), 1 row in 10	10,000	0

Why covered columns break HOT

A column in an INCLUDE list counts as used by an index. So updating total is never HOT, even though it isn't part of the search key. If a column changes on almost every request, think twice before covering it.

Fillfactor 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).

When the planner's guess is wrong

Columns that depend on each other

The planner assumes conditions are independent unless you tell it otherwise (planner statistics docs). But zip decides city, so city = 'city_42' AND zip = 'zip_421' is exactly as selective as the zip alone. The planner multiplies the two chances anyway:

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))

It estimated 8 rows per worker. The real number was about 661.

CREATE STATISTICS: dependencies, ndistinct, mcv

CREATE STATISTICS tells ANALYZE to collect statistics across several columns. 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.

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 vs 661 per worker, which is close enough. pg_stats_ext showed the dependency zip → city with a strength of 1.0.

A wrong guess that changed the plan

A wrong estimate on its own is harmless. It matters when it changes the plan. 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 (shortened):

 ->  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 it:

 ->  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.

ndistinct fixed GROUP BY city, zip in the same way. 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().

Pagination that stays fast

OFFSET 1,000,000 vs a cursor

To show the effect clearly, this test uses a global feed index with no tenant filter. The tenant version comes in the next 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

OFFSET reads a million index entries and throws them away. The keyset (cursor) version jumps straight to where the last page ended. id is a unique tie-breaker, so the order is always well defined. Winand calls this the "seek method" (OFFSET is bad, no-offset). Franck Pachot recently explained why the row comparison 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).

The bigint/numeric trap

A real multi-tenant feed filters by tenant and status first, so the equality columns must come before the cursor columns:

CREATE INDEX orders_tenant_feed_idx
  ON orders (tenant_id, status, created_at DESC, id DESC);

Pachot also shows a trap that I reproduced. 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.

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.

Postgres 19's EXPLAIN IO

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

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:

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

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 $in list 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 for IN lists of constants more efficient (PG17 notes). In both databases, check the plan for a sort step.
• Negation and regex. MongoDB treats $ne, $nin and $regex as range operators.
• Covered queries. MongoDB's covered query and Postgres's Index Only Scan are the same idea. In Postgres, Heap Fetches: 0 also depends on the visibility map.

A checklist for your next slow query

• [ ] 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 many IN lookups.
• [ ] Use INCLUDE only for columns that rarely change. After VACUUM, confirm Heap 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. Lower fillfactor only if updates are spread across many pages.
• [ ] If columns depend on each other, add CREATE STATISTICS with the kinds that fix your estimate. Check again after upgrades.
• [ ] Replace deep OFFSET with 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 IO to 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