Re: Selecting a random row

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

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

|\__/|
( oo ) Kari Lavikka - tuner(at)bdb(dot)fi - (050) 380 3808
__ooO( )Ooo_______ _____ ___ _ _ _ _ _ _ _
""