#26194: WHERE EXISTS / WHERE NOT EXISTS clause without using QuerySet.extra
----------------------------------------------+----------------------------
Reporter: john-parton | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer (models, ORM) | Version: 1.9
Severity: Normal | Keywords: QuerySet.extra
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+----------------------------
I initially posted this to the Django users group here:
[https://groups.google.com/forum/#!topic/django-users/nBySOT3C27E]
Greetings!
I'm trying to refactor a query to avoid using QuerySet.extra (as per the
recommendation here:
[https://docs.djangoproject.com/en/1.9/ref/models/querysets/#extra])
Here's a simplified version of my code:
{{{
# testapp.models
class Parent(models.Model):
pass
class Child(models.Model):
parent = models.ForeignKey('testapp.Parent')
}}}
This is the query I am attempting to replace:
{{{
Parent.objects.extra(where=["""
NOT EXISTS (SELECT 1 FROM testapp_child WHERE testapp_child.parent_id
= testapp_parent.id)
"""])
}}}
This is extremely similar to, but not the same as
{{{
Parent.objects.exclude(id__in=Child.objects.all().values('parent_id'))
}}}
Both queries should return the same rows, but the biggest difference is
that PostgreSQL's query planner produces completely different plans. The
performance difference can be huge, even for a relatively modest data set.
(I believe my data set has something like 270k "parent" instances and 80k
"child" instances.)
I tried searching and couldn't find a solution to this exact problem.
Thanks for taking the time to read through all of this!
--
Ticket URL: <https://code.djangoproject.com/ticket/26194>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/054.cad03ad1f6826511dafc88d6596356e9%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
