Slow Postgres, part 1 of 8. The SQL and raw numbers for every test are on GitHub.
A Seq Scan reads each page once, in order. An index lookup walks the B-tree and then reads one heap page per TID.
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.
One note before the numbers: the AlphaVPS link at the end is a referral link, so I may get a commission at no extra cost to you, and the netcup vouchers come from my own account.
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_seqscanout of app code, readpg_statsbeforeCREATE 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; 
Share of rows and share of pages for each status in the 30,000-row table. failed is 0.9% of the rows but sits on 94% of the pages.
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.
Two things decide how rows land on pages. Row width sets how many fit on one, and at 136 rows per page any value much more common than 1 in 136 ends up on nearly every page. Physical order is the other. I spread failed evenly on purpose. If your failed orders came from one bad deploy on one afternoon, they'd sit on a few neighbouring pages and the index would look great.
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.
Median of 5 warm runs on 5 million rows, one process, JIT off. Dots mark the plan Postgres chose on its own. Grey dotted line: Seq Scan on the clustered copy.
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.
By 10% the scattered matches sit on every single page, and the bitmap scan still won. It reads all 41,667 pages but checks only the 499,000 rows the bitmap points to, while the Seq Scan checks all 5 million. With the table in RAM a page read costs next to nothing, and the time goes into checking rows.
For clustered rows the index won at every share I tried, even 50%: 123 ms against 142.
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.
Same tables and queries read cold from disk, median of 5. Dots mark the plan Postgres chose. Grey dotted line: Seq Scan on the clustered copy.
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.
The bitmap scan at 2% read more heap pages than at 1%, 37,939 versus 29,143, and still finished more than six times faster, 190 ms against 1,275. From 2% to 10% it beat the Seq Scan again.
The 2% run touched 91% of the pages, so it was close to one long sequential read. The 1% run touched 70%, with holes in between. My best guess is OS readahead, which prefetches reads that look sequential and gives up when they're full of gaps. I haven't confirmed it yet.
The planner has no way to see that difference. All it has is random_page_cost = 4, a single guess at how much a jump costs next to a sequential read, and it used the same guess for both runs.
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.
CREATE TABLE orders_small AS SELECT * FROM orders WHERE id <= 200; CREATE INDEX ON orders_small (status); ANALYZE orders_small; EXPLAIN SELECT * FROM orders_small WHERE status = 'failed'; Seq Scan on orders_small (cost=0.00..4.50 rows=2 width=25) Filter: (status = 'failed'::text) Only 2 of the 200 rows match, but reading two pages and checking 200 rows costs just 2 + 2 + 0.5 = 4.50. Turn off enable_seqscan and the cheapest index plan costs 6.19, because its one index page alone is priced as a random read at random_page_cost = 4. If you check plans on a 200-row test fixture, this is the arithmetic you're looking at, and it tells you nothing about the same query on 30 million rows in production.
"We're on SSDs, set it to 1.1." Fair, and on this table that flips the plan to an Index Scan at cost 2.77. Both versions finish in 10 microseconds or less, so on a table this size it makes no difference.
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'; It holds 3,000 entries instead of 30,000 and takes 5 pages, where the full index takes 28. Paid orders, 90% of the inserts here, never touch it.
Postgres only uses it when it can prove your filter implies the index's WHERE, and a literal status = 'chargeback' does. With only the partial index in place, a prepared status = $1 under a generic plan falls back to a Seq Scan, because the planner can't prove anything about a value it hasn't seen. Generic plans get their own part later in the series.
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'; null_frac | n_distinct | most_common_vals | most_common_freqs -----------+------------+----------------------------------+------------------------ 0 | 4 | {paid,created,failed,chargeback} | {0.9,0.09,0.009,0.001} 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.
A negative n_distinct means a fraction of the row count. On tables bigger than the 30,000-row sample these numbers are estimates; you can raise the sample with ALTER TABLE ... ALTER COLUMN ... SET STATISTICS.
The same view has a correlation column, the planner's idea of physical order. On my 5-million-row tables it was 0.01 for the scattered copy and 1 for the clustered one, and that difference in physical order is what separates the scattered and clustered lines in both charts.
Before CREATE INDEX, run the pg_stats query, relpages and the rows-versus-pages count on your own table. If the value you care about sits on most of the pages and the table doesn't stay in memory, the index won't save you reads.
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.
My own projects run on netcup (VPS from €2.21 a month) and AlphaVPS (VPS from €2.54 a month). If you need a box for this, either works.
For netcup I have a few €5 vouchers for new customers. Each one works once, so take one and leave the rest for the next person:
36nc179418264998 36nc179418264997 36nc179418264996 36nc179418264995 36nc179418264994 36nc179418264993 Paste it into the "Redeem voucher" field at the bottom of the netcup cart. If they're all gone, message me on LinkedIn and I'll send you a new one.
Good luck with your plans. Part 2 is next: how to read Postgres EXPLAIN ANALYZE.
Finish
I hope you understood the instructions correctly. If you have any edits to this article, I will be waiting for your comments. If I really helped you, I kindly ask you to subscribe to my LinkedIn and provide feedback on this article.
All my articles and links: fomo.moe
