id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
26194	WHERE EXISTS / WHERE NOT EXISTS clause without using QuerySet.extra	john-parton	nobody	"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!"	Uncategorized	closed	Database layer (models, ORM)	1.9	Normal	duplicate	QuerySet.extra		Unreviewed	0	0	0	0	0	0