[pgsql-jp: 40398] Re: n行のテーブルより任意の1行を取り出すSQL

[川原泰三]

川原と申します。

count(*)をpg_classからのreltuplesから取得してはいかがでしょう？
tの行数が変化する度にanalyzeする仕組みが必要になるため、
どれぐらいの頻度でtが更新されるかにもよるとは思いますが…。

analyze t;

select i from t offset random()*((select reltuples from pg_class where relna
me = 't')) limit 1;

Limit  (cost=147.57..147.58 rows=1 width=4) (actual time=193.112..193.115 ro
ws=1 loops=1)

> ----- Original Message -----
> From: PostgreSQL Japanese Mailing List <pgsql-jp ＠ ml.postgresql.jp>
> To: 'PostgreSQL Japanese Mailing List' <pgsql-jp ＠ ml.postgresql.jp>
> Sent: 2010/09/14 15:17:54
> Subject: [pgsql-jp: 40397] n行のテーブルより任意の1行を取り出すSQL
> 
> 　武田と申します。いつも興味深く拝読させていただいております。
> 
> 　とても簡単な所で悩んでしまっているのかもしれないのですが、「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)
> 
> ■案１：とりあえず「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がネック
> 
> 
> ■案２：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(*)の取得）で結局全行スキャン
> 
> ■案３：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から採番された欠番の無いシーケンスであることが前提。

> （実際の実装においてはシーケンス加算後のロールバックなどによってこれは期待
で
> きない）
> 
> 
> 　私の方では以上が限度でして、「目標」までいかずとも、コストを「インデック
ス
> を用いた一件の取得」に近づけることの出来る方法などご存じの方がいらっしゃい
ま
> したらご教授頂けませんでしょうか。
> 
> 
> 
>