Re: Slow query. Any way to speed things up?

Hello Matthias,

2011/3/29 Matthias Kestenholz <mk@spinlock.ch>

On Tue, Mar 29, 2011 at 2:27 PM, Fabian Büchler

<fabian.buechler@gmail.com> wrote:

> 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.
>

only() does not affect the generated query in any way -- it would be a
bug if it did.

only() does affect the selected columns, but of course that can never affect the rows that will be selected.
 

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

There's already a ticket for the duplicated GROUP BY columns in the
Django ticket tracker:

http://code.djangoproject.com/ticket/15709

Thanks for this insight. The bug is already fixed but not yet checked in. Good to know.

 

> 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?
>

Not sure about that. I'd say it isn't a bug because this could
potentially cause different results depending on the table and table
content.

Interesting thought. I guess that might be true, although I'm not quite enough SQL-sawy to be able to evaluate if that is correct.

In my case the massive GROUP BY clause caused a pretty severe impact on query speed.
But by now I've fixed the problem by just using the QuerySet.values() method instead of .only(). That works just fine and fast.
 

Matthias

Regards,
Fabian

 

--
Django CMS building toolkit: http://www.feinheit.ch/labs/feincms-django-cms/

--
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+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.