Re: Why does adding SUM and GROUP BY destroy performance?

Christopher Browne wrote:
> In the last exciting episode, dvlink(at)yahoo(dot)com (David Link) wrote:
>
>>Why does adding SUM and GROUP BY destroy performance?
>
>
> When you use SUM (or other aggregates), there are no short cuts to
> walking through each and every tuple specified by the WHERE clause.

Er... not in this case, if I read David's email correctly.

His first query is walking through every tuple anyway.
His second query is the one summing them up, AFTER, here's the critical
part, GROUPing them by t.tid.

I suspect 7.4 (now in beta), or rewriting the query for <7.4 would speed
thing up. 7.4's Hash Aggregate would be the winner here.

As for rewriting this, David, try:

SELECT t.tid, t.title,
(select the stuff you want from lots of tables where something = t.tid)
FROM
title t;

Doubt it'll be as fast as using 7.4 though.

--
Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386
GNU/Linux
3:00pm up 267 days, 6:26, 4 users, load average: 5.44, 5.26, 5.17