Hi!

I have to select a random row from a table where primary key isn't
continuous (some rows have been deleted). Postgres just seems to do
something strange with my method.

--
-- Use the order by desc limit 1 -trick to get maximum value
--
CREATE OR REPLACE FUNCTION max_uid() RETURNS int4 AS
'SELECT uid FROM users WHERE status = ''a'' ORDER BY uid DESC LIMIT 1'
LANGUAGE 'sql';


--
-- 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';

--
-- testing and looks good
--
galleria=> SELECT max_uid();
max_uid
---------
126263

--
-- testing...
--
galleria=> SELECT random_uid(), random_uid(), random_uid(), random_uid(), random_uid();
random_uid | random_uid | random_uid | random_uid | random_uid
------------+------------+------------+------------+------------
322 | 601 | 266 | 427 | 369

.... but what is this? Values seem to vary from 0 to ~1000.
Not from 0 to 126263!!

How about doing some manual work...

--
-- Testing split point selection
--
galleria=> SELECT cast(cast(max_uid() - 1 AS FLOAT) * random() AS INTEGER);
int4
-------
43279

--
-- And inserting split point manually
--
galleria=> SELECT uid FROM users u WHERE u.status = 'a' AND uid >= 43279
ORDER BY uid ASC LIMIT 1;
uid
-------
43284

Works just fine!


Is there any explanation for this strange behavior or are there better
ways to select a random row?

I'm using PG 8.0 b2. Plan for the query is:

Limit (cost=0.00..5.19 rows=1 width=4)
-> Index Scan using users_pkey on users u (cost=0.00..69145.26 rows=13329 width=4)
Filter: ((status = 'a'::bpchar) AND (uid >= ((((max_uid() - 1))::double precision * random()))::integer))

__ooO( )Ooo_______ _____ ___ _ _ _ _ _ _ _
""

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thursday 04 November 2004 12:36, Kari Lavikka wrote:
How about

SELECT ...whatever... ORDER BY random() LIMIT 1;

Mit freundlichem Gruß / With kind regards
Holger Klawitter
- --
lists <at> klawitter <dot> de
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQFBiibF1Xdt0HKSwgYRAlJXAJ4nUpDfKBKCigPVMt8WpK G4gZmt4wCcD/ZC
KHBlBl1+5FZ4pgqkZlyzWQA=
=MrrE
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Works but is too slooow. Shuffling whole table and selecting the first
row is not the way to go in this case.

Limit (cost=5340.74..5340.74 rows=1 width=4)
-> Sort (cost=5340.74..5440.70 rows=39986 width=4)
Sort Key: random()
-> Seq Scan on users (cost=0.00..2284.37 rows=39986 width=4)
Filter: (status = 'a'::bpchar)

__ooO( )Ooo_______ _____ ___ _ _ _ _ _ _ _
""

On Thu, 4 Nov 2004, Holger Klawitter wrote:

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

IIRC, this was discussed a few times on this list, searching the
archives might get you some results. AFAIR, the only way to do it
efficiently is to have a column specially assigned for this purpose, and
populate it with random numbers in a big range. The column should be
indexed to assure fast access based on it. Then you can select the first
row with that column's value bigger (or smaller if you like) than a
random number in the same range.

HTH,
Csaba.

On Thu, 2004-11-04 at 14:34, Kari Lavikka wrote:

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Replying to myself..

Actually I found an answer. If a I wrap the split point selection to
subquery then the range of results is from 0 to maximum value (~120k in
this case)

galleria=> SELECT u.uid FROM users u WHERE u.status = 'a' AND uid >=
(select cast(cast((SELECT uid FROM users WHERE status = 'a' ORDER BY uid
DESC LIMIT 1) - 1 AS FLOAT) * random() AS INTEGER)) ORDER BY uid ASC LIMIT 1;
uid
-------
91937
(1 row)

Limit (cost=1.73..3.53 rows=1 width=4)
InitPlan
-> Result (cost=1.71..1.73 rows=1 width=0)
InitPlan
-> Limit (cost=0.00..1.71 rows=1 width=4)
-> Index Scan Backward using users_pkey on users (cost=0.00..68423.70 rows=39986 width=4)
Filter: (status = 'a'::bpchar)
-> Index Scan using users_pkey on users u (cost=0.00..23983.04 rows=13329 width=4)
Index Cond: (uid >= $1)
Filter: (status = 'a'::bpchar)


However, without the additional nothing doing subquery the range of
results is something like 0 to ~1000 which is of course wrong.


galleria=> SELECT u.uid FROM users u WHERE u.status = 'a' AND uid >=
cast(cast((SELECT uid FROM users WHERE status = 'a' ORDER BY uid DESC
LIMIT 1) - 1 AS FLOAT) * random() AS INTEGER) ORDER BY uid ASC LIMIT 1;
uid
-----
587
(1 row)


Examining the query plan reveals that without subquery the random
comparison is made for each row. That causes a kind of "premature
selection".


galleria=> explain SELECT u.uid FROM users u WHERE u.status = 'a' AND uid

------------------------------------------------------------------------------------------------------------
Limit (cost=1.71..6.89 rows=1 width=4)
InitPlan
-> Limit (cost=0.00..1.71 rows=1 width=4)
-> Index Scan Backward using users_pkey on users (cost=0.00..68423.70 rows=39986 width=4)
Filter: (status = 'a'::bpchar)
-> Index Scan using users_pkey on users u (cost=0.00..69042.18 rows=13329 width=4)
Filter: ((status = 'a'::bpchar) AND (uid >= (((($0 - 1))::double precision * random()))::integer))
(7 rows)


Well, it works now. Thanks anyway


__ooO( )Ooo_______ _____ ___ _ _ _ _ _ _ _
""


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Kari Lavikka <tuner (AT) bdb (DOT) fi> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Kari,

Why not select count(*) from the table and multiply it by a true 0.0 - 1.0
pseudo random number generator? Then adjust the outcome for the range of
uids. If the uids (or some other column) are contiguous starting at 0,
this would be a snap.

Rick



Tom Lane
<tgl (AT) sss (DOT) pgh.pa.us> To: Kari Lavikka <tuner (AT) bdb (DOT) fi>
Sent by: cc: pgsql-general (AT) postgresql (DOT) org
pgsql-general-owner@pos Subject: Re: [GENERAL] Selecting a random row
tgresql.org


11/04/2004 10:25 AM






Kari Lavikka <tuner (AT) bdb (DOT) fi> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings






---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

From: "Kari Lavikka" <tuner (AT) bdb (DOT) fi>
Tthe problem with this is that this is not very random.
If the uids 30000 to 39999 have been missing, but
the uids are more or less contiguous apart from that,
the uid 40000 would be 10000 times more likely to be selected
than average.

Maybe using an OFFSET of (count(*) * random()) and a LIMIT 1
could be practical.

gnari



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

"gnari" <gnari (AT) simnet (DOT) is> writes:
There is some discussion of selecting random rows in the list archives.
My recollection is that we decided the only way to be fast *and*
truly random was to dedicate an extra column to be a random key.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

There are some gaps but distribution of them is quite uniform. And results
seem to be random enuff for this particular purpose.

Something like OFFSET (random() * 10) could be used for additional
randomness of course.

__ooO( )Ooo_______ _____ ___ _ _ _ _ _ _ _
""


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings