A grouping performance problem

By now I’ve come across this problem at least thee times, each time I needed to build a relatively big rollup table, which would use a join of about ten tables, some of which may have several million rows, and others – several hundred thousand rows. Each time I needed to run something like this:


select
t1.a
,t1.b
,t2.a2
,t2.b2
,t3.a3
,t4.b4
,sum(t1.c) as c_sum
,max(t1.d) as d_max
,sum(case t1.e='a' then 1 else 0 end) as sum_a
from t1
join t2 (using join_id2)
join t3 (using join_id3)
join t4 (using join_id4)
group by t1.a
,t1.b
,t2.a2
,t3.a3
,t2.b2


In all cases the table t1 will be the only table with the “real” aggregation, and for the rest of the tables there would be exactly one line for each of the aggregated line of t1.

The problem: this query would run… forever.

Now, if I just separate the “aggregation” part:


select
t1.a
,t1.b
,sum(t1.c) as c_sum
,max(t1.d) as d_max
,sum(case t1.e='a' then 1 else 0 end) as sum_a
from t1 group by t1.a, t1.b


This one will run really fast, even though the table t1 would be the biggest table. Let’s add the rest of the tables to the join:


select
t1.a
,t1.b
,sum(t1.c) as c_sum
,max(t1.d) as d_max
,sum(case t1.e='a' then 1 else 0 end) as sum_a
from t1
join t2 (using join_id2)
join t3 (using join_id3)
join t4 (using join_id4)
group by t1.a, t1.b


This will run just a little bit slower, than the “aggregate only”. But then…

I would imagine, after all joins and groupings the rest simply does not matter, because “everything is there already”, and all data is already read from the database. But the moment I start to add these “singular” columns to the select list and group by clause, it starts to break:


select
t1.a
,t1.b
,t2.a2
,t2.b2
,sum(t1.c) as c_sum
,max(t1.d) as d_max
,sum(case t1.e='a' then 1 else 0 end) as sum_a
from t1
join t2 (using join_id2)
join t3 (using join_id3)
join t4 (using join_id4)
group by t1.a
,t1.b
,t2.a2
,t2.b2


This will run visibly slower… this one:


select
t1.a
,t1.b
,t2.a2
,t2.b2
,t3.a3
,sum(t1.c) as c_sum
,max(t1.d) as d_max
,sum(case t1.e='a' then 1 else 0 end) as sum_a
from t1
join t2 (using join_id2)
join t3 (using join_id3)
join t4 (using join_id4)
group by t1.a
,t1.b
,t2.a2
,t3.a3
,t2.b2


even more slower… till it just will run forever…

The work around is easy – to materialize the “aggregated” piece. But I still do not think, it’s a correct behavior… I have no idea, why it does what it does…