Re: Selecting random row

On Fri, 2002-08-02 at 08:38, Michal Taborsky wrote:
> I am facing a performance problem with selecting a random row from a table.
> I do it like this:
>
> SELECT * FROM table ORDER BY random() LIMIT 1
>
> But it seems that the random() function is quite slow and backend apparently
> evaluates the function for all rows (which is logical). On a table with few
> thousand rows it takes seconds. Does anybody know a better solution to
> selecting one (or more) random rows from a table ?

This isn't tested but I'd imagine it would work faster.

SELECT * FROM table WHERE id = (SELECT id FROM table ORDER BY random()
LIMIT 1);

You still generate a random number for every record but only pull the
PKs into mem (and the one selected record) instead of the entire table.
From what I understand of the internals this should be quicker.

Other options include keeping a lookup table with a sequential (no
breaks) key so you can use some PL language to generate a number in that
range. If your key is almost sequential anyways adding an error check -
to make sure the record exists and if not try again - might be easier.