Posted at

PostgreSQL 9.5のTABLESAMPLE句

More than 3 years have passed since last update.

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句で絞り込む」という順で実行される。