Returning random model objects - is order_by('?') on Postgres safe to use now?

[Victor Hooi]

Hi,

We have some code to return randomised Django model instances.

Initially, I was using random.randint(), and returning objects by index between 0 < max_id.

This was based on the Django docs:

https://docs.djangoproject.com/en/dev/ref/models/querysets/#order-by

specifically, the part:

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

This blog post from February 2011 also seems to back that up:

http://www.peterbe.com/plog/getting-random-rows-postgresql-django

However, I just ran the benchmarks on my own box running Django 1.5 and Postgres 9.2.4, and the ORDER BY RANDOM() approach was slower, but not substantially so (certainly not to the extent hinted at by the blog post).

I'm curious - what are other people's experiences with using ORDERY BY RANDOM versus other approaches? 

Has Postgres made some recent changes which makes ORDER BY RANDOM the better option now?

Which database backends specifically should we be warned about when saying that order_by('?') is expensive and slow?

Cheers,

Victor

[Tom Lockhart]

I haven't used the feature, but there is no reason to think that having the db backend do a random sort is any slower than you doing it yourself.

If your query can reduce the number of rows returned to a relatively small number, any "order by" should have acceptable performance. If not, it will be expensive no matter who does the work.

hth

                    - Tom

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Victor Hooi]

Hi,

The issue is that apparently, for ORDER BY RANDOM, the database will foo through and generate a random number for *every* row in a new column, then do a sort by that be column.

Obviously that's an expensive operation and since that column is random, it's a worst case sort.

For my code I was using randint to generate random numbers between 0 and the highest pk (and handling ObjectDoesNotExist).

Hence, this was meant to be faster and cheaper.

However, I'm curious if database backend has changes significantly?

Cheers,
Victor

You received this message because you are subscribed to a topic in the Google Groups "Django users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/django-users/QJk3HDYAkro/unsubscribe.
To unsubscribe from this group and all its topics, send an email to django-users...@googlegroups.com.