Postgresql数据抽样方法

##概述

数据抽样在数据处理方面经常用到，特别是当表数据量比较大时，随机查询表中一定数量记录的操作很常见，通常通过order by random()方式实现数据抽样，这种方式虽然在功能上满足随机返回指定行数据， 但性能很低，如下所示：

select * from test order by random() limit 1; 

**order by random()方式适合取固定数量的抽样场景。**表test数据量为100万， 从100万随机取一条，上述sql的执行时间为Time: 180.445 ms，这种方法进行了全表扫描和排序，效率非常低，当表数据量更大时，性能几乎无法接受。

以后PostgreSQL支持tablesample数据抽样，语法如下：

select from table name 
tablesample sampliog_method (argument[,...))[repeatable(seed)) 

sampling_ method指抽样方法， 主要有两种：system和bernoulli，

接下来详细介绍这两种抽样方式， argument指抽样百分比。

system抽样方式

system抽样方式为随机抽取表上数据块上的数据，理论上被抽样表的每个数据块被检索的概率是一样的，system抽样方式基于数据块级别，后接抽样参数，被选中的块上的所有数据将被检索，例如。
创建test测试表，并插入175万条数据，如下所示：

抽样因子设置成0. 01， 意味着返回175万 × 0.01%=175条记录，执行如下sql:

执行计划如下所示：

explain analyze select * from test tablesample system(0.01); 

                                             QUERY PLAN
------------------------------------------------------------------------------------------------------

 Sample Scan on test  (cost=0.00..5.75 rows=175 width=53) (actual time=0.012..0.058 rows=389 loops=1)
   Sampling: system ('0.01'::real)
 Planning time: 0.031 ms
 Execution time: 0.078 ms
(4 rows)

以上执行计划主要有两点，一方面进行了samplescan扫描（抽样方式为system),执行时间为 0.029 ms，性能较好，另一方面优化器预计访问96条记录，实际返回389，为什么会返回175条记录呢？接着查看表占用的数据块数量，如下所示：

select relname,relpages from pg_class where relname='test'; 
 relname | relpages 
---------+----------
 test    |     4919
(1 row)

Time: 0.366 ms

表test物理上占用4919个数据块，也就是说每个数据块存储 175万/4919=389条记录。
查看抽样数据的ctid，如下所示：

select ctid,* from test tablesample system(0.01); 

 ctid    |  id  | testid | personid |              imgurl              |          rectime           
-----------+------+--------+----------+----------------------------------+----------------------------
 (936,1)   | 2086 |   6877 |     3399 | c9cf294ec186bd458b50124badd6f91e | 2021-03-30 22:12:45.743534
 (936,2)   | 3206 |   8911 |     4121 | 42934ee86a0f73a7618f151e4d5fa502 | 2021-03-30 22:12:45.744686
 (936,3)   | 3429 |    707 |     8722 | e2d9bb0fbdac00940115de31bb85b0d7 | 2021-03-30 22:12:45.745712
 (936,4)   | 1734 |   9222 |     6850 | e61542b9e7d0fcae6b150275c4caf251 | 2021-03-30 22:12:45.746354
 (936,5)   | 2794 |   4940 |     5248 | 9b08f63f0b9fbc869888a927a74ea1cd | 2021-03-30 22:12:45.747393
...

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

select count(*) from test tablesample system(0.01); 
 count 
-------
   389
(1 row)

再次查询发现返回的记录为214或107，由于一个数据块存储107条记录，因此查询结果有时返回了两个数块上的所有数据，这是因为抽样因子设置成0. 01，意味着返回1750000 × 0.01%=175条记录， 150条记录需要两个数据块存储，这也验证了system抽样方式返回的数据以数据块为单位，被抽样的块上的所有数据被检索。

再次查询发现返回的记录为389或0，由于一个数据块存储107条记录，因此查询结果有时返回了两个数块上的所有数据，这是因为抽样因子设置成0. 01，意味着返回1750000 × 0.01 %=175条记录，175条记录需要两个数据块存储，这也验证了system抽样方式返回的数据以数据块为单位，被抽样的块上的所有数据被检索。

时而为0，不靠谱

bernoulli抽样方式

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

explain  analyze  select *  from  test tablesample  bernoulli(0.01); 
query  plan 
 Sample Scan on test  (cost=0.00..4920.75 rows=175 width=53) (actual time=0.100..19.999 rows=185 loops=1)
   Sampling: bernoulli ('0.01'::real)
 Planning time: 0.031 ms
 Execution time: 20.034 ms
(4 rows)

Time: 21.139 ms

从以上执行计划看出进行了samplescan扫描（抽样方式为bernoulli）， 执行计划预计返回150条记录，实际返回152条， 从返回的记录数来看，非常接近175条
( 17500 000×0.01%），但执行时间却要22. 569毫秒，性能相比system抽样方式0.366毫秒差了57倍。
多次执行以下查询，查看返回记录数的变化，如下所示：

select ctid,* from test tablesample  bernoulli(0.01)  limit  3; 
  ctid   |  id  | testid | personid |              imgurl              |          rectime           
----------+------+--------+----------+----------------------------------+----------------------------
 (23,53)  | 6684 |   7804 |     6901 | b4201e76e54968516862e174b6a2efea | 2021-03-30 22:12:39.074777
 (24,140) | 9760 |   8542 |     9916 | 9597f35e5689d9aa030bbf563f1250de | 2021-03-30 22:12:39.071712
 (48,26)  | 1414 |   3728 |      895 | f0a9d97f2f71dae6b72c0d9e06bb6ead | 2021-03-30 22:12:39.057819
(3 rows)

Time: 0.807 ms

从以上三条记录的ctid信息看出， 三条数据分别位于数据块23、 24、 48上，因此，bernoulli抽样方式随机性相比system抽样方式更好。

https://en.wikipedia.org/wiki/Bernoulli_distribution