Postgres has an index built for exactly the query you’re running, and it walks straight past it to scan millions of rows instead. If you’ve stared at a plan doing that and couldn’t work out why, this one’s yours. I wanted to reproduce one of the nastier versions of that failure, the ORDER BY ... LIMIT 1 trap, small enough that anyone could run it locally and argue with the same planner I was arguing with.

The problem

Postgres had an index built for exactly this lookup and refused to use it, scanning about eighteen million rows in primary-key order to answer a query that returns one. The reason was a single number in the planner’s statistics that was wrong by 130x, and under a LIMIT that one wrong number is enough to turn a sub-millisecond query into a four-second one. This is me reproducing that failure from scratch and following the bad estimate back to where it lives.

My first 5 million rows behaved perfectly. Slightly rude when you’re trying to write a post about a broken plan, but good news for the benchmark. I pushed it to 20 million rows, still fine. The trap finally fired when I kept the column 82% NULL and tightened each session into bursts of 180 events. No planner cost knobs, no disabled scan types, no hand-written plan. Postgres 16 looked at a real index, chose the primary key anyway, and removed 17,999,000 rows by filter before finding the one I asked for.

The Docker harness, deterministic seed, raw CSVs, and untouched EXPLAIN output are in the repo. These are laptop numbers from one Docker container, the mechanism transfers, the absolute timings do not. I left the two failed shapes in the README too, because quietly pretending the 5 million row version worked would make for a cleaner story and a useless benchmark.

What ANALYZE thought n_distinct was

pg_stats estimate real count: 20,000

One ANALYZE at targets 100 and 2000, then two independently captured samples at 5000. The default estimate missed the real count by 3.056x; target 2000 was already close enough to fix this plan.

The table I used

The shape is an admin audit trail. Human activity carries a session ID, automated activity from CDC and scheduled jobs does not, so most of the column is NULL. A support session arrives as a tight burst and the table is append-only, which leaves equal values sitting beside each other on the same few pages.

CREATE TABLE audit_events (
    id           BIGSERIAL PRIMARY KEY,
    entity_type  TEXT NOT NULL,
    entity_id    BIGINT NOT NULL,
    session_id   UUID,
    event_type   TEXT NOT NULL,
    payload      JSONB NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE admin_sessions (
    id             UUID PRIMARY KEY,
    admin_email    TEXT NOT NULL,
    ip_address     INET NOT NULL
);

CREATE INDEX idx_audit_events_session_id ON audit_events (session_id);

The final seed has 20,000,000 events, 20,000 real non-null session IDs, and exactly 180 contiguous events per session. The target session is number 18,000 in insertion order, late enough that a primary-key walk has a long afternoon ahead of it.

The query is the kind of thing an admin console does all the time, jump to the first event in one session:

SELECT ae.*, s.admin_email, s.ip_address
FROM audit_events ae
JOIN admin_sessions s ON s.id = ae.session_id
WHERE ae.session_id = 'c92b26f6-8689-c7af-56b0-b08721897732'
ORDER BY ae.id ASC
LIMIT 1;

Following the bad plan

This is the target-100 plan exactly as Postgres printed it. I did not trim the ugly bit, the ugly bit is why we’re here.

Limit  (cost=0.72..1386.49 rows=1 width=92) (actual time=1409.718..1409.719 rows=1 loops=1)
  Buffers: shared read=223880
  I/O Timings: shared read=228.696
  ->  Nested Loop  (cost=0.72..763557.35 rows=551 width=92) (actual time=1409.717..1409.718 rows=1 loops=1)
        Buffers: shared read=223880
        I/O Timings: shared read=228.696
        ->  Index Scan using audit_events_pkey on audit_events ae  (cost=0.44..763542.15 rows=551 width=61) (actual time=1409.693..1409.693 rows=1 loops=1)
              Filter: (session_id = 'c92b26f6-8689-c7af-56b0-b08721897732'::uuid)
              Rows Removed by Filter: 17999000
              Buffers: shared read=223877
              I/O Timings: shared read=228.689
        ->  Materialize  (cost=0.29..8.31 rows=1 width=47) (actual time=0.021..0.021 rows=1 loops=1)
              Buffers: shared read=3
              I/O Timings: shared read=0.008
              ->  Index Scan using admin_sessions_pkey on admin_sessions s  (cost=0.29..8.30 rows=1 width=47) (actual time=0.017..0.017 rows=1 loops=1)
                    Index Cond: (id = 'c92b26f6-8689-c7af-56b0-b08721897732'::uuid)
                    Buffers: shared read=3
                    I/O Timings: shared read=0.008
Planning Time: 0.062 ms
Execution Time: 1409.730 ms

There it is, Rows Removed by Filter: 17999000. Postgres walked the primary key in id order because that gives it the ordering for free, checked session_id on every row, and found the first match after 17,999,000 misses. The session index was sitting right there.

The reason is the LIMIT 1. Postgres discounts an ordered scan when it believes plenty of rows will match, roughly by rows_needed / rows_available. If 551 rows should match and I only need one, surely the primary-key walk will bump into one early. Except this session has 180 rows and its first event lives near the end of the append-only table. The planner cannot know that last part from ordinary column stats, and the bad distinct estimate made its bet three times worse.

The LIMIT trap in one picture

Statistics target 100

Estimated551

Actual180

Statistics target 5000

Estimated179

Actual180

The equality estimate fell from 551 rows to 179 against 180 real matches. That was enough to make the LIMIT discount stop flattering the primary-key scan.

The lie in pg_stats

I followed the 551 back to the column statistics:

SELECT null_frac, n_distinct
FROM pg_stats
WHERE tablename = 'audit_events'
  AND attname = 'session_id';

At target 100, my run had null_frac = 0.819667 and n_distinct = 6544. The table had 20,000 real distinct non-null values, so pg_stats was short by 3.056x.

Postgres estimates equality rows roughly like this when the value is not in the most-common-values list:

rows ≈ reltuples × (1 − null_frac) / n_distinct
     ≈ 20,003,698 × (1 − 0.819667) / 6,544
     ≈ 551

That lands exactly on the plan’s estimate. Once I saw that, the mystery index choice stopped being mysterious, the planner was doing reasonable arithmetic with a bad input.

At the default statistics target of 100, ANALYZE aims for a sample of about 30,000 rows and estimates distinct values with the Haas-Stokes estimator. The physical layout is what makes this awkward. Each sampled block can show the estimator the same session ID over and over because all 180 events for that session were inserted together. It sees far less variety than the table really has, then underestimates n_distinct.

The fix

I raised the statistics target on this column and analyzed again:

ALTER TABLE audit_events
    ALTER COLUMN session_id SET STATISTICS 2000;
ANALYZE audit_events;

That run estimated n_distinct = 20003 against the real 20,000. The plan flipped immediately, estimated 180 matching rows, read the 180 real rows through idx_audit_events_session_id, sorted that tiny set by id, and returned the first one.

I also ran target 5000 with two independent ANALYZE passes, because sampled stats move and I wanted to see whether the estimate held:

ALTER TABLE audit_events
    ALTER COLUMN session_id SET STATISTICS 5000;
ANALYZE audit_events;
ANALYZE audit_events;

Both samples landed on exactly 20,000. Target 5000 was unnecessary for this local table, 2000 had already fixed the plan, but keeping both samples made the convergence visible instead of treating one lucky sample as a law of nature.

Here is the final target-5000 plan, again copied straight from the captured file:

Limit  (cost=718.75..718.75 rows=1 width=92) (actual time=0.042..0.042 rows=1 loops=1)
  Buffers: shared hit=9
  ->  Sort  (cost=718.75..719.19 rows=179 width=92) (actual time=0.041..0.042 rows=1 loops=1)
        Sort Key: ae.id
        Sort Method: top-N heapsort  Memory: 25kB
        Buffers: shared hit=9
        ->  Nested Loop  (cost=6.11..717.85 rows=179 width=92) (actual time=0.007..0.026 rows=180 loops=1)
              Buffers: shared hit=9
              ->  Index Scan using admin_sessions_pkey on admin_sessions s  (cost=0.29..8.30 rows=1 width=47) (actual time=0.002..0.002 rows=1 loops=1)
                    Index Cond: (id = 'c92b26f6-8689-c7af-56b0-b08721897732'::uuid)
                    Buffers: shared hit=3
              ->  Bitmap Heap Scan on audit_events ae  (cost=5.82..707.76 rows=179 width=61) (actual time=0.005..0.012 rows=180 loops=1)
                    Recheck Cond: (session_id = 'c92b26f6-8689-c7af-56b0-b08721897732'::uuid)
                    Heap Blocks: exact=3
                    Buffers: shared hit=6
                    ->  Bitmap Index Scan on idx_audit_events_session_id  (cost=0.00..5.78 rows=179 width=0) (actual time=0.003..0.003 rows=180 loops=1)
                          Index Cond: (session_id = 'c92b26f6-8689-c7af-56b0-b08721897732'::uuid)
                          Buffers: shared hit=3
Planning Time: 0.042 ms
Execution Time: 0.050 ms

Same query, corrected column statistics

Target 1001,409.730 ms

Target 50000.050 ms

Each captured plan got the same one-query warm-up. The local run dropped from 1,409.730 ms and 223,880 shared-buffer reads to 0.050 ms and 9 shared-buffer hits. That is about 28,195x on this laptop; compare the plans and buffer work, don't treat it as a capacity claim.

Stuff worth remembering

• A higher statistics target costs real work. ANALYZE samples more rows, takes longer, and stores a larger statistics object. If the ALTER TABLE sits inside a larger transaction, the lock lives as long as that transaction does too.
• Treat a major-version upgrade as a statistics reset even if your migration tooling carries the column setting across. Verify the per-column target and run ANALYZE before traffic comes back, otherwise you can reintroduce the same plan after a maintenance window and spend a morning wondering why history has a sense of humour.
• Run ANALYZE more than once while diagnosing this. It resamples, and one clean estimate can be luck. I care about whether the plan is stable across samples, not whether one run made my chart prettier.
• This failure shape is not exclusive to Postgres. Any cost-based optimizer working from sampled cardinality estimates can get talked into a bad plan when equal values are physically clustered. Append-only data grouped by a foreign key is where I now check the estimate before blaming the index.

The takeaway

The bit I keep coming back to is Rows Removed by Filter. When that number is enormous under a LIMIT, the index may be fine, the query may be fine, and the planner may simply believe it will get lucky much earlier than the data allows. Check the estimated row count against pg_stats, then fix the estimate the planner is actually using.