#36492: Sqlite: lack of specificity when querying against booleans hurts the
query-
optimizer
-------------------------------------+-------------------------------------
Reporter: Klaas van Schelven | Type:
| Uncategorized
Status: new | Component:
| Uncategorized
Version: 5.2 | Severity: Normal
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
When doing queries against booleanfields Django generates SQL of the the
shape "WHERE" and "WHERE NOT" rather than matching exactly on 1 and 0.
This makes it harder than necessary for the sqlite query optimizer to
optimize, because the selection appears much more broad than it is in
practice.
This is exactly like described in this issue which was acknowledged and
fixed years ago. (though I don't know about the "regression" bit, it's
been going on since at least django 4.2):
https://code.djangoproject.com/ticket/32691 "Performance regression in
Exact lookup on BooleanField on MySQL."
Here's a reproducer:
https://github.com/bugsink/zeroforfalse
{{{
$ python manage.py print_zero_or_false
SELECT "myapp_mymodel"."id", "myapp_mymodel"."boolean_field" FROM
"myapp_mymodel" WHERE NOT "myapp_mymodel"."boolean_field"
SELECT "myapp_mymodel"."id", "myapp_mymodel"."boolean_field" FROM
"myapp_mymodel" WHERE "myapp_mymodel"."boolean_field"
}}}
Here's the context of discovery of this bug, as well as a workaround:
https://github.com/bugsink/bugsink/pull/139
--
Ticket URL: <https://code.djangoproject.com/ticket/36492>
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 view this discussion visit
https://groups.google.com/d/msgid/django-updates/01070197db1c709a-a35dfb0d-af25-4860-b6d4-d3a226af1eb1-000000%40eu-central-1.amazonses.com.
