Received: from localhost (uranus.hub.org [200.46.204.60]) by postgresql.org (Postfix) with ESMTP id C97A39F9FE1 for ; Sat, 18 Nov 2006 14:45:15 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.60]) (amavisd-new, port 10024) with ESMTP id 45232-01 for ; Sat, 18 Nov 2006 14:45:10 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.182]) by postgresql.org (Postfix) with ESMTP id 380A09F9B3F for ; Sat, 18 Nov 2006 14:45:08 -0400 (AST) Received: by py-out-1112.google.com with SMTP id d32so637197pye for ; Sat, 18 Nov 2006 10:45:08 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=DJpTdz1iqU4kCiRe0d+kfeV9RCYHoxl8+U326pPydCYxD9H04pzC+RQJGzKh+qFN3pJ4BS1AufmKIsPchzHlFK24s1HgEt3xJeUnaDNEB346uWH6+NJrnJ3xCAlyofeYeG/itdhyspDyTX8ExJfIVl0NO2GFAqWVHnl8KrlpUZU= Received: by 10.35.34.20 with SMTP id m20mr4784303pyj.1163875507081; Sat, 18 Nov 2006 10:45:07 -0800 (PST) Received: by 10.35.80.10 with HTTP; Sat, 18 Nov 2006 10:45:06 -0800 (PST) Message-ID: Date: Sun, 19 Nov 2006 00:15:07 +0530 From: "Rajesh Kumar Mallah" To: "A. Kretschmer" Subject: Re: Random() Cc: pgsql-sql@postgresql.org In-Reply-To: <20061118175216.GA1444@a-kretschmer.de> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3437_23084230.1163875507028" References: <55c095e90611161031h7a1ce42chd9a59a53ba7e7028@mail.gmail.com> <20061116191632.GA21285@a-kretschmer.de> <20061118175216.GA1444@a-kretschmer.de> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/200 X-Sequence-Number: 26852 ------=_Part_3437_23084230.1163875507028 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 11/18/06, A. Kretschmer wrote: > > am Sat, dem 18.11.2006, um 23:02:33 +0530 mailte Rajesh Kumar Mallah > folgendes: > > >select from order by random() limit 1; > > > > This query will tend to get slower as the table grows because of the > > sorting. > > Right. > > > > > > it possible to get a row from a random offset > > how about > > > > select from limit 1 offset 20000*random() > ; > > For instance, you have a table with only 500 rows and random() returns > 0.999? In other words: in think, this is a bad idea, unless you use a > constant for the offset-calculation thats not larger than the number of > rows in this table. For this, you need this value, but select count(1) > from table force a seqscan -> slow. but order by random() would also forces a seqscan , looks like the random offset method performs better explain analyze select count(*) from general.profile_master; +--------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +--------------------------------------------------------------------------------------------------------------------------------+ | Aggregate (cost=139214.26..139214.27 rows=1 width=0) (actual time= 3071.178..3071.179 rows=1 loops=1) | | -> Seq Scan on profile_master (cost=0.00..137703.21 rows=604421 width=0) (actual time=0.032..2686.842 rows=601240 loops=1) | | Total runtime: 3071.268ms | +--------------------------------------------------------------------------------------------------------------------------------+ (3 rows) tradein_clients=> explain analyze SELECT profile_id from general.profile_master limit 1 offset 601240*random(); +--------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +--------------------------------------------------------------------------------------------------------------------------------+ | Limit (cost=13770.30..13770.53 rows=1 width=4) (actual time= 1614.146..1614.147 rows=1 loops=1) | | -> Seq Scan on profile_master (cost=0.00..137703.21 rows=604421 width=4) (actual time=0.036..1375.742 rows=429779 loops=1) | | Total runtime: 1614.187ms | +--------------------------------------------------------------------------------------------------------------------------------+ (3 rows) tradein_clients=> explain analyze SELECT profile_id from general.profile_master order by random() limit 1; +--------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +--------------------------------------------------------------------------------------------------------------------------------------+ | Limit (cost=207079.39..207079.40 rows=1 width=4) (actual time= 11715.694..11715.695 rows=1 loops=1) | | -> Sort (cost=207079.39..208590.45 rows=604421 width=4) (actual time= 11715.691..11715.691 rows=1 loops=1) | | Sort Key: random() | | -> Seq Scan on profile_master (cost=0.00..139214.26 rows=604421 width=4) (actual time=0.036..4605.259 rows=601241 loops=1) | | Total runtime: 11716.039ms | +--------------------------------------------------------------------------------------------------------------------------------------+ (5 rows) Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header) > GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > ------=_Part_3437_23084230.1163875507028 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline

On 11/18/06, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote:
am  Sat, dem 18.11.2006, um 23:02:33 +0530 mailte Rajesh Kumar Mallah folgendes:
> >select <bigint_column> from <your_table> order by random() limit 1;
>
> This query will tend to get slower as the table grows because of the
> sorting.

Right.


>
> it possible to get a row from a random offset
> how about
>
> select <bigint_column> from <your_table>  limit 1 offset  20000*random() ;

For instance, you have a table with only 500 rows and random() returns
0.999? In other words: in think, this is a bad idea, unless you use a
constant for the offset-calculation thats not larger than the number of
rows in this table. For this, you need this value, but select count(1)
from table force a seqscan -> slow.

but order by random() would also forces a seqscan , looks like the random offset method performs
better

explain analyze select count(*) from general.profile_master;
+--------------------------------------------------------------------------------------------------------------------------------+
|                                                           QUERY PLAN                                                           |
+--------------------------------------------------------------------------------------------------------------------------------+
| Aggregate  (cost=139214.26..139214.27 rows=1 width=0) (actual time=3071.178..3071.179 rows=1 loops=1)                          |
|   ->  Seq Scan on profile_master  (cost=0.00..137703.21 rows=604421 width=0) (actual time=0.032..2686.842 rows=601240 loops=1) |
| Total runtime: 3071.268 ms                                                                                                     |
+--------------------------------------------------------------------------------------------------------------------------------+
(3 rows)

tradein_clients=> explain analyze SELECT profile_id from general.profile_master limit 1 offset 601240*random();
+--------------------------------------------------------------------------------------------------------------------------------+
|                                                           QUERY PLAN                                                           |
+--------------------------------------------------------------------------------------------------------------------------------+
| Limit  (cost=13770.30..13770.53 rows=1 width=4) (actual time=1614.146..1614.147 rows=1 loops=1)                                |
|   ->  Seq Scan on profile_master  (cost=0.00..137703.21 rows=604421 width=4) (actual time=0.036..1375.742 rows=429779 loops=1) |
| Total runtime: 1614.187 ms                                                                                                     |
+--------------------------------------------------------------------------------------------------------------------------------+
(3 rows)

tradein_clients=> explain analyze SELECT profile_id from general.profile_master order by random() limit 1;
+--------------------------------------------------------------------------------------------------------------------------------------+
|                                                              QUERY PLAN                                                              |
+--------------------------------------------------------------------------------------------------------------------------------------+
| Limit  (cost=207079.39..207079.40 rows=1 width=4) (actual time=11715.694..11715.695 rows=1 loops=1)                                  |
|   ->  Sort  (cost=207079.39..208590.45 rows=604421 width=4) (actual time=11715.691..11715.691 rows=1 loops=1)                        |
|         Sort Key: random()                                                                                                           |
|         ->  Seq Scan on profile_master  (cost=0.00..139214.26 rows=604421 width=4) (actual time=0.036..4605.259 rows=601241 loops=1) |
| Total runtime: 11716.039 ms                                                                                                          |
+--------------------------------------------------------------------------------------------------------------------------------------+
(5 rows)






Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match

------=_Part_3437_23084230.1163875507028--