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