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_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 storesA 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 | chargebackRow 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 pagesThe 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% ENDFROM 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 | 30000136 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 costsAsk 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 = 22130000 rows × cpu_tuple_cost 0.01 = 30030000 rows × cpu_operator_cost 0.0025 = 75 --- 596Cost 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 onSelectivity 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 pagesFROM ordersGROUP BY statusORDER 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 RAMOn 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 cacheRAM 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 tableA 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