---

Using equi-width histograms to estimate result set size of join operations

---

---

---

 

Slideshow:

Estimating join result size using an equi-width histogram - Problem description
Input relations:

Statistical data on each relation:

Equi-width histogram data on each relation:

Problem description: given the estimated # tuples in the result set of this query:

Estimating join result size using an equi-width histogram - Worked out example
Equi-width histogram data on each relation:

Case 1: temperature range 40 - 49

Estimating join result size using an equi-width histogram - Worked out example
Equi-width histogram data on each relation:

Case 2: temperature range 50 - 59

Estimating join result size using an equi-width histogram - Worked out example
Equi-width histogram data on each relation:

Other cases and final solution:

Comparing with the "base line" estimation result
Result obtained using equi-width histogram:
 
Result obtained using statistical data on relations:

❮ ❯



---

---

---

---

• Using equi-width histogram
  • Fact:

    Equi-width histograms are ideally (= simple) suited for estimating the result of joins

  • Problem description:

    JanTemp = JanTemp(day, temp) JulTemp = JulTemp(day, temp) Statistics on the relations: JanTemp JulTemp ================================================= T(JanTemp) = 245 T(JulTemp) = 245 V(JanTemp,Temp) = 100 V(JulTemp.Temp) = 100 Estimate the size of: SELECT JanTemp.day, JulTemp.day FROM JanTemp, JulTemp WHERE JanTemp.Temp = JulTemp.Temp (Days in Jan and Jul that has same temperature)

  • Histogram information on the input relations:

    Range JanTemp JulTemp ---------+---------+--------- 0 - 9 | 40 | 0 10 - 19 | 60 | 0 20 - 29 | 80 | 0 30 - 39 | 50 | 0 40 - 49 | 10 | 5 50 - 59 | 5 | 20 60 - 69 | 0 | 50 70 - 79 | 0 | 100 80 - 89 | 0 | 60 90 - 99 | 0 | 10

    ---

    ---

  • Estimate the result of R(a,b) ⋈ S(b,c):

    Case 1: 40 ≤ Temp ≤ 49 JanTemp: 10 tuples has values in 40, 41, 42, ...., 49 (10 values) JulTemp: 5 tuples has values in 40, 41, 42, ...., 49 (10 values) # tuples joined: 10 × 5 (= 50 tries) Probability of success = 1/10 (A tuple x ∈ JanTemp has 1/10 chance to be joined with a tuple y ∈ JulTemp that has the same temperature value) # tuples in join result = 10 × 5 × 1/10 = 5 tuples Case 2: 50 ≤ Temp ≤ 59 JanTemp: 5 tuples has values in 50, 51, 52, ...., 59 (10 values) JulTemp: 20 tuples has values in 50, 51, 52, ...., 59 (10 values) # tuples joined: 5 × 20 (= 100 tries) Probability of success = 1/10 (A tuple x ∈ JanTemp has 1/10 chance to be joined with a tuple y ∈ JulTemp that has the same temperature value) # tuples in join result = 5 × 20 × 1/10 = 10 tuples Case 3: other Temp There are no common values in the other ranges Therefore: T(JanTemp ⋈ JulTemp) ~= 5 + 10 = 15 tuples

    ---

    ---

    ---

    ---

  • Compare: estimate without using histogram information

    Statistics on the relations: JanTemp JulTemp ================================================= T(JanTemp) = 245 T(JulTemp) = 245 V(JanTemp,Temp) = 100 V(JulTemp.Temp) = 100 T(R) × T(S) T(R⋈S) ~= ------------------- max(V(R,b), V(S,b)) 245 × 245 ~= ------------- max(100, 100) = 60025/100 ~= 600 600/15 = 40 (4000% error !!!)