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

[Tomoaki Sato]

佐藤です。

> 　武田と申します。いつも興味深く拝読させていただいております。
> 
> 　とても簡単な所で悩んでしまっているのかもしれないのですが、「n行の
> テーブルより無作為に選んだ1行を取り出すSQL」で困っています。
> 
> 　書けないことは無いのですがパフォーマンスを気にしており、更に可能で
> あれば、クライアント側の処理系を使うこと無くSQLのみで完結させたいと思っ
> ています。当方主に8.3系、8.4系を使っておりますが、全バージョンで同じ
> 挙動となる気がします。

以下のような SQL ではいかがでしょうか？

=# EXPLAIN SELECT i FROM t WHERE i <= (SELECT floor(min(i) + (max(i) - min(i) + 1) * random()) FROM t)::integer ORDER BY i DESC LIMIT 1;
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Limit  (cost=0.09..0.13 rows=1 width=4)
   InitPlan 3 (returns $2)
     ->  Result  (cost=0.06..0.09 rows=1 width=0)
           InitPlan 1 (returns $0)
             ->  Limit  (cost=0.00..0.03 rows=1 width=4)
                   ->  Index Scan using t_pkey on t  (cost=0.00..30408.36 rows=1000000 width=4)
                         Index Cond: (i IS NOT NULL)
           InitPlan 2 (returns $1)
             ->  Limit  (cost=0.00..0.03 rows=1 width=4)
                   ->  Index Scan Backward using t_pkey on t  (cost=0.00..30408.36 rows=1000000 width=4)
                         Index Cond: (i IS NOT NULL)
   ->  Index Scan Backward using t_pkey on t  (cost=0.00..10971.69 rows=333333 width=4)
         Index Cond: (i <= ($2)::integer)
(13 rows)

i 列の最小値から最大値までのランダムな値を作り、その値以下の最大値を取
り出すというものです。この SQL ならインデックスを使えますし、取り出し
た値は必ず存在するはずです。

ただ、欠番の分布が均一でないと取り出す値に偏りが出てしまうことに注意し
てください。


----
佐藤 友章 <sato ＠ sraoss.co.jp>
SRA OSS, Inc. 日本支社
PostgreSQL トレーニング: http://www.sraoss.co.jp/prod_serv/training/
PowerGres ファミリー: http://powergres.sraoss.co.jp/