Postgres skips the fresh status index when 45,120 of 100,000 rows match the filter

The two row counts in the plan settle one question before anything else. The planner estimated 45,000 rows and the scan returned 45,120 ^[2]. The estimate missed by 0.27% ^[7]. Whatever is wrong with this query, the statistics on the status column are not it. Add the rows the filter discarded and the predicate matched 45.1% of the table ^[6].

A leaf tuple in a B-tree holds the key and a 6-byte heap item pointer, made of a block number and a line pointer offset ^[12]. The query asks for every column ^[1], so the index can only say where each row lives. Reading 45,120 matches through the index means 45,120 trips into the heap in key order, against one pass that reads every block once ^[20][5]. Force an index scan with a session flag and the same number of rows still match.

The page layout shows where the rest of the cost sits. Heap and index are both cut into fixed 8KB blocks ^[9]. Each index block spends 24 bytes on PageHeaderData and 16 bytes on BTPageOpaqueData at the end ^[10], which leaves 8,152 bytes for everything else ^[13]. Line pointers are 4 bytes each and grow down from the header, index tuples grow up from the bottom, and the free space between them is pd_upper minus pd_lower ^[11].

Duplicates are cheaper than they were. From PostgreSQL 13, equal keys are stored once followed by a packed array of up to 100 or more TIDs ^[14]. At 100 TIDs per posting list, the 45,120 shipped rows need at least 452 posting tuples instead of 45,120 copies of the string ^[15]. The saving is disk footprint on a low-cardinality column. A scan of those rows still touches the same number of heap pages.

Concurrency is the other constraint. A classical B-tree needs lock coupling: hold a read lock on the parent until you hold the child, and during a split hold parent, child and neighbour at the same time ^[16]. The post gives 10,000 queries per second as the illustration of where that breaks, not a measured workload ^[17]. PostgreSQL uses the Lehman and Yao B-link tree instead, where every page carries btpo_next to its right sibling on the same level ^[18] and the first tuple on any non-rightmost page is a high key bounding what that page may hold ^[19].

The post says the cost-based optimizer "understands the physics of physical disk blocks, random I/O penalties, and the internal structure of disk pages better than we do" ^[8]. It leaves out the planner's cost constants and the selectivity point at which an index scan starts to win. The 14.210 ms in the plan ^[4] belongs to that table on that machine. For it to mean anything on yours you would need the same block count, the same estimated row width of 128 bytes ^[2] and the same cache state.