Proactive Re-Optimization: Techniques and Plans for Efficient Query Processing, Slides of Database Management Systems (DBMS)

Download Proactive Re-Optimization: Techniques and Plans for Efficient Query Processing and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Proactive Re-Optimization

Overview

• What’s wrong with reactive?

• Proactive via 3 core techniques

• Experiments

Single-Point Limitation

A:

B:

Limited Information for Re-opt

select from R, S, T where

R.a=S.a and S.b=T.b and

R.c>K 1 and R.d=K 2

0

20

40

60

80

100

120

140

160

180

200

R S T

σ(R) act

! σ(R) est

!^!

Bounding Boxes

• “Representing Uncertainty in Statistics”

• Are the upper and lower bounds for each

estimated statistic

Bounding Boxes

Robust Plan

• 1 plan is, or close to,

optimal for all 3 points

• 1 plan can be safely

chosen

Robust Plans

0 50 100 150 200 250 300 350

Switchable Plan

• There is a plan with close to

optimal cost plan at each

point

• Additional Requirements
  • The decision can be deferred
  • Actual statistics lie must

within bounding box

• It is possible to switch

between the plans

Switchable

0 50 100 150 200 250 300 350

Collecting statistics

1. Each operator collects some % in buffer

2. The eos(f) is emitted & statistics are calculated

3. Plan is chosen from switch plan members or re-

optimization is run

4. Query processing proceeds

Questions

• Prevalence of switchable plans vs. case 4

• How good is Rho at preventing re-

optimizations

• How is Rho affected by large # estimates

2-Way Join Queries: Robust

0

40

80

120

160

200

240

280

320

360

400

440

A C

σ(A) est

2-Way Join Queries: Switchable

0

40

80

120

160

200

240

280

320

360

400

440

A C

σ(A) est σ(A) b. box

Pt |σ1(A)| TRAD VRO Rio Opt

A 6 MB P17a Inside range, P17a Outside box, re-optimize, P17a P17a

B 80 MB P17a Inside range, P17a Inside box, P17a P17a

C 160 MB P17a Outside range, re- optimize, P17d

Inside box, P17d P17b

D 310 MB P17a Outside range, re- optimize, P17d

Outside box, re-optimize, P17b P17b