Slideshow:
Estimating the cost of a logical query plan
Recall: The best join ordering (= query plan) is the one that has the least # tuples in all the result sets:
Estimating the cost of a logical query plan
- Example
Samples of query plans with different join ordering:
Estimating the cost of a logical query plan
- Example
Samples of query plans with different join ordering:
Every query plan will read the same input relations !!!
Estimating the cost of a logical query plan
- Example
Operators that are above the last join operator are the same in all query plans:
X2 = Y2 = Z2 = C2 and X3 = Y3 = Z3 = C3 and X4 = Y4 = Z4 = C4 !
Estimating the cost of a logical query plan
- Conclusion
The cost difference between different logical query plans differs only in the size of the intermediate result set(s) of the join operations:
- Naive way to
compute the
cost
of a logical query plan:
Cost = total # of tuples in the input relations + total # tuples output by operators in logical query planExample:
- Query plans:
Cost of the query plans:
Cost of plan 1 = T(R) + T(S) + T(T) + X1 + X2 + X3 + X4 Cost of plan 2 = T(R) + T(S) + T(T) + Y1 + Y2 + Y3 + Y4 Cost of plan 3 = T(R) + T(S) + T(T) + Z1 + Z2 + Z3 + Z4
- Query plans:
- Consider the
cost in
different
logical query plans:
- Each
logical query plan will
access the
same input relations:
Observe:
Cost of plan 1 = T(R) + T(S) + T(T) + X1 + X2 + X3 + X4 Cost of plan 2 = T(R) + T(S) + T(T) + Y1 + Y2 + Y3 + Y4 Cost of plan 3 = T(R) + T(S) + T(T) + Z1 + Z2 + Z3 + Z4
- Each
logical query plan will
produce the
same output:
So that:
Cost of plan 1 = T(R) + T(S) + T(T) + X1 + C2 + C3 + C4 Cost of plan 2 = T(R) + T(S) + T(T) + Y1 + C2 + C3 + C4 Cost of plan 3 = T(R) + T(S) + T(T) + Z1 + C2 + C3 + C4
- Each
logical query plan will
access the
same input relations:
- Conclussion:
- The cost difference between
different
logical query plans
differs
only
in the size of
the
intermediate result set(s)
of the join operations:
- The cost difference between
different
logical query plans
differs
only
in the size of
the
intermediate result set(s)
of the join operations:
- Simplifying the
cost computation of
logical query plans:
- When we compare
2 different
logical query plans,
we can use:
- Cost of logical query plan = the size (= # tuples) of the intermediate results of the join operations
- When we compare
2 different
logical query plans,
we can use: