PostgreSQL 9.5のTABLESAMPLE句

PostgreSQL9.5に入る新機能TABLESAMPLE句について。
対象はPostgreSQL9.5 Alpha2。
※Alpha1とは挙動が異なるので注意

概要

• サンプリングして結果セットを制限することができる
• TABLESAMPLE句はSQL標準
• SELECTでのみ使用可能(UPDATE/DELETEでは使えない)

シンタックス

TABLESAMPLE sampling_method (percentage) [ REPEATABLE (seed) ];

• sampling_method : SYSTEM or BERNOULLIを指定
• percentage : テーブルの何％取得するか
• seed : サンプリングセットの選択

SYSTEM : ブロック単位でサンプリング
BERNOULLI : タプル単位でサンプリング

注意点

• タプル(またはブロック)それぞれが選択される確率(percentage)を持つ
  • 1000行の10%を指定してもきっちり100行返るとは限らない
• WHERE句と組み合わせると、「サンプリング→WHERE句条件の評価」の順に実行される
• デッドタプルもサンプリング対象に含まれる(←たぶん)

使用例

データ準備

データ件数	ブロック数	1ブロック当たりのタプル数
10000件	45	約222件

=# CREATE TABLE hoge AS SELECT generate_series(1,10000) as col;
INSERT 0 10000

=# SELECT pg_relation_size('hoge') / current_setting('block_size')::int;
 relpages
----------
       45
(1 row)

=# SELECT 10000 / 45 as tuples_per_block; 
 tuples_per_block
-----------------
             222

単純な例

SYSTEM

ブロック単位でサンプリングするので、今回の例だと連番で取得できる。

=# SELECT * FROM hoge TABLESAMPLE SYSTEM(10);
  col
-------
  3165
  3166
  3167
  3168
  3169
  3170
  3171
  3172
  3173
  3174
  3175
  3176
  3177
:
(以下略)

BERNOULLI

タプル単位でサンプリングするので、取得できる値はバラバラ。

=# SELECT * FROM hoge TABLESAMPLE BERNOULLI(10);
 col
------
    2
   15
   43
   59
   67
   72
   74
   79
   83
   91
  102
  111
  119
  126
  159
:
(以下略)

取得できる件数について

関数準備

1SQL内で複数回実行するために関数を定義

• SYSTEM用

=# CREATE OR REPLACE FUNCTION system_sampling(in float, out bigint) AS 
$$
SELECT count(*) FROM hoge TABLESAMPLE SYSTEM($1);
$$
LANGUAGE sql;

• BERNOULLI用

=# CREATE OR REPLACE FUNCTION bernoulli_sampling(in float, out bigint) AS 
$$
SELECT count(*) FROM hoge TABLESAMPLE BERNOULLI($1);
$$
LANGUAGE sql;

SYSTEM

ブロック単位でランダムにサンプリングする。

• テーブルの10%のブロック(45*0.1=4.5ブロック)を指定 (10回施行)

=# SELECT system_sampling(10) FROM generate_series(1,10);
 system_sampling
-----------------
             452
             226
             226
             226
             226
             226
             226
               0
             226
             226
(10 rows)

実際には226行/ブロック入っているようで、0件(0ブロック)だったり、1864件(8ブロック)選ばれる。

BELNOULLI(ベルヌーイ)

タプル単位でランダムにサンプリングする。

• テーブルの10%のタプル(10000*0.1=1000タプル)を指定 (10回施行)

=# SELECT bernoulli_sampling(10) FROM generate_series(1,10);
 bernoulli_sampling
--------------------
                965
               1000
               1012
               1016
                971
               1047
               1031
                982
                983
               1077
(10 rows)

ぴったり1000行だったり、多少前後する時もある。
BERNOULLIでは指定したパーセンテージに近い行数が返ってくるので、SYSTEMよりも直観的。

REPEATABLE句

• REPEATABLE句を使わないと結果は毎回バラバラ

=# SELECT * FROM hoge TABLESAMPLE BERNOULLI(0.1);
 col
------
 2598
 3077
 3835
 4414
 4996
 5803
 7001
 7384
 8246
 8723
(10 rows)

=# SELECT * FROM hoge TABLESAMPLE BERNOULLI(0.1);
 col
------
  360
  703
 1246
 2476
 6054
 9234
(6 rows)

• REPEATABLE句でseedを指定すれば、サンプリング結果を指定できる

=# SELECT * FROM hoge TABLESAMPLE BERNOULLI(0.1) REPEATABLE(3);
 col
------
   47
 2124
 2413
 2510
 2579
 2628
 8194
 9054
 9553
 9923
(10 rows)

=# SELECT * FROM hoge TABLESAMPLE BERNOULLI(0.1) REPEATABLE(3);
 col
------
   47
 2124
 2413
 2510
 2579
 2628
 8194
 9054
 9553
 9923
(10 rows)

WHERE句との併用

=# EXPLAIN ANALYZE SELECT * FROM hoge TABLESAMPLE BERNOULLI(10) WHERE col < 4000;
                                              QUERY PLAN
------------------------------------------------------------------------------------------------------
 Sample Scan on hoge  (cost=0.00..59.35 rows=459 width=4) (actual time=0.018..1.626 rows=397 loops=1)
   Sampling: bernoulli ('10'::real)
   Filter: (col < 4000)
   Rows Removed by Filter: 610
 Planning time: 0.098 ms
 Execution time: 1.827 ms
(6 rows)

Sample Scanという新しいスキャン方法が選択され、「サンプリング→サンプリング結果をWHERE句で絞り込む」という順で実行される。