How TABLESAMPLE picks rows

Most exploratory questions against a large table only need a rough answer, but without an index on status, even "roughly how many shipped orders" costs a full scan. On the two-million-row orders table, Postgres has to read every single 8 kB page, all 18,085 of them (141 MB), just to count matching rows. EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM orders WHERE status = 'shipped'; Aggregate Buffers: shared hit=18085 -> Seq Scan on orders (actual rows=500000.00 loops=1) Filter: (status = 'shipped'::text) Rows Removed by Filter: 1500000 Execution Time: 56.188 ms shared hit=18085 is one buffer access per page, every page found already in memory (Reading Buffer statistics in EXPLAIN output goes through the counters). At 141 MB of heap size that's nothing. Take a 1 TB table with 134 million pages: a scan reads all of them from disk, every time, however small the answer is. Postgres reads tables larger than a quarter of shared_buffers through a small ring buffer. See more in introduction…