#10790: Too many joins in a comparison for NULL.
-------------------------------------+-------------------------------------
Reporter: mtredinnick | Owner:
Type: Bug | mtredinnick
Component: Database layer | Status: new
(models, ORM) | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by famousactress@…):
I'd like to campaign for this patch being included in a 1.4 release. The
reason is because something happened between 1.3 and 1.4 that made this
problem wildly worse, and the patch solves the original issue and the new
severity. I suppose the change might qualify as a new bug but since the
fix appears to live here I figured I'd start here first.
My example involves three models (pseudocode ahead):
{{{
class AuditLog(models.Model):
pass
class Person(models.Model):
delete_log = models.ForeignKey(AuditLog)
class Document(models.Model)
status = models.CharField()
delete_log = models.ForeignKey(AuditLog)
person = models.ForeignKey(Person)
}}}
The query I ran into is basically:
{{{
Document.objects.exclude(delete_log__isnull=False).filter(status='something',
person__delete_log__isnull=True)
}}}
Prior to 1.4.x, this query would produce two joins: an inner join on
Person (required), and a left join on ActionLog against Person.delete_log
(not required). This is per the original description of this bug, since
this clause isn't in an exclude. As of 1.4.x, the query produces three
joins, the extra one being a left join against Document's delete_log. I
have more complex examples of this condition where my queries leap from
three joins to six or eight, obviously impacting performance dramatically.
The attached patch (v6) merges nicely onto the 1.4 branch, and I've
confirmed that it fixes all of the unnecessary joins in my particular
case... producing more efficient SQL in 1.3.x and prior, and _wildly_ more
efficient SQL than 1.4.
This bug is currently keeping me from moving to 1.4. I also suspect many
users are suffering from the original bug and this new worsening without
being completely aware.. If your queries are relatively simple, you've got
one join instead of none, or a couple joins instead of one.. in a lot of
conditions that's likely doubling your query time, but often from very
small to very small x 2.
My point though, is that fixing this problem is a quick way to make
basically everyone's Django code run faster. It's a miracle weight-loss
pill for null-check ORM code. I'd love to see this fix make it to a 1.4.x
release soon, and happy to help in any way I can!
--
Ticket URL: <https://code.djangoproject.com/ticket/10790#comment:23>
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 post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to
django-updates+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
