---

The cost of a logical query plan

---

---

---

 

Slideshow:

Finding the best query plan - how to compare different query plans
 
Pre-requisite to any optimization problem:
 
We say that a query plan (relational algebra expression) A is better than another query plan (relational algebra expression) B if:
cost(A) < cost(B)
Question: what cost measure should we use ?
How to measure the cost of a logical query plan
 
Comment:
Disk IO cost is computed at the implementation (= algorithm) level. At the logical query plan level, we are re-writing the relational algebra expressions without considering the implementation algorithm I.e.: we are not considering whether to use 1-pass, 2-pass, etc, algorithms We use the result set size because this measure can reflect (= good approximation for) the # disk IO needed
Example to illustrate the cost of a (logical) query plan
Question: which query plan has the smallest # disk IOs ?

Example to illustrate the cost of a (logical) query plan
Question: which query plan has the smallest # disk IOs ?

Problem that we need to solve to perform query optimization
 
Pre-requisite to query optimization: cost estimation !!!

❮ ❯



---

---

---

---

• The cost of a logical query plan
  • Fact:

    Before we can improve a query plan: We must have a measure to determine the cost of a logical query plans

    ---

    ---

  • Measuring the cost of logical query plans:

    The ultimate cost measure is: The execution time (= #disk IOs performed) of the query plan              A good approximation of the # disk IOs measure is: The size (= # tuples) of the result produced by an operation

  ---

  ---

  ---

  ---

• Comparing the costs of different query plans
  • Consider the following 2 query plans:

    Query plan 1: Query plan 2:

    Question:

    Which query plan is better ???? I.e.: which plan will have a lower number of disk IO operations ???

    Answer:

    The answer to the question is determined by: The # tuples (~= size) that are accessed by the logical query plan: because: The size (= #tuples) will be proportional to the # disk IOs need to access the tuples

  ---

  ---

  ---

  ---

• Missing knowlegde that we must develop to compute/estimate the cost of a logical query plan

  • We do we need to enable us to compute/estimate the cost of a logical query plan:

    We need a method to compute (estimate) the size of the intermediate results of the relational operators on the logical query plan: