Postgresql的数据抽样

数据抽样（TABLESAMPLE）在数据处理方面经常用到，特别是当表数据量比较大时，随机查询表中一定数量记录的操作很常见，PostgreSQL早在9.5版时就已经提供了 TABLESAMPLE数据抽样功能，9.5版前通常通过ORDER BY random（）方式实现数据抽样，这种方式虽然在功能上满足随机返回指定行数据，但性能很低。

create table test01(id integer, val char(1000)); 

insert into test01 values(generate_series(1,500000),repeat( chr(int4(random()*26)+65),1000));


\timing
 
EXPLAIN ANALYZE SELECT * FROM test01 ORDER BY random() LIMIT 1;

9.5版本以后PostgreSQL支持TABLESAMPLE数据抽样，语法如下所示： 

SELECT ... FROM table_name TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ] 

sampling_method指抽样方法，主要有两种：SYSTEM和 BERNOULLI

argument指抽样百分比。 

注意 explain analyze命令表示实际执行这条SQL，同时显示SQL执行计划和执行时间，Planning time表示SQL语句解析生成执行计划的时间，Execution time表示SQL的实际执行时间。

SYSTEM抽样方式

SYSTEM抽样方式为随机抽取表上数据块上的数据，理论 上被抽样表的每个数据块被检索的概率是一样的，SYSTEM抽样方式基于数据块级别，后接抽样参数，被选中的块上的所有数据将被检索，下面使用示例进行说明。

CREATE TABLE test_sample(id int4,message text,create_time timestamp(6) without time zone default clock_timestamp()); 

INSERT INTO test_sample(id,message) SELECT n, md5(random()::text) FROM generate_series(1,1500000) n; 


SELECT * FROM test_sample LIMIT 1;

抽样因子设置成0.01，意味着返回1500000×0.01%=150条 记录，执行如下SQL：

EXPLAIN ANALYZE SELECT * FROM test_sample TABLESAMPLE SYSTEM(0.01);

以上执行计划主要有两点，一方面进行了Sample Scan扫描（抽样方式为SYSTEM），执行时间为0.178毫秒，性能较好，另一方面优化器预计访问150条记录，实际返回107条，为什么会返回107条记录呢？接着查看表占用的数据块数量，如下所示：

SELECT relname,relpages FROM pg_class WHERE relname='test_sample';

表test_sample物理上占用14019个数据块，也就是说每个数据块存储1000000/14019=107条记录。查看抽样数据的ctid，如下所示：

SELECT ctid,* FROM test_sample TABLESAMPLE SYSTEM(0.01); 

ctid是表的隐藏列，括号里的第一位表示逻辑数据块编号，第二位表示逻辑块上的数据的逻辑编号，从以上看出，这107条记录都存储在逻辑编号为5640的数据块上，也就是说抽样查询返回了一个数据块上的所有数据，抽样因子固定为0.01，多次执行以下查询，如下所示：

这也验证了SYSTEM抽样方式返回的数据以数据块为单位，被抽样的块上的所有数据被检索。

BERNOULLI抽样方式 

BERNOULLI抽样方式随机抽取表的数据行，并返回指定百分比数据，BERNOULLI抽样方式基于数据行级别，理论上被抽样表的每行记录被检索的概率是一样的，因此BERNOULLI抽样方式抽取的数据相比SYSTEM抽样方式具有更好的随机性，但性能上相比SYSTEM抽样方式低很多，设置抽样方式为BERNOULLI，抽样因子为0.01。

EXPLAIN ANALYZE SELECT * FROM test_sample TABLESAMPLE BERNOULLI (0.01); 

从以上执行计划看出进行了Sample Scan扫描（抽样方式 为BERNOULLI），执行计划预计返回150条记录，实际返回147条，从返回的记录数来看，非常接近150条 （1000000×0.01%），但执行时间却要24.773毫秒，性能相比SYSTEM抽样方式差了很多。多次执行以下查询，查看返回记录数的变化，如下所示：

从以上看出，BERNOULLI抽样方式返回的数据量非常接近抽样数据的百分比，而SYSTEM抽样方式数据返回以数据块为单位，被抽样的块上的所有数据都被返回，因此SYSTEM抽样方式返回的数据量偏差较大。
由于BERNOULLI抽样基于数据行级别，猜想返回的数据应该位于不同的数据块上，通过查询表的ctid进行验证，如下所示：

SELECT ctid,id,message FROM test_sample TABLESAMPLE BERNOULLI(0.01) lIMIT 3;

从以上三条记录的ctid信息看出，三条数据分别位于数据块10、93、285上，因此BERNOULLI抽样方式随机性相比SYSTEM抽样方式更好。SYSTEM 抽样方式基于数据块级别，随机抽取表数据块上的记录，因此 这种方式抽取的记录的随机性不是很好，但返回的数据以数据块为单位，抽样性能很高，适用于抽样效率优先的场景，例如抽样大小为上百GB的日志表；而BERNOULLI抽样方式基于数 据行，相比SYSTEM抽样方式所抽样的数据随机性更好，但性能相比SYSTEM差很多，适用于抽样随机性优先的场景。