#23383: Django ORM generates inefficient SQL (only selected columns should be
grouped by)
----------------------------------------------+--------------------
Reporter: aalebedev | Owner: nobody
Type: Uncategorized | Status: new
Component: Database layer (models, ORM) | Version: 1.6
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
I have encountered a problem. When I run the following code in django
shell:
{{{
from django.contrib.auth.models import Group
from django.db.models import Count
print Group.objects.annotate(cnt=Count('user')).values('id',
'cnt').query.sql_with_params()
}}}
Django ORM generates the following SQL query:
{{{
SELECT `auth_group`.`id`, COUNT(`auth_user_groups`.`user_id`) AS `cnt`
FROM `auth_group` LEFT OUTER JOIN `auth_user_groups` ON (
`auth_group`.`id` = `auth_user_groups`.`group_id` ) GROUP BY
`auth_group`.`id`, `auth_group`.`name` ORDER BY NULL
}}}
'''auth_group.name''' occurs in '''GROUP BY''' statement. But this column
isn't represented in '''SELECT''' statement. Such query is inefficient
(expecially for large tables with many columns and rows).
Debuging of Django SQLCompiler
(https://github.com/django/django/blob/stable/1.6.x/django/db/models/sql/compiler.py#L568)
gives me the following information:
- postgresql:
''self.query.select'' == ''self.query.group_by'' == [(u'auth_group',
u'id'), (u'auth_group', 'name')]
''self.connection.features.allows_group_by_pk'' is False
''(len(self.query.get_meta().concrete_fields) ==
len(self.query.select))'' is False
'''auth_group.name''' appears in result because of the line:
{{{
cols = self.query.group_by + having_group_by + select_cols
}}}
- mysql:
''self.query.select == self.query.group_by'' == [(u'auth_group',
u'id'), (u'auth_group', 'name')]
''self.connection.features.allows_group_by_pk'' is True
''(len(self.query.get_meta().concrete_fields) ==
len(self.query.select))'' is False
'''auth_group.name''' appears in result because of the line:
{{{
cols = self.query.group_by + having_group_by + select_cols
}}}
In the same time, the following code (without ''.values()''):
{{{
from django.contrib.auth.models import Group
from django.db.models import Count
print Group.objects.annotate(cnt=Count('user')).query.sql_with_params()
}}}
gives the right SQL query for mysql (because
''(len(self.query.get_meta().concrete_fields) == len(self.query.select))''
is True):
{{{
SELECT `auth_group`.`id`, `auth_group`.`name`,
COUNT(`auth_user_groups`.`user_id`) AS `cnt` FROM `auth_group` LEFT OUTER
JOIN `auth_user_groups` ON ( `auth_group`.`id` =
`auth_user_groups`.`group_id` ) GROUP BY `auth_group`.`id` ORDER BY NULL
}}}
I guess, only selected columns (either through ''values()'' or ''only()'')
should be grouped by, regardless of the backend
--
Ticket URL: <https://code.djangoproject.com/ticket/23383>
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.5e4d8f40abcaa85dbe719ac4fe62cdb7%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
