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
29725	Inefficient SQL generated when counting a ManyToMany	Gavin Wahl	ontowhee	"When calling count() on an unfiltered many to many relation, a useless join is included in the SQL that makes it much slower than it should be. On my dataset, the difference is 1000ms to 100ms, because an index-only scan can be used.

This is the SQL that is currently generated:

{{{#!sql
SELECT COUNT(*) AS ""__count""
FROM ""app_foo""
INNER JOIN ""app_foo_bar"" ON (""app_foo"".""id"" = ""app_foo_bar"".""foo_id"")
WHERE ""app_foo_bar"".""foo_id"" = ?;
}}}

This is the SQL that should be generated:

{{{#!sql
SELECT COUNT(*) AS ""__count""
FROM ""app_foo_bar""
WHERE ""app_foo_bar"".""foo_id"" = ?;
}}}

This optimization can only be applied when there are no filters applied, because then the join is used to satisfy the filters. In the no-filters case, only the through table needs to be consulted.
"	Cleanup/optimization	closed	Database layer (models, ORM)	dev	Normal	fixed		Simon Charette	Ready for checkin	1	0	0	0	0	0