Example: Before and After Optimization
If statistics are available on a column referenced in a WHERE clause, the query optimizer uses the information to choose the most efficient QEP. Understanding how this information is used can be helpful in analyzing query performance. For more information, see
Query Execution Plans.
Two QEPs showing the effect of optimization are presented here. The first is a QEP before optimizing; the second shows the same query after optimization. The query used is a join, where both the r and s tables use the B-tree storage structure:
select * from r, s
where s.a > 4000 and r.a = s.a;
QEP Before Optimization
Before obtaining statistics, the optimizer chooses a full sort-merge (FSM) join, because it assumes that 10% of each table satisfies the qualification “a > 4000,” as shown in the QEP diagram below:
QUERY PLAN 4,1, no timeout, of main query
FSM Join(a)
Heap
Pages 1 Tups 267
D15 C44
/ \
Proj-rest Proj-rest
Sorted(a) Sorted(a)
Pages 5 Tups 1235 Pages 1 Tups 267
D11 C12 D4 C3
/ /
r s
B-Tree(a) B-Tree (a)
Pages 172 Tups 12352 Pages 37 Tups 2666
QEP After Optimization
After obtaining statistics, the optimizer chooses a Key join, because only one row satisfies the qualification “a > 4000,” as shown in the QEP diagram below:
QUERY PLAN 4,1, no timeout, of main query
K Join(a)
Heap
Pages 1 Tups 1
D4 C1
/ \
Proj-rest r
Sorted(a) B-Tree(a)
Pages 1 Tups 1 Pages 172 Tups 12352
D2 C1
/
s
B-Tree(a)
Pages 37 Tups 2666
The cost of the key join is significantly less than the cost of the FSM join because the join involves far fewer rows.