#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 <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/01070197dd18ea2d-f7a83760-2e9f-4730-b97b-7683e08e58d5-000000%40eu-central-1.amazonses.com.
