Cost-Based Optimization Database Systems: The Complete Book Ch 2.3, - - PowerPoint PPT Presentation

▶
cost based optimization
SMART_READER_LITE
LIVE PREVIEW

Cost-Based Optimization Database Systems: The Complete Book Ch 2.3, - - PowerPoint PPT Presentation

May 03, 2023 351 likes •955 views

Cost-Based Optimization Database Systems: The Complete Book Ch 2.3, 6.1-6.4,15, 16.4-16.5 1 Optimizing 2 Optimizing Some equivalence rules are always good Which? 2 Optimizing Some equivalence rules are always good

slide-1
SLIDE 1

Cost-Based Optimization

Database Systems: The Complete Book Ch 2.3, 6.1-6.4,15, 16.4-16.5

1

slide-2
SLIDE 2

Optimizing

2

slide-3
SLIDE 3

Optimizing

  • Some equivalence rules are always good…
  • Which?

2

slide-4
SLIDE 4

Optimizing

  • Some equivalence rules are always good…
  • Which?
  • Some equivalence rules are sometimes good
  • Which?
  • What do we do about it?

2

slide-5
SLIDE 5

Cost Estimation

  • Compare many different plans by…
  • … actually running the query
  • … estimating the plan’s “cost”

3

slide-6
SLIDE 6

Cost Estimation

4

slide-7
SLIDE 7

Costs

5

slide-8
SLIDE 8

Costs

  • Memory Cost (Working Set Size)

5

slide-9
SLIDE 9

Costs

  • Memory Cost (Working Set Size)
  • Compute Cost (“Big-O”)

5

slide-10
SLIDE 10

Costs

  • Memory Cost (Working Set Size)
  • Compute Cost (“Big-O”)
  • IO Cost (Pages read, Pages written)

5

slide-11
SLIDE 11

6

The variable in all of these costs is the arity (size) of a relation.

slide-12
SLIDE 12

How do you compute Arities?

  • Heuristic Assumptions (Pick a “good enough” RF)
  • Summary Statistics About The Data…
  • Upper/Lower Bounds or Value Domains
  • Distribution Summaries (Histograms)
  • Data Sampling

7

slide-13
SLIDE 13

How do you compute Arities?

8

There is no perfect solution (yet)!

slide-14
SLIDE 14

How do you compute Arities?

8

There is no perfect solution (yet)! We don’t need a perfect solution… … we just need one that’s good enough

slide-15
SLIDE 15

Summary Statistics

  • Per-Attribute Bounds / Domain Statistics
  • Assume a Uniform Distribution.
  • Per-Attribute Histograms
  • Use the histogram to model the data distribution
  • Data Samples
  • Use the samples to measure the RF

9

slide-16
SLIDE 16

Uniform Distribution

10

A = 1

slide-17
SLIDE 17

Uniform Distribution

10

A = 1

Chance of Hit = 1 / # of distinct values of A

slide-18
SLIDE 18

Uniform Distribution

11

A ∈ (1, 2, . . .)

slide-19
SLIDE 19

Uniform Distribution

11

A ∈ (1, 2, . . .)

Chance of Hit = | (1,2,3,…) | / # of distinct values of A

slide-20
SLIDE 20

Uniform Distribution

12

A < 3

slide-21
SLIDE 21

Uniform Distribution

12

A < 3

Chance of Hit = 3-Low(A) / High(A) - Low(A)

slide-22
SLIDE 22

Uniform Distribution

13

. /R.A=S.B

slide-23
SLIDE 23

Uniform Distribution

13

. /R.A=S.B

Chance of Hit Per B = 1 / # Distinct Values of A Chance of Hit Per B = 1 (If B is a FK Reference) Chance of Hit Per A = 1 (If A is a FK Reference)

slide-24
SLIDE 24

Let’s apply it

14

SELECT O.Rank, COUNT(*), FROM Officers O WHERE O.Rank >= 2 AND O.Age > 20 AND O.Age < 30 GROUP BY O.Rank HAVING COUNT(DISTINCT O.Ship) > 2

What is the relational algebra plan for this expression?

slide-25
SLIDE 25

Stats

15

O.Rank: 0-5 (Increments of 0.5; 11 total values) O.Age: 16-100 (Increments of 1; 85 total values) Officers: 40,000 tuples (over 500 pages) Tree Indexes available over O.Age, O.Rank What is the total cost in IOs? What is the total cost in CPU/Tuples?

slide-26
SLIDE 26

Histograms

16

Uniform Distributions are a strong assumption! (data is often skewed)

slide-27
SLIDE 27

Histograms

17

People Name Age Rank <“Alice”, 21, 1 > <“Bob”, 20, 2 > <“Carol”, 21, 1 > <“Dave”, 19, 3 > <“Eve”, 20, 2 > <“Fred”, 20, 3 > <“Gwen”, 22, 1 > <“Harry”, 20, 3 > SELECT Name FROM People WHERE Rank = 3 AND Age = 20

RFAge = 1/nkeys = 1/4

… AND Age = 19

RFRank = 1/nkeys = 1/3 Age is best! vs

slide-28
SLIDE 28

Histograms

18

People Name Age Rank <“Alice”, 21, 1 > <“Bob”, 20, 2 > <“Carol”, 21, 1 > <“Dave”, 19, 3 > <“Eve”, 20, 2 > <“Fred”, 20, 3 > <“Gwen”, 22, 1 > <“Harry”, 20, 3 > SELECT Name FROM People WHERE Rank = 3 AND Age = 20

RFAge-20 = 1/2

… AND Age = 19

RFRank = 1/3 Age is worst! vs

slide-29
SLIDE 29

Histograms

19

People Name Age Rank <“Alice”, 21, 1 > <“Bob”, 20, 2 > <“Carol”, 21, 1 > <“Dave”, 19, 3 > <“Eve”, 20, 2 > <“Fred”, 20, 3 > <“Gwen”, 22, 1 > <“Harry”, 20, 3 > SELECT Name FROM People WHERE Rank = 3 AND Age = 20

RFAge-19 = 1/8

… AND Age = 19

RFRank = 1/3 Age is best! vs

slide-30
SLIDE 30

Histograms

20

19 21 20 22 1 2 4 1

slide-31
SLIDE 31

Histograms

21

19 21 22 1.5 2.5

slide-32
SLIDE 32

Histograms

22

19 22 2

slide-33
SLIDE 33

Histograms

23

50 20 70 10 30 40 60 80 SELECT … WHERE A = 33 10 10 63 22 30 15 20

slide-34
SLIDE 34

Histograms

24

50 20 70 10 30 40 60 80 SELECT … WHERE A > 33 10 10 63 22 30 15 20

slide-35
SLIDE 35

Using Constraints

  • A Key attribute has one distinct value per row

(equality selects exactly one row)

  • Foreign Key joins generate one row for each row in

the referencing relation.

  • Cascade relationship guarantees EXACTLY one

row per reference.

25

slide-36
SLIDE 36

Sampling

  • Take a bunch of tuples from each relation.
  • Run 2-3 different query plans on these tuples.
  • Estimate the sampling factors for each operator

in the plan based on how many survive.

26

slide-37
SLIDE 37

Sampling

27

How big is a “bunch?”

slide-38
SLIDE 38

Sampling

  • Problem: Very Selective Predicates
  • Problem: Joins and the Birthday Paradox
  • Problem: Counting Aggregate Groups

28

slide-39
SLIDE 39

Very Selective Predicates

29

R S T

[100 Tuples]

slide-40
SLIDE 40

Very Selective Predicates

30

R S T

[100 Tuples]

slide-41
SLIDE 41

Very Selective Predicates

30

R S T

[0 Tuples]

slide-42
SLIDE 42

Join Conditions

31

Image: Wikipedia

slide-43
SLIDE 43

Join Conditions

31

Birthday Paradox Need O(√|R|+|S|) tuples to reliably guess RF for equijoin

Image: Wikipedia

slide-44
SLIDE 44

Estimating Join Costs

32

R S T U . / . / . /

How many query plans are there?

slide-45
SLIDE 45

Estimating Join Costs

33

There are (N-1)! (factorial) different ways (plans) to evaluate this join. Computing costs for all of these plans is expensive!

slide-46
SLIDE 46

Left-Deep Plans

34

R S T U . / . / . /

RHS Join Input is always a relation 1) Shrinks join search space 2) Allows index scans/lookups

Technique Pioneered by the System R Optimizer

slide-47
SLIDE 47

In Practice

35

Heuristics, Histograms and Sampling are “good enough” to optimize the common cases.

slide-48
SLIDE 48

In Practice

35

Heuristics, Histograms and Sampling are “good enough” to optimize the common cases. Some relational databases have manual overrides.

slide-49
SLIDE 49

Oracle

36

SELECT /*+ INDEX (employees emp_department_ix)*/ employee_id, department_id FROM employees WHERE department_id > 50;

slide-50
SLIDE 50

Postgres

37

SELECT attname, inherited, n_distinct, array_to_string(most_common_vals, E'\n') as most_common_vals FROM pg_stats WHERE tablename = 'road'; attname | inherited | n_distinct | most_common_vals

  • --------+-----------+------------+------------------------------------

name | f | -0.363388 | I- 580 Ramp+ | | | I- 880 Ramp+ | | | Sp Railroad + | | | I- 580 + | | | I- 680 Ramp name | t | -0.284859 | I- 880 Ramp+ | | | I- 580 Ramp+ | | | I- 680 Ramp+ | | | I- 580 + | | | State Hwy 13 Ramp

slide-51
SLIDE 51

In Practice

38

Heuristics, Histograms and Sampling are “good enough” to optimize the common cases.

slide-52
SLIDE 52

In Practice

38

Heuristics, Histograms and Sampling are “good enough” to optimize the common cases. Some relational databases have manual overrides.

slide-53
SLIDE 53

In Practice

38

Heuristics, Histograms and Sampling are “good enough” to optimize the common cases. Some relational databases have manual overrides. All relational databases have an “EXPLAIN” operator

slide-54
SLIDE 54

Postgres

39

EXPLAIN SELECT sum(i) FROM foo WHERE i < 10; QUERY PLAN

  • Aggregate (cost=23.93..23.93 rows=1 width=4)
  • > Index Scan using fi on foo (cost=0.00..23.92 rows=6 width=4)

Index Cond: (i < 10)

slide-55
SLIDE 55

Backup Slides

40

slide-56
SLIDE 56

Join Algorithm Comparison

41

Hybrid Hash Index Nested Loop (Block) Nested Loop Hash Join Sort/Merge Join Can Support Pipelining?

RHS Hash Table needs to fit in memory

Yes

LHS and RHS must both be sorted on the join key

No But? Yes

RHS Table needs an index on the join key

Yes

RHS Table needs to fit in memory

Yes

No buts. Hash Join always materializes

slide-57
SLIDE 57

Join Algorithm IO Costs

42

Hybrid Hash Index Nested Loop Nested Loop Hash Join Sort/Merge Join

R . / S

[#pages of S] (if fits in mem) |R| * [cost of one scan/lookup on S] [#pages of S] (+sorting costs) [#pages of S] (if fits in mem)

IO Cost Block Nested Loop

[#pages of R] + [#of block pairs] * ([#pages per block of R]+[#pages per block of S])

2*([#pages of R]+[#pages of S]) + [#pages of S]

slide-58
SLIDE 58

Data Access IO Costs

43

Raw File Static Hash Index Linear Hash Index Extendible Hash Index Sorted File ISAM Tree Index B+ Tree Index Full Scan Range Scan Lookup

N N N N log2(N)+|R| log2(N) >N >N ~1 >N+|D|

(random)

>N+|D|

(random)

2 >N >N ~1 ~N ~log|T|(N)+|R| ~log|T|(N) N

(random)

log|T|(N)+|R|

(random)

log|T|(N)