- User writes SQL query Q, say σ (R⋈ S), or R⋈ S⋈ T.
- Query Optimization (basic idea)
- Enumerate all possible execution plans. (Problem: too many plans)
- Choose the fastest one.
- Problem: don't know how fast they are.
- Approaches
- cost model
- data statistics stored in the catalogs
- Relation: cardinality, size in pages.
- Index: cardinality (# distinct keys), size in pages, height, range.
- Catalogs update periodically: can be slightly inconsistent
- streaming db
- The same terms with different names
- Academia: relation and tuples
- Industry: table and rows
- Query proceesing phases
- Parsing
- Optimization based on predicates and access path
- Code generation
- Execution
- access path
- segment scan (relation scan)
- index scan (table sorted in index)
- clustered index
- tuples in segment pages are stored in the index ordering.
- Both index and data pages access once.
- Only one clustered index per table
- unclustered index
- clustered index
- Join methods
- nested loops
- sort merge
- Architecture
- RDS (the System R)
- RSI (the Research Storage Interface)
- RSS (the Research Storage system): manages buffer pools
- A SARGable (Search ARGuments) predicate is one of the form “ attribute operator value ” generalized to boolean expressions of these predicates.
- Push to buffer pools: R.a > 7 and R.b = 17 (the same row), R.a > 7, or S.b = 6
- Cannot: R.a = S.b (join)
- A predicate that “ matches ” an index if the columns in the predicate are the leftmost prefix of the index key.
- Suppose R(A, B, C, D, E, ...), and Index on A, B, and C in R.
- Works for predicates like A = 7, or A = 7 ∧ B = C.
- Does not work for predicates like B = 6.
- An ordering of tuples is “ interesting ” if it is needed by a Group By, Order By, or join column/predicate.
- Pick up the cheapest one on an “ interesting ” order, instead of just calculating the single best one-table plan for each table in the query.
- This is really an attempt to mitigate the errors a greedy optimization algorithm can make. If one only remembers the cheapest plan at each level, one might miss plans where a single table access is slower than the best option for that table, but the overall cost is good because the slower single table access results in tuples ordered in a way needed further up the tree. Plans that keep track of “ interesting ” orders avoid this problem.
- Join results may or may not in order.
- Pick up the cheapest one on an “ interesting ” order, instead of just calculating the single best one-table plan for each table in the query.
- Cost of query = (# of page fetches) + w * (# of RSI calls)
- Proxy for both I/O costs and CPU utilization.
- w: weighting factor between I/O and CPU.
- Small for fast CPU
- Big for slow one.
- RSI calls
- The predicated # of tuples returned from the RSS.
- Lower if using SARGable predicates.
- LRU is bad for query.
- Statistics: cost of operations + result size
- NCARD(T): # tuples in T.
- TCARD(T): # pages containing tuples of T.
- P(T) = TCARD(T) ⁄ (# of non-empty pages in segment). NOT interesting.
- ICARD(I) = numbers of distinct keys in I. INTERESTING.
- NINDX(I) = # pages in index I.
| Case | Cost |
|---|---|
| Unique index matching equality predicate | 1 + 1 + w: one index page, one data page, and one RSI call |
| Clustered index I matching some predicates | F(preds) * (NINDX(I) + TCARD(T)) + w * RSICARD: depend on data |
- calculate the selectivity factor F for each boolean factor in the predicate list.
- Select # of columns from T1, T2, ..., Tk where preds: (|T1|, |T2|, ..., |Tk|) * (selectivity of preds).
- Statistics of data for whether a value stores using index or not.
- Query cardinality: QCARD = |R1| * |R2| * ... * |Rn| * (selectivity of all preds combinations)
- For R1⋈ R1.A = R2.BR2, QCARD = |R1| * |R2| * (selectivity of R1.A = R2.B)
- RSICARD = |R1| * |R2| * ... * |Rn| * (selectivity of all SARGable predicates)
- σR1.A = 7 R1⋈ R1.B = R2.CR2
- P1: R1.A = 7
- P2: R1.B = R2.C
- QCARD = |R1| * |R2| * (selectivity of P1) * (selectivity of P2)
- RSICARD = |R1| * |R2| * selectivity(P1)
- P2 put high level, P1 for optimization
- DB2's least resource used model (force plan)
- Predicate on R.a = 7, index exists
- Optimizer is not chosing it, but it shalls, instead of table scan.
- Use index: (R.a = 7) ∧ (R.a = 7) because F = 0.1 * 0.1 = 0.01.
- Avoid index: (R.a ≥ 7) ∧ (R.a ≤ 7)
Predicate Selectivity Estimate attr = value (i.e., R.a = 7) F = 1 / ICARD(attr index), if index exists (assume uniformity of a distribution among the idnex key values) F = 1 / 10, o.w., (good to avoid horrible execution plans) value1 < attr < value2 F = (value2 - value1) / (high key - low key), if index exists F = ¼, o.w. expr1 AND expr2 F = F(expr1) * F(expr2), (assume independence of column values) expr1 OR expr2 F = F(expr1) + F(expr2) - F(expr1) * F(expr2), (assume independence of column values) - For each relation, calculate the cost of scanning the relation for each index & by segment scan.
- What is produced?
- cost C in terms of # page fetched + w * RSICARD
- ordering of tuples if any
- choose table scan if unclustered index exists and large range predicates.
- What is produced?
- For each pair of relations, calculate the best way to join them (for each interesting order) among all available access methods (for each relation in FROM clause) and all join permutations of left-deep join trees
- Find the best way to join one more relation to the result of step 3) until finishing the whole query.
- A bushy tree outperforms any linear “ deep ” tree, which may require the materialization of a big intermediate result.
- Nested Loop Join uses left-deep tree by pipeline, but for right-deep tree waits for the right branch.
- Hash join using righ-deep tree by streaming everything, but for left-deep tree does not by pipelining
- Sementic join uses bushy tree?
- System R uses only left-deep trees because left-deep trees can be pipelined but right-deep trees cannot, & does not do cross products unless it is unavoidable.
- System R cannot do every possibility → reduce search space by using the property that once the first k relations are joined, the method to join the composite to the k+1-st relation is independent of the order of joining the first k; i.e. the applicable predicates, the set of interesting orderings, or the possible join methods, are the same.
- ''Star'' Schema and hierarchy tables
- Schema
- Sales(prodId, storeId, dateId, qty)
- dimensions: prodId, storeId, and dateId.
- measure: qty.
- prod(prodId, prodClass, superClass, ...)
- geo(storeId, State, Region, ...)
- date(dateId, week, month, quarter, ...)
- Sales(prodId, storeId, dateId, qty)
- Explanation
- prod, geo, and data have cross-product.
- Left-deep tree produces big joins during each step.
- By adding a dummy column with the same value in the prod, geo, and data schema, left-deep tree would produce results fast.
- Hash join uses right-deep tree by stream processing and produces results fast.
- Queries
- Sales by storeId
- Sales by dateId
- Schema
- Snowflake Schema
- Cubic
- Schema: F(A, B, C, qty)
- Query: SELECT sum(qty) from F group by
- A
- B
- C
- AB
- AC
- BC
- ABC: the origin table
- ⊥: sum of the whole table
- histograms used in commercial systems: more accurate estimates
- equi-width histogram
- equi-depth histogram
- fine-granularity
- “anti-bias”
- extreme values
- Update stats: done by sampling
- An example
- R⋈ S without hash join
- Index nested loops build index
- 1000 ⋈ 1000: cost of 1000 with INL using 10 sec
- 10000 ⋈ 10000: cost of 10000 with INL using 120 sec
- 100000 ⋈ 100000: cost of -654437... with NL using ''infinite'' time (longer than 2 days) to finish
- Why was not thus great?
- Only learn about (possibly) bad plans. Good plan may be expensive, and it learns the plan that has actually executed.
- End up comparing estimate (overestimate with bias) to real & get in trouble; may not get better.
- Query optimizers make decisions based on their estimates of the relative costs of two plans.
- But it is possible that these estimates can be in error in a consistent way; that is, the estimate for P1 is too low, the estimate for P2 is too low, but the optimizer is right that P1 is faster than P2.
- If one were able to magically “ reach in ” and convert some of the cardinality estimates to P1's true, exact values, the optimizer might incorrectly assume that P2 is faster than P1.