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

武田 憲太郎 takeda @ youmind.jp
2010年 9月 15日 (水) 13:30:25 JST


 佐藤様

 武田です。

=====================================
>  とても簡単な所で悩んでしまっているのかもしれないのですが、「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;
=====================================

 ありがとうございます。確かにこれで行けますね。

 シーケンスの欠番の偏りは気になりますが、tに対してdeleteが走ったりする構造
でない限り、また何らかの理由で明示的にrollbackさせることを想定した実装でない
限り、多くの場合、欠番の発生は一定確率に収まるはずなので、これで大丈夫そうで
す。そもそも「無作為」が要件ですし…。

 値の分布や更新頻度などを調査し、佐藤様と川原様より頂いた方法のどちらにする
か考えてみようかと思います。ありがとうございました。

※蛇足
 Cでソートなどを自前で実装していた頃はこういったアプローチからのコーディン
グもよくやっていたのですが、ここ最近はSQLに慣れきってしまい他人任せというか
アプリ任せ。見直す良い機会になりました。



-----Original Message-----
From: Tomoaki Sato [mailto:sato @ sraoss.co.jp]
Sent: Tuesday, September 14, 2010 11:15 PM
To: pgsql-jp @ ml.postgresql.jp; takeda @ youmind.jp
Subject: Re: [pgsql-jp: 40397] n行のテーブルより任意の1行を取り出すSQL

佐藤です。

>  武田と申します。いつも興味深く拝読させていただいております。
>
>  とても簡単な所で悩んでしまっているのかもしれないのですが、「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/



pgsql-jp メーリングリストの案内