[Answer]-Django Optimisation of a queryset with Q objects

by

1đź‘Ť

âś…

First of all, the SQL command generated by the Django ORM probably won’t have the condition clauses in the same order as your .filter methods. So don’t worry about the “optimal” order.

Second, no matter in which order the clauses appear in the SQL command, the DB engine will optimize the request and generate an execution plan tailored to your data distribution. Any DB engine worth considering keeps some data distribution statistics. If the proportion of active records is actually the best discriminant in this query, then it will be filtered first.

👤Javier

0đź‘Ť

You can do that by chaining filters

MyModel.objects.filter(
    active=True
).filter(
    Q(start__gt=today) | Q(end__lte=today)
).update(active=False)

Additional notes

I don’t think you’ll get any performance gain by first filtering active and then filtering start and end. Because chaning or not-chaining will perform the same query. Here’s an example from Django docs:

Entry.objects.exclude(pub_date__gt=datetime.date(2005, 1, 3), headline='Hello')

In SQL terms, that evaluates to:

SELECT ...
WHERE NOT (pub_date > '2005-1-3' AND headline = 'Hello')

Note that the filters have been chained in the example above, yet in the SQL query both the filters are taken together.

To boost DB performance

  1. Look up DB indexing (as pointed in comments).

  2. Considering caching database in memory (see Memcached, for instance).

👤xyres

Leave a comment