id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
36492	Sqlite: lack of specificity when querying against booleans hurts the query-optimizer	Klaas van Schelven	Simon Charette	"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"	Bug	assigned	Database layer (models, ORM)	5.2	Normal				Accepted	1	0	0	0	0	0