Fastest query for random row from subset of large table - postgresql

Question

I am using rails and Postgresql. I have a very large table that includes a column called status. From this status column a lot of rows have a value of 1. I want to select a random row based on all rows with a value of 1 in the status column. Each row also has an id and they are sequential.

What is the fastest way to get this random row?

I would recommend also check this http://stackoverflow.com/questions/8674718/best-way-to-select-random-rows-postgresql for good discussion on how to perform this query effectively — dimakura, Sep 12 '15 at 20:34

Arsen · Answer 1 · 2015-09-12T20:33:19.973

0

You can play with OFFSET option

SELECT * FROM users WHERE status = 1 OFFSET random()*I LIMIT 1;

The I is the number of rows in table.

edited Sep 12 '15 at 20:33

answered Sep 12 '15 at 20:20

Arsen

10,815
2
34
46

What is the number of rows in the table is constantly changing? – user4584963 Sep 12 '15 at 20:27
You probably mean `LIMIT 1` – dimakura Sep 12 '15 at 20:29
You can write `offset random() * (select count(*) from users)` – Arsen Sep 12 '15 at 20:33

Zakaria Acharki · Accepted Answer · 2015-09-12T20:59:34.483

0

Try to use ActiveRecord solution, e.g :

Model_name.where(status: 1).limit(1).order("RANDOM()")

OR :

Model_name.where(status: 1).order("RANDOM()").first

Hope this helps.

edited Sep 12 '15 at 20:59

answered Sep 12 '15 at 20:28

Zakaria Acharki

66,747
15
75
101

Why is the limit 5 if I only need 1 row? Is that a typo? – user4584963 Sep 12 '15 at 20:57
You can use `.first`. – Zakaria Acharki Sep 12 '15 at 20:59
So its just making the order of the subset random and picking the first one? It doesn't need to go through all the records right? – user4584963 Sep 12 '15 at 21:05
How would I change the second line to pick a subset where status = 1 AND a second column lets say apples does not equal 5? – user4584963 Sep 12 '15 at 22:11

Fastest query for random row from subset of large table - postgresql

2 Answers2