Model D plans,
examples of improvements in plans
due to the addition of certain rules
can be shown.
Aggregate Push Down
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.
Table 7.8: Cost of Plans Produced With and Without Aggregate Pushdown
Query #joins Estimated Run Time Estimated Run Time
With Aggregate Pushdown Rules Without Aggregate Pushdown Rules
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,
the cost of the plan produced by the optimizer
when the rule
in not enabled,
of these rules
on a particular query
can be observed.
Aggregate through EQJOIN
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.