Re: Getting a random row

2009/10/14 Thom Brown <thombrown(at)gmail(dot)com>:
> 2009/10/14 Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>:
>>
>> If what you're trying to do is emulate a real world app which randomly
>> grabs rows, then you want to setup something ahead of time that has a
>> pseudo random order and not rely on using anything like order by
>> random() limit 1 or anything like that.  Easiest way is to do
>> something like:
>>
>> select id into randomizer from maintable order by random();
>>
>> then use a cursor to fetch from the table to get "random" rows from
>> the real table.
>>
>>
>
> Why not just do something like:
>
> SELECT thisfield, thatfield
> FROM my_table
> WHERE thisfield IS NOT NULL
> ORDER BY RANDOM()
> LIMIT 1;
>

this works well on small tables. On large tables this query is extremely slow.

regards
Pavel

> Thom
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>