#29725: Inefficient SQL generated when counting a ManyToMany
-------------------------------------+-------------------------------------
Reporter: Gavin | Owner: nobody
Wahl |
Type: | Status: new
Uncategorized |
Component: Database | Version: 2.1
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/29725>
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 post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/052.570bebabd152aef9a862d8e48cb7fecc%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
