TABLESAMPLEとは
大きなテーブルからランダムに一部の行だけを抽出したい場合、ORDER BY random() LIMITがよく使われるが、全行をスキャンしてソートするため大きなテーブルでは非常に遅くなる。TABLESAMPLEはテーブルの一部だけを読み取ってサンプリングする句であり、SELECTのFROM句に対して指定する。
SELECT * FROM テーブル名 TABLESAMPLE SYSTEM (パーセンテージ);
500万件のaccountsテーブルに対して1%のサンプリングを行うと、以下のように実行できる。
SELECT count(*) FROM accounts TABLESAMPLE SYSTEM (1);
count
-------
54008
(1 row)
5000000件の1%である50000件前後の結果が返る。
SYSTEMとBERNOULLIの違い
TABLESAMPLEにはサンプリング方式としてSYSTEMとBERNOULLIの2種類を指定できる。
SYSTEM: テーブルのページ（ブロック）単位でランダムに選択し、選ばれたページ内の行はすべて含めるBERNOULLI: 行単位で独立にランダム選択する
EXPLAIN ANALYZEで実行計画を比較すると、コストの違いが分かる。
EXPLAIN ANALYZE SELECT * FROM accounts TABLESAMPLE SYSTEM (1);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Sample Scan on accounts (cost=0.00..1654.18 rows=38218 width=40) (actual time=0.054..62.239 rows=46472 loops=1)
Sampling: system ('1'::real)
Planning Time: 6.983 ms
Execution Time: 117.317 ms
(4 rows)
EXPLAIN ANALYZE SELECT * FROM accounts TABLESAMPLE BERNOULLI (1);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Sample Scan on accounts (cost=0.00..32230.18 rows=38218 width=40) (actual time=0.058..66.813 rows=49733 loops=1)
Sampling: bernoulli ('1'::real)
Planning Time: 0.132 ms
Execution Time: 68.227 ms
(4 rows)
SYSTEMはページ単位で選択するため必要なページしか読み込まず、コストが1654.18とBERNOULLIの32230.18より大幅に小さい。BERNOULLIは全ページを読みながら行ごとに抽選するため、SYSTEMよりコストが高くなる。テーブルが大きいほどSYSTEMの方が高速だが、後述するようにSYSTEMにはサンプリングの偏りがある。
ORDER BY RANDOM()との速度比較
同じ5万件程度の抽出をORDER BY random() LIMITで行うと、全行のソートが発生し大幅に遅くなる。
EXPLAIN ANALYZE SELECT * FROM accounts ORDER BY random() LIMIT 50000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Limit (cost=397010.30..397135.30 rows=50000 width=48) (actual time=2210.354..2216.768 rows=50000 loops=1)
-> Sort (cost=397010.30..406564.70 rows=3821760 width=48) (actual time=2208.552..2212.423 rows=50000 loops=1)
Sort Key: (random())
Sort Method: external merge Disk: 205552kB
-> Seq Scan on accounts (cost=0.00..79620.00 rows=3821760 width=48) (actual time=0.064..510.336 rows=5000000 loops=1)
Planning Time: 0.181 ms
Execution Time: 2239.901 ms
(11 rows)
ORDER BY random()は5000000件すべてを読み込んだうえでソートするため、ディスクを使った外部マージソート（Sort Method: external merge）が発生し、実行時間は2239.901msかかっている。TABLESAMPLE SYSTEMの117.317ms、BERNOULLIの68.227msと比べると、テーブル全体のスキャンとソートを避けられる分TABLESAMPLEの方が高速である。
SYSTEMの偏りに注意する
SYSTEMはページ単位でサンプリングするため、同じページに格納された行がまとまって抽出される。挿入順に近い値が同じページに収まっているテーブルでは、抽出結果に偏りが生じる。
SELECT id FROM accounts TABLESAMPLE SYSTEM (0.001) REPEATABLE (42) LIMIT 5;
id
---------
1865961
1865962
1865963
1865964
1865965
(5 rows)
抽出されたidが1865961から1865965まで連続しており、ランダムに散らばっていない。同一ページ内の行がまとめて選ばれたためである。行単位で独立にランダム抽出したい場合はBERNOULLIを使う。統計目的でおおまかな傾向を掴みたいだけであればSYSTEMの速度が有利だが、行ごとの独立性が必要な用途ではBERNOULLIを選ぶ。
REPEATABLEで再現可能にする
TABLESAMPLEは指定しなければ実行のたびに異なる行が抽出されるが、REPEATABLE句でシード値を指定すると同じ抽出結果を再現できる。上記の例でもREPEATABLE (42)を指定しており、同じシードで2回実行すると両方とも1865961から1865965の同じidが返る。動作確認やデバッグのために同じサンプルを繰り返し取得したい場合はREPEATABLEにシード値を固定するとよい。シード値を変えれば異なるサンプルが得られる。ただし同じシードでも、INSERTやUPDATE、VACUUM FULLなどでテーブルの物理的な行配置が変わると抽出結果も変わる点に注意する。
パーセンテージと件数の関係
TABLESAMPLEに指定するのはサンプリング対象の割合（パーセンテージ）であり、抽出したい件数を直接指定する句ではない。件数を指定したい場合は、テーブルの推定件数から逆算してパーセンテージを求める。
SELECT n_live_tup FROM pg_stat_user_tables WHERE relname = 'accounts';
n_live_tup
------------
5000000
(1 row)
5000000件のテーブルから1000件を抽出したい場合、1000 / 5000000 * 100 = 0.02をパーセンテージとして指定する。
SELECT count(*) FROM accounts TABLESAMPLE SYSTEM (0.02);
count
-------
1074
(1 row)
n_live_tupは正確な件数ではなく統計情報に基づく推定値であるため、抽出される件数も指定した件数ぴったりにはならない。件数を厳密に一致させたい場合はTABLESAMPLEで多めに抽出したうえでLIMITを併用する。
