[Django] #36492: Sqlite: lack of specificity when querying against booleans hurts the query-optimizer

[Django]

#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.

[Django]

#36492: Sqlite: lack of specificity when querying against booleans hurts the query-
optimizer
-------------------------------------+-------------------------------------

Reporter: Klaas van Schelven | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted

Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Simon Charette):

* component: Uncategorized => Database layer (models, ORM)
* owner: (none) => Simon Charette
* stage: Unreviewed => Accepted
* status: new => assigned
* type: Uncategorized => Bug

Comment:

Managed to reproduce as well

{{{#!sql
sqlite> CREATE TABLE foo (a int, b int);
sqlite> CREATE INDEX some_idx ON foo (a, b);
sqlite> EXPLAIN SELECT * FROM foo WHERE NOT a AND NOT b;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 13 0 0 Start at 13
1 OpenRead 0 2 0 2 0 root=2 iDb=0;
foo
2 Explain 2 0 0 SCAN foo 0
3 Rewind 0 12 0 0
4 Column 0 0 1 0 r[1]=foo.a
5 If 1 11 1 0
6 Column 0 1 1 0 r[1]=foo.b
7 If 1 11 1 0
8 Column 0 0 2 0 r[2]=foo.a
9 Column 0 1 3 0 r[3]=foo.b
10 ResultRow 2 2 0 0 output=r[2..3]
11 Next 0 4 0 1
12 Halt 0 0 0 0
13 Transaction 0 0 2 0 1
usesStmtJournal=0
14 Goto 0 1 0 0
sqlite> EXPLAIN SELECT * FROM foo WHERE a=false AND b=false;
addr opcode p1 p2 p3 p4 p5 comment
---- ------------- ---- ---- ---- ------------- -- -------------
0 Init 0 12 0 0 Start at 12
1 OpenRead 1 3 0 k(3,,,) 2 root=3 iDb=0;
some_idx
2 Explain 2 0 0 SEARCH foo USING COVERING INDEX
some_idx (a=? AND b=?) 0
3 Integer 0 1 0 0 r[1]=0
4 Integer 0 2 0 0 r[2]=0
5 SeekGE 1 11 1 2 0 key=r[1..2]
6 IdxGT 1 11 1 2 0 key=r[1..2]
7 Column 1 0 3 0 r[3]=foo.a
8 Column 1 1 4 0 r[4]=foo.b
9 ResultRow 3 2 0 0 output=r[3..4]
10 Next 1 6 1 0
11 Halt 0 0 0 0
12 Transaction 0 0 2 0 1
usesStmtJournal=0
13 Goto 0 1 0 0
}}}

(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 override
`conditional_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.
--
Ticket URL: <https://code.djangoproject.com/ticket/36492#comment:1>

[Django]

#36492: Sqlite: lack of specificity when querying against booleans hurts the query-
optimizer
-------------------------------------+-------------------------------------
Reporter: Klaas van Schelven | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
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
-------------------------------------+-------------------------------------
Changes (by Simon Charette):

* has_patch: 0 => 1

Comment:

Took the `has_native_boolean_type` approach as suggested on
ticket:32691#comment:8
--
Ticket URL: <https://code.djangoproject.com/ticket/36492#comment:2>

[Django]

#36492: Sqlite: lack of specificity when querying against booleans hurts the query-
optimizer
-------------------------------------+-------------------------------------
Reporter: Klaas van Schelven | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 1

Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Simon Charette):

* needs_better_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/36492#comment:3>

[Django]

#36492: Sqlite: lack of specificity when querying against booleans hurts the query-
optimizer
-------------------------------------+-------------------------------------
Reporter: Klaas van Schelven | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
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
-------------------------------------+-------------------------------------
Changes (by Simon Charette):

* needs_better_patch: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/36492#comment:4>

[Django]

#36492: Sqlite: lack of specificity when querying against booleans hurts the query-
optimizer
-------------------------------------+-------------------------------------
Reporter: Klaas van Schelven | Owner: Simon
| Charette
Type: Bug | Status: assigned
Component: Database layer | Version: 5.2
(models, ORM) |
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 Simon Charette):

Addressed the merge conflicts and confirmed that the surfaced problems
with `exclude(indexed_bool_field=False|True)` on SQLite is a distinct
issue.
--
Ticket URL: <https://code.djangoproject.com/ticket/36492#comment:5>

[Django]

#36492: Sqlite: lack of specificity when querying against booleans hurts the query-
optimizer
-------------------------------------+-------------------------------------
Reporter: Klaas van Schelven | Owner: Simon
| Charette

Type: Bug | Status: closed

Component: Database layer | Version: 5.2
(models, ORM) |

Severity: Normal | Resolution: fixed

Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls <jacobtylerwalls@…>):

* resolution: => fixed
* status: assigned => closed

Comment:

In [changeset:"4bbc27c8686f10f9556cef02dbfa9f5157fbcf56" 4bbc27c]:
{{{#!CommitTicketReference repository=""
revision="4bbc27c8686f10f9556cef02dbfa9f5157fbcf56"
Fixed #36492 -- Restored exact boolean lookup against literals on SQLite.

Performance regression in 37e6c5b on SQLite. Just like MySQL, and
presumably
Oracle, which don't have a native boolean type and incidently store
booleans in
integer columns, indices on such columns cannot be used when explicit
boolean
literal equalities are omitted.

Adapt the logic introduced by refs #32691 for MySQL to be used for all
backends
that don't support native boolean fields instead of special casing MySQL,
SQLite, and Oracle in their own special way.

Note that review of this work surfaced that SQLite's query planner also
cannot
make use of indices when dealing with expressions of form

WHERE NOT (indexed_bool_field = false)

but that's a long standing problem unrelated to the restorative work
performed
in this patch.

Thanks Klaas van Schelven for the report.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36492#comment:6>

[Django]

#36492: Sqlite: lack of specificity when querying against booleans hurts the query-
optimizer
-------------------------------------+-------------------------------------
Reporter: Klaas van Schelven | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution: fixed

Keywords: | Triage Stage: Ready for
| checkin

Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Jacob Walls):

* stage: Accepted => Ready for checkin

--
Ticket URL: <https://code.djangoproject.com/ticket/36492#comment:7>

[Django]

#36492: Sqlite: lack of specificity when querying against booleans hurts the query-
optimizer
-------------------------------------+-------------------------------------
Reporter: Klaas van Schelven | Owner: Simon
| Charette
Type: Bug | Status: closed
Component: Database layer | Version: 5.2
(models, ORM) |
Severity: Normal | Resolution: fixed
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Jacob Walls <jacobtylerwalls@…>):

In [changeset:"ecc5182234546e712207a60a6109f70a9068a588" ecc51822]:
{{{#!CommitTicketReference repository=""
revision="ecc5182234546e712207a60a6109f70a9068a588"
[6.1.x] Fixed #36492 -- Restored exact boolean lookup against literals on

SQLite.

Performance regression in 37e6c5b on SQLite. Just like MySQL, and
presumably
Oracle, which don't have a native boolean type and incidently store
booleans in
integer columns, indices on such columns cannot be used when explicit
boolean
literal equalities are omitted.

Adapt the logic introduced by refs #32691 for MySQL to be used for all
backends
that don't support native boolean fields instead of special casing MySQL,
SQLite, and Oracle in their own special way.

Note that review of this work surfaced that SQLite's query planner also
cannot
make use of indices when dealing with expressions of form

WHERE NOT (indexed_bool_field = false)

but that's a long standing problem unrelated to the restorative work
performed
in this patch.

Thanks Klaas van Schelven for the report.

Backport of 4bbc27c8686f10f9556cef02dbfa9f5157fbcf56 from main.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/36492#comment:8>