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 msshared 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 to buffers.
At least Postgres avoids flushing its own cache by reading large tables through a small ring buffer. Nevertheless the disk still perform full read. On a primary server handling production traffic is shared with everything else. When all you need is exploratory confirmation "roughly how many" or "are there any representative samples" reading the whole terabyte is too expensive.
SQL Standard comes with TABLESAMPLE to help with that. It goes in the FROM clause right after the table name, and the rest of the query runs against the subset of pages it picks.
SELECT
id, status, created_at, amount
FROM orders TABLESAMPLE SYSTEM (1) REPEATABLE (7)
LIMIT 5; id | status | created_at | amount
-----+-----------+------------------------+--------
429 | delivered | 2024-01-01 00:07:09+00 | 38.46
430 | delivered | 2024-01-01 00:07:10+00 | 150.72
431 | delivered | 2024-01-01 00:07:11+00 | 295.59
432 | delivered | 2024-01-01 00:07:12+00 | 488.23
433 | delivered | 2024-01-01 00:07:13+00 | 238.84The value (1) is a percentage, and the size of the sample isn't fixed: five different seeds returned between 18,107 and 20,416 rows. If you need an exact number of rows, tsm_system_rows in contrib adds a SYSTEM_ROWS (n) method. The rows come back in physical order, which is why these five are neighbours, ids 429 to 433. And whatever you compute is computed over the sample, so totals have to be scaled back up by hand, count(*) and sum() times 100, while averages, ratios and extremes are read as they come.
Here's the shipped-orders count again, scaled up, with its buffer numbers:
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) * 100 FROM orders TABLESAMPLE SYSTEM (1)
WHERE status = 'shipped'; Aggregate
Buffers: shared hit=177
-> Sample Scan on orders (actual rows=4920.00 loops=1)
Sampling: system ('1'::real)
Filter: (status = 'shipped'::text)
Execution Time: 0.829 ms177 pages instead of 18,085, and the estimate comes out at 492,000 against the real 500,000. For counts like this one it holds up. The rows are inserted one per second in created_at order, though, so the oldest row sits at the very start of the table. Ask the same 1% sample for it:
SELECT min(created_at) FROM orders TABLESAMPLE SYSTEM (1);Six runs:
2024-01-01 02:51:13+00
2024-01-01 01:36:19+00
2024-01-01 01:27:24+00
2024-01-01 04:41:47+00
2024-01-01 02:51:13+00
2024-01-01 00:44:36+00The real answer is 00:00:01. The sample is off by anything from 44 minutes to almost five hours, and runs one and five agree to the second, which a sample of random rows would almost never do. Everything odd about TABLESAMPLE comes from one fact: it doesn't pick rows, it picks addresses.
Setup
Everything in this article was captured on PostgreSQL 18.6 using the postgres:18 image.shared_buffers = 1GB and a warm cache, so the timings are for comparing methods against each other, not for predicting yours.
CREATE EXTENSION IF NOT EXISTS pageinspect;
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL,
status text NOT NULL,
created_at timestamptz NOT NULL,
amount numeric(10,2) NOT NULL
);
SELECT setseed(0.42);
INSERT INTO orders (customer_id, status, created_at, amount)
SELECT (random() * 50000)::bigint + 1,
CASE WHEN i <= 1300000 THEN 'delivered'
WHEN i <= 1800000 THEN 'shipped'
WHEN i <= 1900000 THEN 'cancelled'
ELSE 'pending' END,
timestamptz '2024-01-01 00:00:00+00' + i * interval '1 second',
round((random() * 500)::numeric, 2)
FROM generate_series(1, 2000000) AS i;
VACUUM ANALYZE orders;
-- the same rows in random physical order
CREATE TABLE orders_shuffled AS SELECT * FROM orders ORDER BY random();
VACUUM ANALYZE orders_shuffled;
CREATE TABLE customers AS SELECT id FROM generate_series(1, 50001) AS id;
ALTER TABLE customers ADD PRIMARY KEY (id);
-- a small copy with room on every page, for the pageinspect step
CREATE TABLE h (LIKE orders INCLUDING ALL) WITH (fillfactor = 70);
INSERT INTO h OVERRIDING SYSTEM VALUE
SELECT * FROM orders WHERE id <= 1000 ORDER BY id;Every row has an address
The pages are numbered from 0, and right after its header each page keeps an array of line pointers, one per row, numbered from 1 (Inside PostgreSQL's 8KB Page takes one apart byte by byte). Page number and line pointer together are the row's ctid:
SELECT ctid, id, created_at FROM orders ORDER BY id LIMIT 3; ctid | id | created_at
-------+----+------------------------
(0,1) | 1 | 2024-01-01 00:00:01+00
(0,2) | 2 | 2024-01-01 00:00:02+00
(0,3) | 3 | 2024-01-01 00:00:03+00Both built-in sampling methods hash part of that address together with a seed and keep whatever hashes under a 1% cutoff. They differ only in which part. SYSTEM, in src/backend/access/tablesample/system.c, hashes the page number (trimmed to the lines that matter):
hashinput[0] = nextblock; /* page */
hashinput[1] = sampler->seed;
hash = DatumGetUInt32(hash_any((const unsigned char *) hashinput,
(int) sizeof(hashinput)));
if (hash < sampler->cutoff) /* keep the whole page */BERNOULLI, in bernoulli.c, hashes the page and the line pointer number:
hashinput[0] = blockno; /* page */
hashinput[1] = tupoffset; /* line pointer */
hashinput[2] = sampler->seed;So SYSTEM flips one coin per page and takes every row on a winning page. BERNOULLI flips one coin per line pointer, which means it has to open every page to see how many line pointers there are. That's the whole performance story: on this table SYSTEM answers in 1.2 ms and BERNOULLI in 10.4 ms.
Pages come as bundles
A page here holds about 107 rows, and since the rows went in by time, one page is 107 consecutive seconds of orders. Group a SYSTEM sample by the page half of ctid and the bundles are right there:
SELECT (ctid::text::point)[0]::int AS page, count(*) AS rows,
min(created_at)::time AS first, max(created_at)::time AS last
FROM orders TABLESAMPLE SYSTEM (1) REPEATABLE (7)
GROUP BY 1 ORDER BY 1 LIMIT 4; page | rows | first | last
------+------+----------+----------
4 | 107 | 00:07:09 | 00:08:55
38 | 107 | 01:07:47 | 01:09:33
57 | 107 | 01:41:40 | 01:43:26
265 | 107 | 07:52:36 | 07:54:22The sample isn't 20,000 scattered seconds. It's about 200 short strips of the timeline, and min() returns the start of whichever strip the hash kept first. Runs one and five above kept the same first page.
Shuffle the table and the problem disappears. orders_shuffled holds the same rows written in ORDER BY random() order, so every page is a handful of seconds from all over the three weeks, and the same query lands within a few minutes of the truth every time:
2024-01-01 00:00:06+00
2024-01-01 00:01:20+00
2024-01-01 00:01:43+00
2024-01-01 00:01:24+00
2024-01-01 00:02:50+00
2024-01-01 00:05:07+00BERNOULLI on the original, time-ordered table does just as well, because a coin per line pointer never bundles anything.
Nobody samples for a minimum, but the bundles hurt averages the same way. Across 200 seeds, avg(created_at) from a SYSTEM sample wanders ten times as far as the same average from BERNOULLI. The reason is plain once you think in pages: 107 rows from one page tell you about as much about the timeline as one row from that page would, so a SYSTEM sample of 20,000 rows has roughly the precision of a 200-row one. A column that has nothing to do with where rows sit, like a random amount, averages identically under both methods. Time columns, ever-increasing ids, and anything loaded in batches are the columns where it matters.
REPEATABLE remembers addresses
REPEATABLE (42) fixes the seed. Same seed, same hash, same addresses. Whether those addresses still hold the same rows depends on what happened to the table in between, and the address model predicts every line of this table. Each run captures a sample with seed 42, changes the table, captures again, and counts how many rows from the first sample are still in it:
| after | SYSTEM kept | BERNOULLI kept |
|---|---|---|
| append 200k rows | 17,595 of 17,595 | 19,800 of 19,800 |
| delete half the rows | 8,795 of 8,795 | 10,003 of 10,003 |
VACUUM | 8,795 of 8,795 | 10,003 of 10,003 |
UPDATE every row | 8,740 of 8,795 | 109 of 10,003 |
VACUUM FULL | 113 of 8,795 | 113 of 10,003 |
CLUSTER on a reversed index | 0 of 8,795 | 95 of 10,003 |
Appends create new addresses and deletes empty old ones; no surviving row moves, so nothing changes. VACUUM marks dead line pointers unused and compacts the tuple data inside each page, but it never renumbers a line pointer that still points at a live row (VACUUM at the Page Level walks through each phase). VACUUM FULL and CLUSTER build a fresh copy of the table, every row gets a new address, and both samples are effectively redrawn.
UPDATE is the interesting line. Postgres never overwrites a row in place: an update writes a new version, and when the page has room and no indexed column changed, it puts that version on the same page without touching any index. That's a HOT update (HOT Updates in Postgres), and here 99% of them were. Same page means SYSTEM doesn't notice. But the new version gets a new line pointer, and pageinspect shows it for one row of h:
SELECT ctid FROM h WHERE id = 3; -- (0,3)
UPDATE h SET amount = amount + 1 WHERE id = 3;
SELECT ctid FROM h WHERE id = 3; -- (0,76)
SELECT lp, lp_flags, lp_off, t_ctid
FROM heap_page_items(get_raw_page('h', 0)) WHERE lp IN (3, 76); lp | lp_flags | lp_off | t_ctid
----+----------+--------+--------
3 | 2 | 76 |
76 | 1 | 2792 | (0,76)Line pointer 3 is now LP_REDIRECT (lp_flags = 2), a signpost that says "moved to 76" so index entries pointing at (0,3) still find the row. BERNOULLI doesn't read signposts. It hashes line pointer 76, which is a fresh 1% coin flip, so almost every updated row drops out of the sample and a different 1% drops in.
Hashing the key instead
When people pin a seed, they usually want a development or test subset that stays the same from one day to the next. An address can't promise that, because updates and rewrites move rows. A primary key doesn't move, so hash the key:
SELECT * FROM orders WHERE (hashint8extended(id, 42) & 1023) < 10;hashint8extended hashes a bigint with a seed; keeping 10 of 1,024 buckets returns 0.98% of the rows. Through the same changes it kept every surviving row at every step, 9,916 of 9,916 after the delete, UPDATE, VACUUM FULL and CLUSTER included. It has no bundles either: its 200-seed spread on avg(created_at) matches BERNOULLI.
A key can also be a foreign key. Sample customers on hashint8extended(id, 42) and orders on hashint8extended(customer_id, 42), and you get 484 customers with all 19,432 of their orders. The same two tables sampled with SYSTEM (1) REPEATABLE (42) leave 17,517 of the 17,595 sampled orders pointing at a customer who isn't in the sample.
The price is a full scan, since the hash has to be computed for every row: 44.4 ms serially, 21.5 ms with two parallel workers. For a subset you pull often, an expression index on (hashint8extended(id, 42) & 1023) (14 MB here) turns it into a bitmap scan at 8.5 ms, with the seed fixed in the index definition.
The join doesn't shrink
Everything so far read one table. Join the orders to their customers and count:
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM orders o JOIN customers c ON c.id = o.customer_id;The full join answers in 84.4 ms from a parallel hash join with two workers: 18,751 buffer hits, all but 666 of them on orders. Sample the orders side:
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) * 100
FROM orders o TABLESAMPLE SYSTEM (1) REPEATABLE (7)
JOIN customers c ON c.id = o.customer_id; Aggregate
Buffers: shared hit=424
-> Hash Join (actual rows=22316.00 loops=1)
Hash Cond: (o.customer_id = c.id)
-> Sample Scan on orders o (actual rows=22316.00 loops=1)
Sampling: system ('1'::real)
Buffers: shared hit=202
-> Hash
Buckets: 65536 Batches: 1 Memory Usage: 2270kB
Buffers: shared hit=222
-> Seq Scan on customers c (actual rows=50001.00 loops=1)
Buffers: shared hit=222
Execution Time: 25.590 msThe sample scan did its part, 202 pages against the full 18,085. The hash over customers still reads all 222 of its pages, because TABLESAMPLE only shrinks the table it sits on, and building the hash is 11.5 ms of the 25.6. The query gets 3.3× faster, not 90×. Most of what remains is the table the sample never touched. The workers are gone too: a sample scan runs in the leader process, so the plan falls back to serial however big the table is.
Forcing the other join shape moves the cost rather than removing it:
SET enable_hashjoin = off;
SET enable_mergejoin = off;The same query then runs 22,316 index-only probes against customers_pkey, 44,636 buffer hits against the sample's own 202, and finishes in 21.6 ms. Every sampled row pays full price for its customer lookup.
A predicate on the far side works normally: adding WHERE c.id <= 500 builds the hash over 500 rows and answers in 1.5 ms. Some questions break outright, though. Every one of the 50,001 customers has orders, but the 1% draw sees:
SELECT count(DISTINCT customer_id) FROM orders TABLESAMPLE SYSTEM (1) REPEATABLE (7); count
-------
18034Multiplying by 100 can't bring back a customer whose orders all landed on losing pages. A sample scales sums and counts of what it kept; anything you have to see to count comes out low.
Sampling the join output directly isn't an option: TABLESAMPLE attaches to one table in the FROM clause, and putting it on a join or a subquery is a syntax error. Sampling both sides is as close as you can get, and it's close to useless:
SELECT count(*)
FROM orders o TABLESAMPLE SYSTEM (1) REPEATABLE (7)
JOIN customers c TABLESAMPLE SYSTEM (1) REPEATABLE (7) ON c.id = o.customer_id; count
-------
288The real join has two million rows. Two independent address draws keep 288 of them, because a sampled order finds its customer only when both page hashes kept the matching pair. When a sampled pair of tables has to stay joinable, hashing the foreign key on both sides is the way to do it.
Which to reach for
SYSTEM for a quick number over a column that has nothing to do with insert order. BERNOULLI when it might, and reading the whole table once is affordable. A hashed key when the subset has to be the same rows tomorrow, or has to keep its foreign keys intact. None of them when you need a filtered subset: the sample is chosen before your WHERE runs, so the filter only trims a draw that was already made.
ANALYZE builds pg_stats from a sample too, with its own block sampler and a seed you don't get to set, which is why the numbers move between runs even when the data hasn't.