The use of the aggregate push-down rules results in some improvement to plans for a few TPC-D queries. In order to produce plans which use aggregate push down rule 2, the cost of HASH_JOIN was increased (for the results in table 7.8 below only) to make plans with early aggregation more attractive.
Query #joins Estimated Run Time Estimated Run Time With Aggregate Pushdown Rules Without Aggregate Pushdown Rules (minutes) (minutes) 3 2 33.3 34.2 4 1 28.4 28.4 10 3 41.4 41.5 12 1 23.8 23.8 17 3 22.0 23.9 * Using left deep joins. ** Using Cost Model That Roughly Doubles the cost of a Hash Join
By disabling the aggregate push-down rules, and comparing the cost of the plan produced by the optimizer when the rule in not enabled, the effectiveness of these rules on a particular query can be observed.
The Aggregate through EQJOIN rule fires in the optimization of query 3. As can be seen from table 7.8 a plan with the aggregation pushed below one of the two EQJOIN's in query 3 results in a plan estimated to cost 1 minute less than the plan with the aggregation computed after both joins.
| 
Page 11
|