PostgreSQL - 数据抽样 (RANDOM vs. TABLESAMPLE)

首先我们来看看数据总量: (共计 3百万 以上)

(encore@localhost) [pg10] > select COUNT(*) from big_data_table;
  count
---------
 3212557
(1 row)

Time: 242.859 ms

1. 使用 RANDOM() 函数,这也是我们最常用的方式随机查询函数

(encore@localhost) [pg10] > EXPLAIN ANALYZE  select * from big_data_table ORDER BY random() limit 1;
                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=492124.03..492124.03 rows=1 width=1086) (actual time=2485.934..2485.934 rows=1 loops=1)
   ->  Sort  (cost=492124.03..500240.32 rows=3246516 width=1086) (actual time=2485.932..2485.932 rows=1 loops=1)
         Sort Key: (random())
         Sort Method: top-N heapsort  Memory: 26kB
         ->  Seq Scan on big_data_table  (cost=0.00..475891.45 rows=3246516 width=1086) (actual time=0.055..1477.629 rows=3212557 loops=1)
 Planning time: 0.281 ms
 Execution time: 2486.029 ms
(7 rows)

Time: 2597.360 ms (00:02.597)

从结下来我们分析下 ANALYZE 语句,这条查询实际上进行了全表扫描,一共花费2秒多,  随着数据量上去后这个时间也会延长。故 RANDOM() 的可能会是比较差的

2.  使用 TABLESAMPLE 函数来进行抽样查询 - Syntax

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

NOTE:  argument is percentage of rows

2.1 sampling_method - SYSTEM

  1. Block level sampling
  2. Very fast 
  3. Non-Independent rows

      接下来,我们使用 TABLESAMPLE SYSTEM 来进行抽样查询

      (encore@localhost) [pg10] > EXPLAIN ANALYZE  select * from big_data_table TABLESAMPLE SYSTEM (0.001);
                                                      QUERY PLAN
      -----------------------------------------------------------------------------------------------------------
       Sample Scan on big_data_table  (cost=0.00..16.32 rows=32 width=1078) (actual time=0.408..5.508 rows=39 loops=1)
         Sampling: system ('0.001'::real)
       Planning time: 0.199 ms
       Execution time: 5.558 ms
      (4 rows)
      
      Time: 14.674 ms

      执行时间14毫秒, 所以从时间来看它有绝对优势,接来下,我们来看看结果数据:

      (encore@localhost) [pg10] > select ctid, id from entities TABLESAMPLE SYSTEM (0.001);
          ctid     |   id
      -------------+---------
       (98910,1)   | 1732517
       (98910,2)   | 1327847
       (98910,3)   |  496001
       (98910,4)   | 3081722
       (98910,5)   |  834619
       (98910,6)   | 3081727
       (98910,7)   |  331786
       (98910,8)   |  293772
       (98910,9)   |  392600
       (98910,10)  |  304536
       (98910,11)  | 1346982
       (423306,1)  | 1811199
       (423306,2)  |   82218
       (423306,3)  |  737029
       (423306,4)  |  940963
       (423306,5)  | 2365740
       (423306,6)  | 1822060
       (423306,7)  | 2365700
       (423306,8)  | 1138962
       (423306,9)  | 2550308
       (423306,10) |  834070
       (423306,11) | 1553692
       (423306,12) | 1221598
       (423306,13) | 2532790
       (423306,14) | 2972756
       (423306,15) | 2530052
       (423306,16) | 2478989
       (423306,17) | 2373148
       (423306,18) | 2393660
      (29 rows)
      
      Time: 8.857 ms

      备注: ctid是row数据中的隐藏数据,存贮着row数据存贮的具体位置. 括号中的第一位表示逻辑数据块编号,第二位表示逻辑块上的数据逻辑编号。

      我们看到这些数据的逻辑数据块编号是连续的,也就意味它不是全表扫描的,而是根据随机查询几个数据块上的rows.

      2.2 sampling_methodBERNOULLI

      • Row Level sampling
      • Slower than SYSTEM
      • Independent row (uniformly random)

      接下来,我们使用 TABLESAMPLE BERNOULLI 来进行抽样查询

      (encore@localhost) [pg10] > EXPLAIN ANALYZE  select * from from TABLESAMPLE BERNOULLI (0.001);
                                                          QUERY PLAN
      ------------------------------------------------------------------------------------------------------------------
       Sample Scan on entities  (cost=0.00..435310.32 rows=32 width=1078) (actual time=13.693..600.575 rows=41 loops=1)
         Sampling: bernoulli ('0.001'::real)
       Planning time: 0.201 ms
       Execution time: 600.631 ms
      (4 rows)
      
      Time: 619.567 ms

      接下来,执行时间 619 毫秒, 所以从时间来看没有绝对优势,不如system 块,然而比起 RANDOM 还是有绝对优势的。 接来下,我们来看看结果数据:

      (sonar@192.168.1.98:5432) [sonar] > select ctid, id from entities TABLESAMPLE BERNOULLI (0.001);
          ctid     |   id
      -------------+---------
       (24776,5)   | 2183626
       (55560,2)   | 1242007
       (81704,3)   | 2183725
       (90983,6)   | 2503323
       (96980,6)   | 2740003
       (102041,4)  |  988492
       (130142,34) | 2772186
       (136455,6)  |  386552
       (146188,9)  |  760693
       (149244,6)  | 2221747
       (163565,1)  |  601601
       (169014,19) | 1533355
       (202571,8)  |  744968
       (203485,6)  | 2726013
       (313556,19) | 2346971
       (323214,1)  |  246043
       (336993,7)  | 1141978
       (347225,6)  |   95636
       (370711,2)  | 3013547
       (375456,2)  | 1976852
       (382090,2)  | 1224492
       (383632,15) | 2310430
       (391271,8)  | 2110210
       (411211,1)  |  587877
       (415579,14) |  923595
       (421318,2)  | 1126526
       (421893,3)  | 2745767
       (426502,19) |  606706
      (28 rows)
      
      Time: 641.440 ms

      我们看到这些数据它们的逻辑数据块编号不是连续的,也就意味它是从多个数据块中查询得到,所以它实际场景中可能更加使用,真正做到随机性. 而且性能比 RANDOM 要好很好.

      最后, 在随机数据抽样中,我们该如何选择呢? 这个可能要根据你的数据来决定,使用的方法.

      • 比如logs中随机抽样,可能我们使用TABLESAMPLE SYSTEM,是比较好的。
      • 在数据其中采集的表中,比如,大批量同类型数据写入的表中,可能使用TABLESAMPLE BERNOULLI 比较合适.