Back to Devoured - November 16, 2026
DEVOURED
Postgres not using your index? It's counting pages, not rows

Postgres not using your index? It's counting pages, not rows

Data Hacker Noon
Postgres ignores indexes because it optimizes for 8KB page reads rather than individual row counts, making indices ineffective for high-selectivity queries.
What: Anatolii Zhadan demonstrates that Postgres's query planner chooses execution plans based on estimated page reads, not rows. A query fetching 0.9% of rows can still require reading 94% of a table's data pages, causing the planner to favor a sequential scan over an index scan.
Why it matters: Developers often assume an index is a silver bullet for performance; this reveals that physical data layout on disk and page density frequently render indexes useless if the query doesn't restrict the number of pages fetched.
Takeaway: Before adding an index, check your column's physical data distribution using 'pg_stats' and count the pages occupied by specific values; if your target rows are scattered across almost every page, consider partial indexes or clustering.
Deep dive
  • Postgres reads data in 8KB pages; fetching a single row requires loading the entire page it resides on.
  • Index lookups perform well only when the required rows are concentrated on a small fraction of the table's total pages.
  • High-selectivity queries that are scattered physically across the table may cause more random I/O than a sequential scan.
  • The query planner uses 'cost' metrics based on estimated page reads, not row counts, which can lead to 'bad' plans if statistics are outdated.
  • 'ANALYZE' updates the planner's statistics, but 'pg_stats' should be inspected to understand how the planner views data distribution.
  • Partial indexes (e.g., 'WHERE status <> 'paid'') significantly reduce index size and can improve performance for queries targeting rare values.
Decoder
  • Heap: The primary table storage area in Postgres where actual data rows are stored.
  • TID (Tuple Identifier): A physical address consisting of a page number and an offset within that page, pointing to a specific row in the heap.
  • Bitmap Heap Scan: An execution plan that scans an index to build a map of required pages, then visits those pages in physical order to reduce random disk I/O.
  • Selectivity: The proportion of rows in a table that satisfy a given filter condition; low selectivity means a large portion of the table matches.
Original article

Slow Postgres, part 1 of 8. The SQL and raw numbers for every test are on GitHub.

You add an index on status, run EXPLAIN, and Postgres reads the whole table anyway. ANALYZE doesn't change the plan. Someone suggests SET enable_seqscan = off, the plan finally shows your index, and the query gets slower.

Usually the planner is right. It prices a plan by the 8 KB pages it has to read and by whether it reads them in order or jumps around between them, and once you count pages the same way, you can answer most "why is it ignoring my index" tickets yourself.

This series started as my notes from an open lesson on slow Postgres queries by balun.courses. The tests and numbers are my own.

The short version:

  • Postgres prices a plan in pages, and in my test table a status with 0.9% of the rows sits on 94% of them, so its index saves 12 page reads out of 221.
  • On 5 million rows in RAM, the index beat a full scan up to 10% of the rows when the matches were scattered, and at every share I tried (up to 50%) when they were stored together.
  • Read cold from disk, the scattered lookups at 0.5% and 1% of rows ran 2 to 5 times slower than a full scan, and Postgres still picked them.
  • A 200-row table gets a Seq Scan even when 2 rows match, so a plan from a tiny test copy says nothing about production.
  • Keep enable_seqscan out of app code, read pg_stats before CREATE INDEX, and if you only query the rare values, index only those rows.

What a B-tree index stores

A regular B-tree index keeps only keys, the values it is sorted by, and next to each key a TID: the row's address in the heap, which is the table's main storage.

A TID is a page number plus a slot on that page. You can see it as the hidden ctid column:

SELECT ctid, id, status FROM orders WHERE status = 'chargeback' ORDER BY id LIMIT 3;
  ctid   |  id  |   status
---------+------+------------
 (7,48)  | 1000 | chargeback
 (14,96) | 2000 | chargeback
 (22,8)  | 3000 | chargeback

Row 1000 is slot 48 on page 7. An index lookup for 'chargeback' returns a list of addresses like these, and Postgres then visits each of those heap pages to fetch the row and check that your transaction can see it.

The second step is the one people forget. To fetch one row, Postgres has to read the whole 8 KB page it sits on.

The lab: 30,000 orders on 221 pages

The lab runs on PostgreSQL 17.11 in Docker with default settings. My numbers come from a 4-vCPU, 8 GB cloud VM, but any small VPS or a laptop will do.

CREATE TABLE orders (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at   timestamptz NOT NULL,
  amount_cents int NOT NULL,
  status       text NOT NULL
);

INSERT INTO orders (created_at, amount_cents, status)
SELECT now() - (30000 - i) * interval '1 minute',
       (random() * 10000)::int,
       CASE
         WHEN i % 1000 = 0 THEN 'chargeback'  -- 30 rows, 0.1%
         WHEN i % 100  = 0 THEN 'failed'      -- 270 rows, 0.9%
         WHEN i % 10   = 0 THEN 'created'     -- 2,700 rows, 9%
         ELSE 'paid'                          -- 27,000 rows, 90%
       END
FROM generate_series(1, 30000) AS i;

CREATE INDEX orders_status_idx ON orders (status);
ANALYZE orders;

SELECT relpages, reltuples FROM pg_class WHERE relname = 'orders';
 relpages | reltuples
----------+-----------
      221 |     30000

136 rows fit on a page, so 30,000 rows take 221 pages. I picked 30,000 because ANALYZE samples 300 × default_statistics_target rows, which is 30,000 at the default. On this table it reads every row, so the statistics are exact and your plans should come out the same as mine.

What a Seq Scan costs

Ask for the most common value and the index goes unused:

EXPLAIN SELECT * FROM orders WHERE status = 'paid';

Seq Scan on orders  (cost=0.00..596.00 rows=27000 width=25)
  Filter: (status = 'paid'::text)

221 pages  × seq_page_cost     1.0    = 221
30000 rows × cpu_tuple_cost    0.01   = 300
30000 rows × cpu_operator_cost 0.0025 =  75
                                        ---
                                        596

Cost has no unit you can convert to milliseconds. It only means something next to another plan's cost.

The value you filter on appears nowhere in that formula. Ask for 'chargeback' with the index paths switched off and the Seq Scan still costs 596. Only the index path's price moves with the value, so the planner is choosing between a fixed price and a sliding one.

For 'paid', every one of the 221 pages holds paid rows, so the index path would read the whole heap plus the index. Force it with enable_seqscan = off and you get a Bitmap Heap Scan at cost 872.

How many pages each status sits on

Selectivity is the share of rows that pass the filter. The usual rule of thumb goes: one row, use the index; 1%, usually; 20%, it depends; 70%, Seq Scan. Here is the same table counted by pages as well as by rows:

SELECT status,
       count(*)                               AS rows,
       count(DISTINCT (ctid::text::point)[0]) AS pages
FROM orders
GROUP BY status
ORDER BY rows DESC;

The rule of thumb says index failed, and Postgres agrees: Index Scan at cost 235 against 596. Run both with EXPLAIN (ANALYZE, BUFFERS), though, and the index path touches 209 buffers to the Seq Scan's 221, a saving of twelve page reads.

It still finished faster, 0.19 ms versus 0.93, because checking 270 rows is less CPU work than filtering 30,000. For 'chargeback', 30 rows on 30 pages, the index path needs 32 buffers instead of 221.

Timing it on 5 million rows in RAM

On 221 pages everything finishes in a couple of milliseconds, so for timings I built a bigger table: 5 million rows, 326 MB, about 120 rows per page.

A bucket column holds a random number from 0 to 999,999, so WHERE bucket < 10000 matches 1% of the rows. I loaded the same rows twice, once in random order and once sorted by bucket, and forced each plan with the enable_* settings at shares from 0.01% to 50%. shared_buffers was 2 GB, which kept the whole table in memory.

For scattered rows Postgres picked a bitmap scan, which collects all the TIDs first and then reads each heap page once, in page order. Up to 10% of the rows it beat the Seq Scan, 98 ms to 124. At 25% it lost, 174 against 139, and the planner still chose it. A plain Index Scan, one heap visit per row, lost from 5% on.

The same queries with a cold cache

RAM is the friendly case. For the cold runs I restarted Postgres and dropped the OS page cache before every run, so every page came off the VM's disk.

The Seq Scan got about twice as slow, 250 to 350 ms at every share. The clustered index won everywhere again, 23 ms versus 263 at 5%.

The scattered bitmap scan took 748 ms at 0.5% of rows, to the Seq Scan's 349. At 1% it took 1.27 seconds against 255 ms, five times slower, and that's the plan Postgres picks.

Seq Scans on a 200-row table

A 200-row copy shows the other end of the scale, where the table is too small for any index to win.

A partial index instead of enable_seqscan

The one place for enable_seqscan = off is a scratch session, where it shows you what the other plan would cost. It applies to every query in the session, so in app code it also overrides the plans that were right, like 596 against 872 for 'paid'.

If the queries you care about only ask for the rare values, say a failed-payments dashboard or a chargeback queue, index only those rows:

CREATE INDEX orders_status_not_paid_idx ON orders (status)
  WHERE status <> 'paid';

The planner's statistics in pg_stats

Before adding an index, look at the column in pg_stats:

SELECT null_frac, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE schemaname = 'public' AND tablename = 'orders' AND attname = 'status';

That's what the planner believes about status as of the last ANALYZE. If most of your queries filter on paid, at 0.9, they get a Seq Scan, and no extra index will change that.

Run it on your own server

All the numbers above come from one 4-vCPU AMD EPYC cloud VM with 8 GB of RAM, so yours will differ. The SQL, the scripts, the raw output and the chart code are on GitHub. Run them on your own server, and if you get a different picture or think I got something wrong, open an issue or a PR.

Explore all 69 articles in today's digest

Fresh dev news, AI-digested daily. Never miss a trend at devoured.fyi