Re: Getting a random row

On Wed, Oct 14, 2009 at 1:20 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
> 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.

Exactly. If you're running that query over and over your "performance
test" is on how well pgsql can run that very query. :) Anything else
you do is likely to be noise by comparison.