Postgres Pro
Downloads
Home   >   mailing lists

Performance of ORDER BY RANDOM to select random rows? - Mailing list pgsql-general

From Victor Hooi
Subject Performance of ORDER BY RANDOM to select random rows?
Date
Msg-id CAMnnoU+K5dh_atGRAXPgYGhzxT5Spfs1ZMPRz8AmZ5CPeFaixQ@mail.gmail.com
Whole thread Raw
Responses Re: Performance of ORDER BY RANDOM to select random rows?  (hubert depesz lubaczewski <depesz@depesz.com>)
Re: Performance of ORDER BY RANDOM to select random rows?  (Sergey Konoplev <gray.ru@gmail.com>)
List pgsql-general
Tree view
Hi,

I have a Django application where we need to pull random rows out of a table.

According to the Django documentation:


Note: order_by('?') queries may be expensive and slow, depending on the database backend you’re using.  

My understanding is that order_by('?') in the Django ORM will generate a query with ORDER BY RANDOM.

This blog post:


also seems to suggest that using ORDER BY RANDOM() will perform poorly on Postgres.

I'm just wondering if this is still the case?

I just ran those benchmarks on my system (Postgres 9.2.4), and using ORDERY BY RANDOM did not seem substantially to generating random integers in Python and picking those out (and handling non-existent rows).

Has Postgres's behaviour for ORDER BY RANDOM change sometime recently?

Cheers,
Victor

pgsql-general by date:

Previous
From: Sergey Konoplev
Date:
Subject: Re: Pl/Python runtime overhead
Next
From: Sergey Konoplev
Date:
Subject: Re: Self referencing composite datatype