Opened 6 years ago

Closed 6 years ago

#26045 closed Bug (needsinfo)

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

Reported by: mark88 Owned by: nobody
Component: Database layer (models, ORM) Version: 1.8
Severity: Normal Keywords: orm, sql, annotation
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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

Change History (3)

comment:1 Changed 6 years ago by Simon Charette

Hi mark88,

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

comment:2 Changed 6 years ago by 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.

comment:3 Changed 6 years ago by Simon Charette

Resolution: needsinfo
Status: newclosed

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.

Note: See TracTickets for help on using tickets.
Back to Top