11 min left
← all posts
posted · Jul 13, 2026

PostgreSQL Ignored My Index: Chasing the Query Planner's 'Why'

A covering index sat right there, and Postgres chose a seq scan anyway. My friend called it a hallucination. I rebuilt the experiment with 5M rows — the planner wasn't hallucinating, it was doing arithmetic.

posted Jul 13, 2026reading 11 mindatabasespostgresqlperformance

My friend wrote a post titled Hallucinations of PostgreSQL Query Planner — a query had a perfectly usable index, and the planner ran a sequential scan over the whole table instead. The title smells like clickbait, but the behavior underneath is genuinely interesting. So I decided to reproduce it and chase the why.

Spoiler: the planner never hallucinates. It does arithmetic. And once you see the arithmetic, every "weird" decision in this post becomes the obviously correct one.

The setup

A ticket-booking table, and 5 million random rows. Roughly half of all tickets depart from Dhaka — that skew is deliberate, and it turns out to be the heart of the whole story.

CREATE TABLE tickets (
    id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    from_location text        NOT NULL,
    to_location   text        NOT NULL,
    travel_date   date        NOT NULL,
    ticket_type   text        NOT NULL,
    booking_code  text        NOT NULL,
    price         numeric(10,2) NOT NULL,
    created_at    timestamptz NOT NULL DEFAULT now()
);

INSERT INTO tickets (from_location, to_location, travel_date, ticket_type, booking_code, price)
SELECT
    CASE
        WHEN random() < 0.5 THEN 'Dhaka'
        ELSE (ARRAY['Chittagong','Sylhet','Rajshahi','Khulna','Barisal','Rangpur','Comilla'])[1 + floor(random() * 7)::int]
    END,
    (ARRAY['Dhaka','Chittagong','Sylhet','Rajshahi','Khulna','Barisal','Rangpur','Comilla','Cox''s Bazar'])[1 + floor(random() * 9)::int],
    CURRENT_DATE + (floor(random() * 90))::int,
    (ARRAY['economy','business','sleeper','ac'])[1 + floor(random() * 4)::int],
    upper(substr(md5(random()::text), 1, 8)),
    (300 + random() * 4700)::numeric(10,2)
FROM generate_series(1, 5000000);

The query under test, for every experiment below:

EXPLAIN ANALYZE SELECT id FROM tickets WHERE from_location = 'Dhaka';

Experiment 1: a covering index that works

First, a covering index — from_location as the key, with a few payload columns riding along via INCLUDE:

CREATE INDEX idx_fl_include_id ON tickets (from_location)
    INCLUDE (id, price, created_at);

First run:

Index Only Scan using idx_fl_include_id on tickets
    (cost=0.43..106645.79 rows=2517335 width=14)
    (actual time=7.900..475.427 rows=2501076 loops=1)
  Index Cond: (from_location = 'Dhaka'::text)
  Heap Fetches: 0
  Index Searches: 1
  Buffers: shared hit=14840 read=232
Planning Time: 0.965 ms
Execution Time: 581.813 ms

It uses the index — tadaa. But notice: even with the index, and even with Heap Fetches: 0, this took over half a second. An index is not a teleporter; the scan still visits ~15,000 index pages and pumps out 2.5 million rows.

Two things in that plan deserve unpacking before we go further: what a heap fetch is, and why the second run is so much faster.

What is the heap, and what is a heap fetch?

In Postgres, the heap is the table itself — the full rows, stored in 8 KB pages in no particular order. An index is a separate, smaller structure: sorted keys, each pointing back at a heap location.

A normal index scan works in two hops: find the entry in the index, then jump to the heap page to read the actual row. That second hop is the expensive one — millions of random page jumps hurt.

An index-only scan tries to skip the heap entirely: if every column the query needs lives in the index (that's what INCLUDE is for), the value can be returned straight from the index entry. Almost. There's a catch: the index doesn't know whether a row version is visible to your transaction — visibility info (MVCC) lives only in the heap. Postgres works around this with the visibility map, a tiny bitmap with one bit per heap page saying "every row on this page is visible to all transactions."

flowchart TD
    A["Index-only scan reads next index entry"] --> B{"Visibility map:<br/>is that heap page<br/>marked all-visible?"}
    B -- "yes" --> C["Answer straight from the index entry<br/>heap never touched"]
    B -- "no" --> D["Heap fetch:<br/>read the heap page,<br/>check row visibility (MVCC)"]
    C --> E["Heap Fetches: 0"]
    D --> F["Heap Fetches +1"]

Heap Fetches: 0 means the visibility map was fully set (a freshly loaded, freshly vacuumed table), so the scan never touched the heap once. That is the best case an index-only scan can hope for. If autovacuum falls behind on a heavily updated table, that same plan quietly starts doing millions of heap fetches — same plan shape, very different performance.

Why is the second run twice as fast?

Immediate second run of the same query:

Index Only Scan using idx_fl_include_id on tickets
    (cost=0.43..106645.79 rows=2517335 width=14)
    (actual time=6.007..292.046 rows=2501076 loops=1)
  Index Cond: (from_location = 'Dhaka'::text)
  Heap Fetches: 0
  Buffers: shared hit=15072
Planning Time: 0.126 ms
Execution Time: 377.811 ms

582 ms → 378 ms. Nothing about the plan changed — look at the Buffers lines. First run: shared hit=14840 read=232 — some index pages had to come from disk. Second run: shared hit=15072, zero reads — every page was already sitting in shared buffers.

To be precise about what's happening: the index is not built at query time — it has existed on disk since CREATE INDEX. What warms up between runs is the buffer cache. Cold cache pays disk I/O; warm cache is pure memory. (The JIT compilation overhead also drops from ~11 ms to ~6 ms on the repeat run, but the cache is the main event.) Keep this cold/warm distinction in mind — it's exactly the trap the "hallucination" accusation falls into later.

Experiment 2: one more column, and the planner walks away

Now the reproduction of my friend's mystery. Drop the index, recreate it with one extra INCLUDE column — to_location, a text column, so every index entry gets noticeably fatter:

DROP INDEX idx_fl_include_id;
CREATE INDEX idx_fl_include_id ON tickets (from_location)
    INCLUDE (id, price, created_at, to_location);

Same query. And:

Seq Scan on tickets
    (cost=0.00..119237.00 rows=2517167 width=14)
    (actual time=63.039..960.996 rows=2501076 loops=1)
  Filter: (from_location = 'Dhaka'::text)
  Rows Removed by Filter: 2498924
  Buffers: shared hit=1118 read=55619
Planning Time: 1.895 ms
Execution Time: 1071.620 ms

The planner ignored the index. A covering index, exactly matching the WHERE clause, exactly covering the SELECT list — and Postgres reads all 5 million rows and throws half of them away. This is the moment that looks like a hallucination.

It isn't. Let's do the planner's math with it.

The planner costs pages, not vibes

The planner never measures milliseconds. It estimates cost units, dominated by two questions: how many pages must be read, and how expensive is each read (sequential pages are cheap, seq_page_cost = 1; random pages are penalized, random_page_cost = 4) — plus small per-row CPU charges.

For the seq scan, the arithmetic is sitting right there in the plan. The table has 56,737 heap pages and 5,000,000 rows:

  56,737 pages   × seq_page_cost (1.0)      =  56,737
5,000,000 rows   × cpu_tuple_cost (0.01)    =  50,000
5,000,000 rows   × cpu_operator_cost (0.0025) = 12,500
                                       total = 119,237

That's not approximately the number in the plan — it's exactly cost=0.00..119237.00.

Now look at the index side across my three index variants. The only thing that changes between them is how fat each index entry is — which changes how many index pages hold the 2.5M Dhaka entries — which changes the cost:

Index payload Index-only scan cost Seq scan cost Planner's pick
INCLUDE (id) 83,967 119,237 index
INCLUDE (id, price, created_at) 106,645 119,237 index
INCLUDE (id, price, created_at, to_location) 121,459 119,237 seq scan

One text column pushed the covering index's cost from 106k to 121k — just past the seq scan's 119k. The planner picked the cheaper plan by a nose, exactly as designed. No hallucination; a photo finish.

flowchart TD
    Q["SELECT id WHERE from_location = 'Dhaka'"] --> S["Statistics say:<br/>~2.5M rows match — 50% of the table"]
    S --> C1["Cost the seq scan:<br/>56,737 cheap sequential pages<br/>+ per-row CPU = 119,237"]
    S --> C2["Cost the index-only scan:<br/>index pages for 2.5M fat entries,<br/>charged at random-access rates = 121,459"]
    C1 --> P{"Which is cheaper?"}
    C2 --> P
    P --> W["Seq scan wins by 2%"]

Note what's really driving this: selectivity. The predicate matches half the table. An index earns its keep by skipping most of the data; when the query wants 50% of all rows, there's not much to skip. With a filter matching 0.1% of rows, none of this drama happens — the index wins in a landslide at any width.

"But when I force the index, it's faster!" — the hallucination trap

This is almost certainly what my friend saw. Force the planner's hand:

SET enable_seqscan = off;

First (cold) run of the forced index-only scan:

Index Only Scan using idx_fl_include_id on tickets
    (cost=0.56..121458.98 rows=2517167 width=14)
    (actual time=10.618..1004.576 rows=2501076 loops=1)
  Heap Fetches: 0
  Buffers: shared hit=2 read=18738
Execution Time: 1109.176 ms

Second (warm) run:

Index Only Scan using idx_fl_include_id on tickets
    (cost=0.56..121458.98 rows=2517167 width=14)
    (actual time=2.733..267.785 rows=2501076 loops=1)
  Heap Fetches: 0
  Buffers: shared hit=6869 read=11871
Execution Time: 348.834 ms

And there's the trap, in full view:

  • Cold index scan: 1109 ms — slower than the seq scan's 1071 ms. The planner's call was right.
  • Warm index scan: 348 ms — crushes the warm seq scan's 691 ms. The planner's call looks wrong.

Run the comparison after the cache is warm — which is what you naturally do when poking at a query twice in a row — and you'll swear the planner is broken. But the planner can't see your buffer cache. It costs plans for the general case, using page counts and cost constants, not for the lucky case where 18,000 index pages happen to already be in RAM from your previous run. And notice the warm seq scan barely improved (1071 → 691 ms): 56k heap pages don't all fit in shared buffers, while the smaller index does. The cache rewards the index disproportionately — after you've paid to warm it.

That asymmetry is the entire "hallucination." The planner was judged on evidence it wasn't allowed to see.

Experiment 3: the minimal index

If fat entries are the problem, go minimal:

DROP INDEX idx_fl_include_id;
CREATE INDEX idx_fl_include_id ON tickets (from_location) INCLUDE (id);

First run — chosen voluntarily, no forcing:

Index Only Scan using idx_fl_include_id on tickets
    (cost=0.43..83967.29 rows=2503706 width=8)
    (actual time=0.107..447.814 rows=2501076 loops=1)
  Heap Fetches: 0
  Buffers: shared hit=9588
Execution Time: 554.590 ms

Second run:

Index Only Scan using idx_fl_include_id on tickets
    (cost=0.43..83967.29 rows=2503706 width=8)
    (actual time=0.228..212.895 rows=2501076 loops=1)
  Heap Fetches: 0
  Buffers: shared hit=9588
Execution Time: 293.031 ms

Cost 83,967 — far below the seq scan's 119,237, so the planner picks it without hesitation. Only 9,588 buffers touched instead of 56,737. Cheap cold start, very fast warm. Smallest index, best behavior, planner and reality in full agreement.

The flip side: ask for one column too many

Same minimal index, but now the query wants price — which this index does not include:

EXPLAIN ANALYZE SELECT id, price FROM tickets WHERE from_location = 'Dhaka';

Seq Scan on tickets
    (cost=0.00..119242.14 rows=2503706 width=14)
    (actual time=11.790..717.184 rows=2501076 loops=1)
  Filter: (from_location = 'Dhaka'::text)
  Rows Removed by Filter: 2498924
  Buffers: shared hit=16185 read=40552
Execution Time: 800.589 ms

Obvious in hindsight: price lives only in the heap, so an index-only scan is off the table. Any index route now has to visit the heap for 2.5 million rows. Seq scan it is.

What does the "use the index anyway" road look like? Force it:

SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT id, price FROM tickets WHERE from_location = 'Dhaka';

Bitmap Heap Scan on tickets
    (cost=59556.15..147589.48 rows=2503706 width=14)
    (actual time=218.018..969.048 rows=2501076 loops=1)
  Recheck Cond: (from_location = 'Dhaka'::text)
  Heap Blocks: exact=55599
  Buffers: shared read=65185
  ->  Bitmap Index Scan on idx_fl_include_id
        (cost=0.00..58930.23 rows=2503706 width=0)
        (actual time=203.613..203.618 rows=2501076 loops=1)
        Index Cond: (from_location = 'Dhaka'::text)
        Buffers: shared read=9586
Execution Time: 1080.719 ms

Postgres won't even do a plain index scan here — it picks a bitmap heap scan: walk the whole index to build a bitmap of matching heap pages, then read those pages in physical order. Smart damage control, but the damage is done: it touched 55,599 heap blocks — effectively the whole table — plus 9,586 index pages on top. 65,185 total buffers versus the seq scan's 56,737. The index turned into pure overhead.

flowchart LR
    subgraph SEQ["Seq scan"]
        A1["Read all 56,737 heap pages in order"] --> A2["Filter rows in place"]
    end
    subgraph IOS["Index-only scan — query fully covered"]
        B1["Walk 9,588 index pages"] --> B2["Done — heap untouched"]
    end
    subgraph BMP["Forced index, query NOT covered"]
        C1["Walk 9,586 index pages"] --> C2["Then read 55,599 heap pages anyway"]
    end

A covering index is binary: it either covers the query or it doesn't. There's no partial credit — one missing column and you're paying for the index and the heap.

Conclusion: be economical about indexes

The planner never once misbehaved in this whole story. Every decision came from the same boring arithmetic: pages to read × cost per page. So the rules that fall out are equally boring — and worth following:

  • Every INCLUDE column has a price. Fatter entries → more index pages → higher cost → at some point the planner correctly abandons your index. Include exactly what your hot queries read, nothing more. One text column flipped this experiment from index to seq scan.
  • Respect selectivity. A predicate matching 50% of the table barely benefits from any index. Indexes pay off when they let Postgres skip most of the data.
  • A covering index either covers or it doesn't. SELECT id flew; SELECT id, price face-planted. Audit the actual column lists of your hot queries before designing the index.
  • Don't benchmark against a warm cache and call the planner broken. Use EXPLAIN (ANALYZE, BUFFERS) and look at hit vs read. If your second run is magically fast, you're measuring your RAM, not your plan.
  • Watch Heap Fetches. Index-only scans are only "index-only" while the visibility map is set. If autovacuum falls behind, that number climbs and your fast plan silently rots.
  • enable_seqscan = off is a diagnostic, not a fix. It's how you ask "what did the road not taken cost?" — never something to leave on.
  • If the planner keeps picking seq scans on fast SSDs, tune the constants (random_page_cost is famously conservative for SSDs) instead of fighting individual plans.

My friend's planner wasn't hallucinating. It was the only one in the room not hallucinating — everyone else was looking at a warm cache and calling it truth.

Engage. Make it so.