I've analyzed the query using pgAdmin and it seems the most time is being
spent with a the GroupAggregate.
This is because of so many columns being listed in the GROUP BY clause.
Creating two-column indexes over event_id and date or some other
combinations I've tried did not gain any perforamance.

Thus I've tried to get rid of some by just selecting some columns via
"only(...)". Strangely this does not have any effect on the GROUP BY clause.

On the other hand, if I use "values(...)" the GROUP BY clause shrinks to
only the named columns (but still being listed twice each).

>>> str(Event.objects.values('id','title_de','status')

...   .filter(status=Event.STATUS_ONLINE)
>
...   .annotate(eventdate_max=models.Max('eventdate__date'))
>
...   .filter(eventdate_max__lt=today).query)
>

>
'SELECT "events_event"."id", "events_event"."title_de",
> "events_event"."status", MAX("events_eventdate"."date") AS "eventdate_max"
> FROM "events_event" LEFT OUTER JOIN "events_eventdate" ON
> ("events_event"."id" = "events_eventdate"."event_id") WHERE
> ("events_event"."status" = 2 ) GROUP BY "events_event"."id",
> "events_event"."title_de", "events_event"."status", "events_event"."id",
> "events_event"."title_de", "events_event"."status" HAVING
> MAX("events_eventdate"."date") < 2011-03-29  ORDER BY
> "events_event"."title_de" ASC, "events_event"."status" ASC'
>

This query runs in a few milliseconds. I guess I'll just go with this for
now since I don't know any other method...

The QuerySet.only() method not limiting the GROUP BY clause to the named
columns seems like a "bug" (or inefficiency) in the ORM to me. Is that valid
at any rate?


Regards,
Fabian


2011/3/29 bruno desthuilliers <bruno.desthuilli...@gmail.com>

> On 29 mar, 12:26, Fabian Büchler <fabian.buech...@gmail.com> wrote:
>
> (snip part about the generated query)
>
> > As Javier suggested, an index on the "events_eventdate" over table over
> > "event_id" and "date" could help, but I don't know how to create one
> using
> > Django's model techniques
>
> This is something you can do directly at the database level - and you
> can add custom SQL statements to be run at syncdb time if necessary:
>
>
> http://docs.djangoproject.com/en/dev/howto/initial-data/#providing-initial-sql-data
>
> --
> You received this message because you are subscribed to the Google Groups
> "Django users" group.
> To post to this group, send email to django-users@googlegroups.com.
> To unsubscribe from this group, send email to
> django-users+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/django-users?hl=en.
>
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Reply via email to