Re: Selecting random row

This does decrease the the cost, but only very little (10500 -> 9800), which
is not the solution. That thing with sequential index might work for some
cases, that is true. Unfortunately not in this one, because I actually do
not select random row from a table, but from a complex select query
resultset. But thanks for the suggestions.

Michal

"Arguile" <arguile(at)lucentstudios(dot)com> pe v diskusnm pspvku
news:1028294789(dot)352(dot)8(dot)camel(at)broadswd(dot)(dot)(dot)
> 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.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org