企业官网建设流程全解析

TABLESAMPLE 如何挑选行

https://boringsql.com/posts/tablesample/
2026-10-11
· 9 分钟 ·
Radim Marek

目录

对一张大表的大多数探索性问题只需要一个粗略答案，但如果没有 status 上的索引，即使是“大概有多少已发货订单”也要付出全表扫描的代价。在 200 万行的 orders 表上，Postgres 必须读取每一个 8 kB 页面，全部 18,085 个（141 MB），只是为了统计匹配的行。

EXPLAIN(ANALYZE,BUFFERS)SELECTcount(*)FROMordersWHEREstatus='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是每页一次缓冲区访问，每个页面都已发现位于内存中（阅读 EXPLAIN 输出中的 Buffer 统计会经过这些计数器）。141 MB 的堆大小不算什么。拿一张 1 TB 的表，有 1.34 亿页：一次扫描每次都从磁盘读取所有页面，无论答案多小。

Postgres 通过一个小环形缓冲区读取大于 shared_buffers 四分之一的表。更多内容见缓冲区介绍。至少 Postgres 通过小环形缓冲区读取大表，避免冲刷自己的缓存。尽管如此，磁盘仍然执行完整读取。在一台处理生产流量、与其他一切共享的主服务器上。当你只需要探索性确认“大概多少”或“是否有代表性样本”时，读取整个 TB 太昂贵了。

SQL 标准带来了 TABLESAMPLE 来帮助解决这个问题。它放在 FROM 子句中表名之后，查询的其余部分针对它挑选的页面子集运行。

SELECTid,status,created_at,amountFROMorders TABLESAMPLE SYSTEM(1)REPEATABLE(7)LIMIT5;

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.84

值 (1) 是一个百分比，样本大小不固定：五个不同的种子返回了 18,107 到 20,416 行之间的结果。如果你需要精确的行数，contrib 中的 tsm_system_rows 添加了一个 SYSTEM_ROWS (n) 方法。行按物理顺序返回，这就是为什么这五个是邻居，id 从 429 到 433。而且你计算的任何东西都是在样本上计算的，所以总数必须手动放大，count(*) 和 sum() 乘以 100，而平均值、比率和极值按原样读取。

这是再次统计已发货订单，放大后，带有它的缓冲区数字：

EXPLAIN(ANALYZE,BUFFERS)SELECTcount(*)*100FROMorders TABLESAMPLE SYSTEM(1)WHEREstatus='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 ms

177 个页面而不是 18,085 个，估计值 492,000，而实际是 500,000。对这样的计数来说它站得住。不过，行是按 created_at 顺序每秒插入一行的，所以最旧的行位于表的最开始。问同一个 1% 样本要它：

SELECTmin(created_at)FROMorders TABLESAMPLE SYSTEM(1);

六次运行：

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+00

真实答案是 00:00:01。样本偏差从 44 分钟到将近五小时不等，而第一次和第五次运行精确到秒地一致，这是随机行的样本几乎永远不会发生的。关于 TABLESAMPLE 的一切奇怪之处都来自一个事实：它不挑选行，它挑选地址。

设置

本文的一切都是在 PostgreSQL 18.6 上使用 postgres:18 镜像捕获的。shared_buffers = 1GB，热缓存，所以计时是用于相互比较方法，而不是预测你的。

CREATEEXTENSIONIFNOTEXISTSpageinspect;CREATETABLEorders(idbigintGENERATED ALWAYSASIDENTITYPRIMARYKEY,customer_idbigintNOTNULL,statustextNOTNULL,created_at timestamptzNOTNULL,amountnumeric(10,2)NOTNULL);SELECTsetseed(0.42);INSERTINTOorders(customer_id,status,created_at,amount)SELECT(random()*50000)::bigint+1,CASEWHENi<=1300000THEN'delivered'WHENi<=1800000THEN'shipped'WHENi<=1900000THEN'cancelled'ELSE'pending'END,timestamptz'2024-01-01 00:00:00+00'+i*interval'1 second',round((random()*500)::numeric,2)FROMgenerate_series(1,2000000)ASi;VACUUMANALYZEorders;-- 相同的行，随机物理顺序CREATETABLEorders_shuffledASSELECT*FROMordersORDERBYrandom();VACUUMANALYZEorders_shuffled;CREATETABLEcustomersASSELECTidFROMgenerate_series(1,50001)ASid;ALTERTABLEcustomersADDPRIMARYKEY(id);-- 一个每页都有空间的小副本，用于 pageinspect 步骤CREATETABLEh(LIKEorders INCLUDINGALL)WITH(fillfactor=70);INSERTINTOh OVERRIDING SYSTEMVALUESELECT*FROMordersWHEREid<=1000ORDERBYid;

每一行都有一个地址

页面从 0 开始编号，紧接其头部之后，每个页面保留一个行指针数组，每行一个，从 1 开始编号（《深入 PostgreSQL 的 8KB 页面》会逐字节剖析一个页面）。页号和行指针一起就是行的 ctid：

SELECTctid,id,created_atFROMordersORDERBYidLIMIT3;

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+00

两种内置采样方法都把这个地址的一部分与一个种子一起哈希，并保留哈希值低于 1% 截止值的任何东西。它们只在哈希哪一部分上不同。SYSTEM，在 src/backend/access/tablesample/system.c 中，哈希页号（修剪到重要的行）：

hashinput[0]=nextblock;/* page */hashinput[1]=sampler->seed;hash=DatumGetUInt32(hash_any((constunsignedchar*)hashinput,(int)sizeof(hashinput)));if(hash<sampler->cutoff)/* 保留整个页面 */

BERNOULLI，在 bernoulli.c 中，哈希页和行指针号：

hashinput[0]=blockno;/* page */hashinput[1]=tupoffset;/* line pointer */hashinput[2]=sampler->seed;

所以 SYSTEM 每页抛一枚硬币，并取获胜页面上的每一行。BERNOULLI 每个行指针抛一枚硬币，这意味着它必须打开每个页面才能看到有多少行指针。这就是整个性能故事：在这张表上，SYSTEM 1.2 毫秒回答，BERNOULLI 10.4 毫秒。

页面成捆出现

这里一个页面容纳约 107 行，而由于行按时间写入，一个页面就是 107 个连续秒的订单。按 ctid 的页面部分对 SYSTEM 样本分组，这些捆就在那里：

SELECT(ctid::text::point)[0]::intASpage,count(*)ASrows,min(created_at)::timeASfirst,max(created_at)::timeASlastFROMorders TABLESAMPLE SYSTEM(1)REPEATABLE(7)GROUPBY1ORDERBY1LIMIT4;

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:22

样本不是 20,000 个分散的秒。它是时间线上约 200 条短条带，而 min() 返回哈希首先保留的那条条带的起点。上面的第一次和第五次运行保留了同一个第一页。

打乱表，问题就消失了。orders_shuffled 持有相同的行，但以 ORDER BY random() 顺序写入，所以每个页面都是来自三周各处的几秒钟，同一个查询每次都能落在真相几分钟之内：

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+00

在原始的、按时间排序的表上，BERNOULLI 做得一样好，因为每个行指针一枚硬币从不把任何东西捆在一起。

没有人为了最小值而采样，但这些捆以同样的方式伤害平均值。在 200 个种子上，来自 SYSTEM 样本的 avg(created_at) 的游走幅度是来自 BERNOULLI 的同一平均值的十倍。一旦你按页面思考，原因就很明显：来自一个页面的 107 行告诉你的关于时间线的信息，大约与来自该页面的一行一样多，所以 20,000 行的 SYSTEM 样本大致只有 200 行的精度。与行所在位置无关的列，比如随机 amount，在两种方法下平均值相同。时间列、不断增长的 id，以及任何按批次加载的东西，都是它重要的列。

REPEATABLE 记住地址

REPEATABLE (42) 固定种子。同样的种子，同样的哈希，同样的地址。这些地址是否仍然持有相同的行，取决于其间表发生了什么，而地址模型预测了这张表的每一行。每次运行用种子 42 捕获一个样本，改变表，再次捕获，并统计第一个样本中有多少行仍然在它里面：

追加创建新地址，删除清空旧地址；没有幸存的行移动，所以什么都没变。VACUUM 将死行指针标记为未使用，并压缩每个页面内的元组数据，但它从不重新编号仍指向活行的行指针（《页面级 VACUUM》走过每个阶段）。VACUUM FULL 和 CLUSTER 构建表的新副本，每一行都得到一个新地址，两个样本实际上都被重新抽取。

UPDATE 是有趣的一行。Postgres 从不原地覆盖一行：更新写入一个新版本，当页面有空间且没有索引列改变时，它把该版本放在同一页面上，不碰任何索引。那是一次 HOT 更新（《Postgres 中的 HOT 更新》），这里 99% 的更新都是。同一页面意味着 SYSTEM 不会注意到。但新版本得到一个新的行指针，pageinspect 为 h 的一行显示了它：

SELECTctidFROMhWHEREid=3;-- (0,3)UPDATEhSETamount=amount+1WHEREid=3;SELECTctidFROMhWHEREid=3;-- (0,76)SELECTlp,lp_flags,lp_off,t_ctidFROMheap_page_items(get_raw_page('h',0))WHERElpIN(3,76);

lp | lp_flags | lp_off | t_ctid ----+----------+--------+-------- 3 | 2 | 76 | 76 | 1 | 2792 | (0,76)

行指针 3 现在是 LP_REDIRECT（lp_flags = 2），一个路标，说“移动到 76”，这样指向 (0,3) 的索引项仍然能找到该行。BERNOULLI 不读路标。它哈希行指针 76，这是一次全新的 1% 抛硬币，所以几乎每一行被更新的行都从样本中掉出，而另一个 1% 掉入。

改为哈希键

当人们固定种子时，他们通常想要一个从一天到下一天保持不变的开发或测试子集。地址不能承诺这一点，因为更新和重写会移动行。主键不会移动，所以哈希键：

SELECT*FROMordersWHERE(hashint8extended(id,42)&1023)<10;

hashint8extended 用一个种子哈希一个 bigint；保留 1,024 个桶中的 10 个返回 0.98% 的行。经过同样的变化，它在每一步都保留了每一个幸存的行，在删除、UPDATE、VACUUM FULL 和 CLUSTER 之后包括 9,916 of 9,916。它也没有捆：它在 avg(created_at) 上的 200 种子离散度与 BERNOULLI 匹配。

键也可以是外键。对 customers 用 hashint8extended(id, 42) 采样，对 orders 用 hashint8extended(customer_id, 42) 采样，你会得到 484 个客户及其全部 19,432 个订单。同样两张表用 SYSTEM (1) REPEATABLE (42) 采样，会让 17,595 个被采样订单中的 17,517 个指向一个不在样本中的客户。

代价是一次全表扫描，因为哈希必须为每一行计算：串行 44.4 毫秒，两个并行 worker 21.5 毫秒。对于你经常拉取的子集，在 (hashint8extended(id, 42) & 1023) 上的表达式索引（这里 14 MB）把它变成 8.5 毫秒的位图扫描，种子固定在索引定义中。

连接不会缩小

到目前为止一切都只读一张表。把 orders 连接到它们的 customers 并计数：

EXPLAIN(ANALYZE,BUFFERS)SELECTcount(*)FROMorders oJOINcustomers cONc.id=o.customer_id;

完整连接在 84.4 毫秒内用两个 worker 的并行哈希连接回答：18,751 缓冲区命中，除了 666 个之外全在 orders 上。采样 orders 一侧：

EXPLAIN(ANALYZE,BUFFERS)SELECTcount(*)*100FROMorders o TABLESAMPLE SYSTEM(1)REPEATABLE(7)JOINcustomers cONc.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 ms

样本扫描完成了它那一份，202 个页面对完整的 18,085 个。customers 上的哈希仍然读取它全部 222 个页面，因为 TABLESAMPLE 只缩小它所在的表，而构建哈希是 25.6 毫秒中的 11.5 毫秒。查询快了 3.3 倍，而不是 90 倍。剩下的大部分是样本从未碰过的表。worker 也没了：样本扫描在 leader 进程中运行，所以无论表多大，计划都退回串行。

强制另一种连接形状只是移动成本而不是移除它：

SETenable_hashjoin=off;SETenable_mergejoin=off;

同一个查询然后对 customers_pkey 运行 22,316 次 index-only 探测，44,636 缓冲区命中，而对样本自己的 202 个，并在 21.6 毫秒内完成。每一个被采样的行都为它的客户查找付出全价。

远端的谓词正常工作：添加 WHERE c.id <= 500 在 500 行上构建哈希，并在 1.5 毫秒内回答。不过，有些问题会直接坏掉。全部 50,001 个客户都有订单，但 1% 的抽取看到：

SELECTcount(DISTINCTcustomer_id)FROMorders TABLESAMPLE SYSTEM(1)REPEATABLE(7);

count ------- 18034

乘以 100 无法带回一个其订单全部落在输掉的页面上的客户。样本放大它保留的东西的总和与计数；任何你必须看到才能计数的东西都会偏低。

直接采样连接输出不是一个选项：TABLESAMPLE 附着在 FROM 子句中的一张表上，把它放在连接或子查询上是语法错误。采样两侧是你最接近的做法，而它几乎没用：

SELECTcount(*)FROMorders o TABLESAMPLE SYSTEM(1)REPEATABLE(7)JOINcustomers c TABLESAMPLE SYSTEM(1)REPEATABLE(7)ONc.id=o.customer_id;

count ------- 288

真正的连接有 200 万行。两次独立的地址抽取只保留其中 288 行，因为一个被采样的订单只有在两个页面哈希都保留匹配对时才能找到它的客户。当一对被采样的表必须保持可连接时，在两侧哈希外键是做法。

该用哪个

SYSTEM 用于对一个与插入顺序无关的列快速取数。BERNOULLI 当它可能有关时，并且完整读取一次表是可承受的。哈希键当子集明天必须还是相同的行，或者必须保持其外键完整时。当你需要过滤子集时，一个都不用：样本在你的 WHERE 运行之前就被选中，所以过滤器只是修剪一个已经做出的抽取。

ANALYZE 也从样本构建 pg_stats，用它自己的块采样器和一个你不能设置的种子，这就是为什么即使数据没有变化，数字也会在运行之间移动。