CS 347 Distributed Databases and Transaction Processing Notes
CS 347: Distributed Databases and Transaction Processing Notes 04: Query Optimization Hector Garcia-Molina CS 347 Notes 04 1
Query optimization • Cost estimation • Strategies for exploring plans Q min CS 347 Notes 04 2
Cost estimation As in centralized system: estimate result sizes CS 347 Notes 04 3
But: # IOs may not be best metric e. g. , Transmission time may dominate work at site answer T 1 T 2 >>>-----TIME-----> or $ CS 347 Notes 04 4
Another reason why plain IOs not enough: Parallelism Plan A 100 IOs CS 347 Plan B site 1 site 2 site 3 Notes 04 50 IOs 70 IOs 5
• Cost metrics – IOs, Bytes transmitted, $, … – Can add together • Response time metric – cannot add – need scheduling and dependency info – skew important CS 347 Notes 04 Task 1 Task 2 Task 3 6
Take into account: (in parallel/distributed system) • Start up costs (for parallel operation) • Data distribution costs/time • Contention – memory, disk, network, … • Assembling result CS 347 Notes 04 7
Example: Response time Site 1 2 3 4 Startup Distri. Searching bution +send results CS 347 Notes 04 Final proc. 8
Searching strategies (1) Exhaustive (with pruning) (2) Hill climbing (greedy) (3) Query separation CS 347 Notes 04 9
(1) Exhaustive - consider “all” query plans with a set of techniques - prune some plans - heuristics CS 347 Notes 04 10
R Example: join A R R S 2 B S T |R|>|S|>|T| S R T S 1 T T S T R 2 (S ship S to R R) 1 T semi join (T ship T to S S) R semi join 1 Prune because cross-product not necessary 2 Prune because larger relation first CS 347 Notes 04 11
In generating plans, keep goal in mind: e. g. : Goal is parallelism in system with fast net, consider partitioning relation(s) first e. g. : Goal is reduction of net traffic, consider semi-joins CS 347 Notes 04 12
(2) Hill climbing Better plans 1 x Initial plan Worse plans CS 347 Notes 04 13
(2) Hill climbing Better plans 2 1 x Initial plan Worse plans CS 347 Notes 04 14
Example R S T Rel Site Size R 1 10 S 2 20 T 3 30 V 4 40 V A B C R S T V tuple size = 1 Goal: minimize data transmission CS 347 Notes 04 15
Initial plan: send relations to one site What site do we send all relations to? To site 1: cost=20+30+40=90 To site 2: cost=10+30+40=80 To site 3: cost=10+20+40=70 To site 4: cost=10+20+30=60 CS 347 Notes 04 16
P 0: R (1 4) S (2 4) T (3 4) Compute R CS 347 S Notes 04 T V at site 4 17
Local search • Consider sending each relation to neighbor: e. g. : 4 4 R S 1 2 1 R 2 CS 347 Notes 04 18
Assume: Size R S T Option (a) 10 S = 20 T=5 V=1 4 4 S R 20 1 2 1 cost = 30 20 R S 10 R 2 cost = 30 No savings CS 347 Notes 04 19
Option (b) 4 S R 1 10 20 S R 2 4 20 1 S cost = 30 20 2 cost = 40 Worse off! CS 347 Notes 04 20
Option (c) S 2 4 T S T 10 30 4 3 5 2 cost = 50 30 T 3 cost = 35 A Win! CS 347 Notes 04 21
Option (d) 4 4 T S 2 20 30 5 20 3 2 S cost = 50 S T 3 cost = 25 A Bigger Win! CS 347 Notes 04 22
P 1 : CS 347 P 1 a: S (2 3) =S T P 1 b: R (1 4) (3 4) compute answer at site 4 Notes 04 23
Repeat local search - Treat = S R 1 4 T as relation R vs 1 3 Notes 04 3 4 1 CS 347 4 R R 3 24
Hill climbing may miss best plan! Example: best plan could be: V PB: T (3 4) 4 ’’ =T V (4 2) 1 2 ’ R S ’= S ’ (2 1) ”= ’ R [optional] ” (1 4) Compute answer CS 347 Notes 04 T 3 T 25
Hill climbing may miss best plan! Example: best plan could be: V PB: T (3 4) 30 4 ’’ T =T V (4 2) 1 1 2 3 ’ R T S ’= S ’ (2 1) 1 Costs could be low ”= ’ R because β is very selective [optional] ” (1 4) 1 Compute answer 33 = total CS 347 Notes 04 26
(3) Query separation - separate query into 2 or more steps -optimize each step independently CS 347 Notes 04 27
Example: simple queries c e. g. : c A 2 1 c 3 R S 1. Compute R’ = A[ c 2 R] S’ = A[ c 3 S] 2. Compute J = R’ S’ CS 347 Notes 04 28
c 1. Compute R’ = A[ c 2 R] c S’ = A[ c 3 S] 2. Compute J = R’ S’ A 2 R 1 c S 3. Compute Ans = CS 347 c {[J c 1 Notes 04 2 R] [J c S]} 3 29 3
In other words: (a) Compute A values in answer (steps 1, 2) (b) Get tuples from sites with matching A values and compute answer (step 3) CS 347 Notes 04 30
Simple query - Relations have a single attribute - Output has a single attribute e. g. , J R’ S’ CS 347 Notes 04 31
Idea • Decompose query into – Local processing – Simple query (or queries) – Final processing • Optimize simple query CS 347 Notes 04 32
Philosophy • Hard part is distributed join • Do this part with only keys; get rest of data later • Simpler to optimize simple queries CS 347 Notes 04 33
Summary: Query Optimization • Cost estimation • Strategies – Exhaustive – Hill climbing – Separation CS 347 Notes 04 34
Words of wisdom “Optimization is like chess playing” i. e. , May have to make sacrifices (move data, partition relations, build indexes) for later gains! CS 347 Notes 04 35
- Slides: 35