Pavel Stehule wrote:
> > > >
> > >
> > > ok. I accept it. Can be some note there? Not this strange select.
> >
> > Well, with 8.3 having this be faster I am thinking we should wait to see
> > if the hacks are needed.
> >
>
> difference, on 10K lines (on small think table)
>
> postgres=# select * from test where i = any(array(select
> (random()*10000)::int from generate_series(1,20))) limit 1;
> i | v
> -----+-----
> 869 | 113
> (1 row)
>
> Time: 3,984 ms
>
> postgres=# select * from test order by random() limit 1;
> i | v
> ------+-----
> 3687 | 293
> (1 row)
>
> Time: 21,978 ms
>
> 8.2
> postgres=# select * from test order by random() limit 1;
> i | v
> ------+-----
> 4821 | 608
> (1 row)
>
> Time: 51,299 ms
>
> postgres=# select * from test where i = any(array(select
> (random()*10000)::int from generate_series(1,20))) limit 1;
> i | v
> -----+-----
> 762 | 254
> (1 row)
>
> Time: 4,530 ms
>
> Results:
>
> 8.3 "fast solution' is 6x faster
> 8.2 'fast solution' is 11x faster .. it's minimum.
OK, how do we even explain this idea in the FAQ. It pulls 20 random
values from 1 to 10000? That seems pretty hard to code to me. Where do
you get the 10000 number from? How do you know you will hit a match in
20 tries?
-- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB
http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +