Re: Selecting a random row - Mailing list pgsql-general

From Tom Lane
Subject Re: Selecting a random row
Date
Msg-id 25550.1099581910@sss.pgh.pa.us
Whole thread Raw
In response to Selecting a random row  (Kari Lavikka <tuner@bdb.fi>)
List pgsql-general
Kari Lavikka <tuner@bdb.fi> writes:
> --
> -- Choose a random point between 0 and max_uid and select the first
> -- value from the bigger part
> --
> CREATE OR REPLACE FUNCTION random_uid() RETURNS int4 AS
>   'SELECT uid FROM users u WHERE u.status = ''a'' AND uid >=
>    cast(cast(max_uid() - 1 AS FLOAT) * random() AS INTEGER) ORDER BY uid
>    ASC LIMIT 1'
>   LANGUAGE 'sql';

This isn't going to do what you think because the random() function is
re-evaluated at every row of the table.  (For that matter, so is
max_uid(), which means performance would suck even if it worked ...)

I'd suggest rewriting in plpgsql so you can assign the (max_uid-1)*random()
expression to a variable and then just use the variable in the SELECT.

            regards, tom lane

pgsql-general by date:

Previous
From: Jim Crate
Date:
Subject: Re: OS X Install
Next
From: "Matthew T. O'Connor"
Date:
Subject: Re: VACUUMING questions...