Opened 2 months ago
Last modified 24 hours ago
#36492 assigned Bug
Sqlite: lack of specificity when querying against booleans hurts the query-optimizer
Reported by: | Klaas van Schelven | Owned by: | Simon Charette |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 5.2 |
Severity: | Normal | Keywords: | |
Cc: | Triage Stage: | Accepted | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
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
Change History (4)
comment:1 by , 2 months ago
Component: | Uncategorized → Database layer (models, ORM) |
---|---|
Owner: | set to |
Status: | new → assigned |
Triage Stage: | Unreviewed → Accepted |
Type: | Uncategorized → Bug |
comment:2 by , 2 months ago
Has patch: | set |
---|
Took the has_native_boolean_type
approach as suggested on ticket:32691#comment:8
comment:3 by , 2 months ago
Patch needs improvement: | set |
---|
comment:4 by , 24 hours ago
Patch needs improvement: | unset |
---|
Managed to reproduce as well
(notice how the NOT usage doesn't make use of the covering index)
Unfortunately Django doesn't have a
has_native_boolean_field
feature flag so the closest we could do here is overrideconditional_expression_supported_in_where_clause
like we did in #32691.I'll try to submit work that unifies the different code path we have in place today.