Annotate with conditional aggregation includes non-relevant fields in GROUP BY clause

[mark88]

I'm using Django 1.8.6 and conditional aggregates.

objects = self.get_queryset().annotate(sent_count=
        Sum(
            Case(When(messages__date_sent__gte=date_from, then=1)),
            output_field=IntegerField()
        ),
        error_count=
        Sum(
            Case(When(messages__status__iexact='error', then=1)),
            output_field=IntegerField()
        ),
        sent_yesterday=
        Sum(
            Case(When(messages__date_sent__gte=yesterday, then=1)),
            output_field=IntegerField()
        )
    )

And when viewing SQL query for this annotation, I see every single field in this model is included in GROUP BY clause, making query very slow. There's also one field from related model included in GROUP BY. I've run the query manually, removing all unnecessary fields from the clause and it runs fine

[Simon Charette]

Hi mark88,

I suspect this is related to #19259. Are you using PostgreSQL? Can you also reproduce with Django 1.9+?

[Josh Smeaton]

MySQL and Postgres will optimise by grouping by all primary keys of each joined table, but this optimisation may not exist for queries that include "complex" annotations. If you don't need all of the fields from self, then prepending a values() clause before the annotate is how you restrict the group by.

self.get_queryset().values('pk').annotate(...) should do what you want, again, if you don't need the other fields from the self model.

[Simon Charette]

Mark,

I tried a building a queryset similar to yours locally and unrelated fields were excluded from the GROUP BY clause on Django 1.9+ using PostgreSQL.

If it's not the case for your query please reopen this ticket so we can investigate what can be done.

Resolving as needsinfo until feedback is provided.