[pgsql-jp: 40397] n行のテーブルより任意の1行を取り出すSQL
武田 憲太郎
takeda @ youmind.jp
2010年 9月 14日 (火) 15:17:30 JST
武田と申します。いつも興味深く拝読させていただいております。
とても簡単な所で悩んでしまっているのかもしれないのですが、「n行のテーブル
より無作為に選んだ1行を取り出すSQL」で困っています。
書けないことは無いのですがパフォーマンスを気にしており、更に可能であれば、
クライアント側の処理系を使うこと無くSQLのみで完結させたいと思っています。当
方主に8.3系、8.4系を使っておりますが、全バージョンで同じ挙動となる気がしま
す。
■準備
create table t(i) as select generate_series(1, 100000) ;
alter table t add constraint t_pkey primary key (i);
■目標:「インデックスを使った一件取得」と同等のコストにしたい。
=> select i from t where i = nnnnn ; -- nnnnnは適当な値
Index Scan using t_pkey on t (cost=0.00..8.28 rows=1 width=4)
Index Cond: (i = nnnnn)
■案1:とりあえず「order by random()」
=> select i from t order by random() limit 1 ;
Limit (cost=2143.00..2143.00 rows=1 width=4)
-> Sort (cost=2143.00..2393.00 rows=100000 width=4)
Sort Key: (random())
-> Seq Scan on t (cost=0.00..1643.00 rows=100000 width=4)
・全行スキャンしてそれをrandom()に並べ替えて先頭一件。
・Seq Scanがネック
■案2:orderがダメならoffset
※テーブル行数が不定のためoffset上限値はcount(*)で動的に取る
=> select i from t offset random()*(select count(*) from t) limit 1
Limit (cost=2143.00..2143.00 rows=1 width=4)
-> Sort (cost=2143.00..2393.00 rows=100000 width=4)
Sort Key: (random())
-> Seq Scan on t (cost=0.00..1643.00 rows=100000 width=4)
・外側のクエリのコストは減った。
・しかしサブクエリ(count(*)の取得)で結局全行スキャン
■案3:where指定(本例の場合であればOK、ただ実際はNG)
=> select i from t where i = (select (max(i)*random())::int from t)
※「目標」で言うところのnnnnnの値を動的に生成
Index Scan using t_pkey on t (cost=0.05..8.32 rows=1 width=4)
Index Cond: (i = $1)
InitPlan 2 (returns $1)
-> Result (cost=0.03..0.05 rows=1 width=0)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..0.03 rows=1 width=4)
-> Index Scan Backward using t_pkey on t
(cost=0.00..2780.26 rows=100000 width=4)
Filter: (i IS NOT NULL)
・目標通りのコストに近づいた。今回の例であればこれでOK。
・ただしこれはiの値が1から採番された欠番の無いシーケンスであることが前提。
(実際の実装においてはシーケンス加算後のロールバックなどによってこれは期待で
きない)
私の方では以上が限度でして、「目標」までいかずとも、コストを「インデックス
を用いた一件の取得」に近づけることの出来る方法などご存じの方がいらっしゃいま
したらご教授頂けませんでしょうか。
pgsql-jp メーリングリストの案内