From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Kari Lavikka <tuner(at)bdb(dot)fi> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Selecting a random row |
Date: | 2004-11-04 15:25:10 |
Message-ID: | 25550.1099581910@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Kari Lavikka <tuner(at)bdb(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Matthew T. O'Connor | 2004-11-04 15:48:04 | Re: VACUUMING questions... |
Previous Message | Jim Crate | 2004-11-04 15:11:18 | Re: OS X Install |