Here's my query:
# explain select m.*,
ac, plus_to_ac, plus_to_dx, plus_to_ma
from minions m join
(select minion_id,
sum(base_ac + plus_to_ac) as ac,
sum(plus_to_ac) as plus_to_ac,
sum(plus_to_dx) as plus_to_dx,
sum(plus_to_ma) as plus_to_ma
from armor_mv a group by minion_id) as a
on m.id = a.minion_id
where m.party_id = 12;
Hash Join (cost=22.98..2995.90 rows=1 width=88)
-> Subquery Scan a (cost=0.00..2952.07 rows=2779 width=20)
-> Aggregate (cost=0.00..2952.07 rows=2779 width=20)
-> Group (cost=0.00..2674.16 rows=27790 width=20)
-> Index Scan using armor_mv_m_i on armor_mv a (cost=0.00..2604.69 rows=27790 width=20)
-> Hash (cost=22.97..22.97 rows=5 width=52)
-> Index Scan using minions_by_party on minions m (cost=0.00..22.97 rows=5 width=52)
What's happening apparently is it first "materializes" the subquery, THEN narrows it down to the particular minions I'm interested in. If OTOH I do a simple subselect,
# explain select m.*,
(select sum(base_ac) from armor_mv a where a.minion_id = m.id)
from minions m
where m.party_id = 12;
Index Scan using minions_by_party on minions m (cost=0.00..22.97 rows=5 width=52)
SubPlan
-> Aggregate (cost=498.58..498.58 rows=1 width=4)
-> Index Scan using armor_mv_m_i on armor_mv a (cost=0.00..498.23 rows=139 width=4)
It does it in the 'correct' order. Analyze shows the 2nd is an order of magnitude faster than the first, and I can indeed get around this by doing four subselects, but that is ugly. Any way to get the first query to plan more intelligently?
