欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Postgresql的数据抽样

程序员文章站 2022-06-25 10:10:31
...

数据抽样(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;

Postgresql的数据抽样

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;

Postgresql的数据抽样

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

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

Postgresql的数据抽样

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

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

Postgresql的数据抽样

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

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

Postgresql的数据抽样

Postgresql的数据抽样

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

Postgresql的数据抽样

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

BERNOULLI抽样方式 

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

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

Postgresql的数据抽样

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

Postgresql的数据抽样

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

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

Postgresql的数据抽样

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

相关标签: postgresql