Selecting a random row

[Kari Lavikka]

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

|\__/|
( oo ) Kari Lavikka - tu***@bdb.fi
__ooO( )Ooo_______ _____ ___ _ _ _ _ _ _ _
""

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

[Holger Klawitter]

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

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

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

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 ma*******@postgresql.org

[Kari Lavikka]

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)

|\__/|
( oo ) Kari Lavikka - tu***@bdb.fi
__ooO( )Ooo_______ _____ ___ _ _ _ _ _ _ _
""

On Thu, 4 Nov 2004, Holger Klawitter wrote:

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

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

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

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 ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Csaba Nagy]

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:

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)

|\__/|
( oo ) Kari Lavikka - tu***@bdb.fi
__ooO( )Ooo_______ _____ ___ _ _ _ _ _ _ _
""

On Thu, 4 Nov 2004, Holger Klawitter wrote:

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

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

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

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 ma*******@postgresql.org

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

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

[Kari Lavikka]

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 - tu***@bdb.fi - [phone]
__ooO( )Ooo_______ _____ ___ _ _ _ _ _ _ _
""
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Tom Lane]

Kari Lavikka <tu***@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

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

[gnari]

From: "Kari Lavikka" <tu***@bdb.fi>

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)

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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Tom Lane]

"gnari" <gn***@simnet.is> writes:

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.

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

[Kari Lavikka]

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.
There are some gaps but distribution of them is quite uniform. And results
seem to be random enuff for this particular purpose.
Maybe using an OFFSET of (count(*) * random()) and a LIMIT 1
could be practical.

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

|\__/|
( oo ) Kari Lavikka - tu***@bdb.fi
__ooO( )Ooo_______ _____ ___ _ _ _ _ _ _ _
""
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings