cost based query optimization christoph koch computing
play

Cost-based Query Optimization Christoph Koch Computing the cost of - PowerPoint PPT Presentation

Apr 10, 2023 •251 likes •760 views

Cost-based Query Optimization Christoph Koch Computing the cost of a query plan We know how to estimate the cost of each individual operator. But for doing this we need to know the size of the input. Compute the size of each relation

  1. Cost-based Query Optimization Christoph Koch

  2. Computing the cost of a query plan � We know how to estimate the cost of each individual operator. � But for doing this we need to know the size of the input. � Compute the size of each relation produced by some operator � Bottom-up � We need to know estimates of selectivities/reduction factors for both selection and join conditions. � Given a fixed query plan, if we exchange a particular operator implementation (e.g. NL join against Hashjoin), the output does not change and we do not have to recompute output sizes. 2

  3. Model of Query plans � We use a very powerful model. � Ingredients: � Algebra tree � For each operation, a choice of implementation � For some binary operations (e.g. NL joins), an annotation saying which of the two inputs is the outer and which is the inner loop. � A choice of whether an operator’s output is to be written back to disk or pipelined into the next operator (sometimes there is no choice). � An allocation of memory buffer pages to operators and their input/output lines. � In case of pipelining, the allocation may not be operator by operator but span several operations. 3

  4. A Remark on the cost function for block NL joins � On this slides I use the formula pages_outer + round_up(pages_outer / (buffer_pages – 1)) * (pages_inner - 1) + 1 � I explained this formula in class but it’s not in the book. � You can use the formula from the book instead: pages_outer + round_up(pages_outer / (buffer_pages – 1)) * pages_inner 4

  5. A University Database Professor (p) id name grad room 2125 Socrates Full 226 Takes (h) 2126 Russel Full 232 sid cid Course (v) 2127 Kopernikus Assoc 310 26120 5001 cid name credits taught_by 2133 Popper Assoc 52 27550 5001 5001 Foundations 4 2137 2134 Augustinus Assoc 309 27550 4052 5041 Ethics 4 2125 2136 Curie Full 36 28106 5041 5043 Epistemology 3 2126 2137 Kant Full 7 28106 5052 5049 Maieutics 2 2125 28106 5216 4052 Logics 4 2125 Student (s) 28106 5259 5052 Theory of Science 3 2126 sid name semester 29120 5001 5216 Bioethics 2 2126 24002 Xenokrates 18 29120 5041 5259 The Vienna Circle 2 2133 25403 Jonas 12 29120 5049 5022 CS432 2 2134 26120 Fichte 10 29555 5022 4630 The three critiques 4 2137 26830 Aristoxenos 8 25403 5022 27550 Schopenhauer 6 28106 Carnap 3 29120 Theophrastos 2 29555 Feuerbach 2 5

  6. Assumptions #tuples per page: � Relation sizes (# tuples) � |p|= 800 p: b 1024/50 c =20 � |s|= 38000 s: b 1024/50 c = 20 � |v|= 2000 � |h|= 60000 v: b 1024/100 c = 10 � Avg. Tuple size h: b 1024/16 c = 64 � p: 50 Bytes � s: 50 Bytes (page overhead is ignored) � v: 100 Bytes � h: 16 Bytes � selectivities #pages: � Sel[sh] = 2.6 * 10^ -5 p: 800/20 = 40 � Sel[hv] = 5 * 10^ -4 � Sel[vp] = 1.25 * 10^ -3 s: 38000/20 = 1900 � Sel[p.Name= ...] = 1.25 * 10^ -3 v: 2000/10 = 200 � Page size 1024 Bytes � Main memory buffers m = 20+ 1 pages h: d 60000/64 e = 938 (we ignore the overhead of the primary index.) 6

  7. π s.semester (with duplicates) Example 1 Join p.id=v.taught_by Join v.cid=h.cid Sel p.name=´Socrates´ Access(p) Join s.sid=h.sid v s h 7

  8. π s.semester (with duplicates) Example 1 Join p.id=v.taught_by Join v.cid=h.cid Sel p.name=´Socrates´ Access(p) Join s.sid=h.sid v s h 8

  9. π s.semester (with duplicates) Example 1 Join p.id=v.taught_by Join v.cid=h.cid Sel p.name=´Socrates´ Access(p) Join s.sid=h.sid v s h 9

  10. π s.semester (with duplicates) Example 1 Join p.id=v.taught_by Join v.cid=h.cid Sel p.name=´Socrates´ Access(p) Join s.sid=h.sid v s h 10

  11. π s.semester (with duplicates) Example 1 Join p.id=v.taught_by Join v.cid=h.cid Sel p.name=´Socrates´ Access(p) Join s.sid=h.sid v s h 11

  12. π s.semester (with duplicates) Example 1 Join p.id=v.taught_by Join v.cid=h.cid Sel p.name=´Socrates´ Access(p) Join s.sid=h.sid v s h And now we have to allocate memory buffers... 12

  13. π s.semester (with duplicates) Example 1a pipl. 0 NL-Join p.id=v.taught_by 0 1 pipl. 0 NL-Join v.cid=h.cid 9 Sel p.name=´Socrates´ 1 (inner: recompute!) pipl. Access(p) NL-Join s.sid=h.sid v 1 9 s h Buffer assignments in this color 13

  14. π s.semester (with duplicates) Example 1a pipl. 0 NL-Join p.id=v.taught_by 0 1 pipl. 0 NL-Join v.cid=h.cid 9 Sel p.name=´Socrates´ 1 (inner: recompute!) pipl. Access(p) NL-Join s.sid=h.sid v 1 9 s h 14

  15. π s.semester (with duplicates) Example 1a NL-Join p.id=v.taught_by NL-Join v.cid=h.cid Sel p.name=´Socrates´ Access(p) NL-Join s.sid=h.sid v s h 15

  16. π s.semester (with duplicates) Example 1b pipl. 0 NL-Join p.id=v.taught_by 0 1 pipl. 0 NL-Join v.cid=h.cid 9 1 Sel p.name=´Socrates´ pipl. Access(p) NL-Join s.sid=h.sid v 1 9 s h Buffer assignments in this color 16

  17. π s.semester (with duplicates) Example 1b pipl. 0 NL-Join p.id=v.taught_by 0 1 pipl. 0 NL-Join v.cid=h.cid 9 1 Sel p.name=´Socrates´ pipl. Access(p) NL-Join s.sid=h.sid v 1 9 s h 17

  18. π s.semester (with duplicates) Example 1b NL-Join p.id=v.taught_by NL-Join v.cid=h.cid Sel p.name=´Socrates´ Access(p) NL-Join s.sid=h.sid v s h 18

  19. π s.semester (with duplicates) Example 1c pipl. 0 NL-Join p.id=v.taught_by 0 1 pipl. 0 NL-Join v.cid=h.cid 1 18 Sel p.name=´Socrates´ Store intermediate pipl. result 1 Access(p) NL-Join s.sid=h.sid v 1 19 s h Buffer assignments in this color 19

  20. π s.semester (with duplicates) Example 1c pipl. 0 NL-Join p.id=v.taught_by 0 1 pipl. 0 NL-Join v.cid=h.cid 1 18 Sel p.name=´Socrates´ pipl. 1 Access(p) NL-Join s.sid=h.sid v 1 19 s h 20

  21. π s.semester (with duplicates) Example 1c NL-Join p.id=v.taught_by NL-Join v.cid=h.cid Sel p.name=´Socrates´ Access(p) NL-Join s.sid=h.sid v s h 21

  22. π s.semester Example 2 pipl. NL-Join s.sid=h.sid 5 0 s NL-Join v.cid=h.cid 1 0 NL-Join p.id=v.taught_by 1 0 h Sel p.name=´Socrates´ v pipl. Access(p) 22

  23. π s.semester Example 2 pipl. NL-Join s.sid=h.sid (Same query, different 5 0 Join order) s NL-Join v.cid=h.cid 1 0 NL-Join p.id=v.taught_by 1 0 h Sel p.name=´Socrates´ v pipl. Access(p) 23

  24. π s.semester Example 2 pipl. NL-Join s.sid=h.sid 5 0 s NL-Join v.cid=h.cid 1 0 NL-Join p.id=v.taught_by 1 0 h Sel p.name=´Socrates´ v pipl. Access(p) 24

  25. π s.semester Example 2 pipl. NL-Join s.sid=h.sid 5 0 s NL-Join v.cid=h.cid 1 0 NL-Join p.id=v.taught_by 1 0 h Sel p.name=´Socrates´ v pipl. Access(p) 25

  26. π s.semester Example 2 pipl. NL-Join s.sid=h.sid 5 0 s NL-Join v.cid=h.cid 1 0 NL-Join p.id=v.taught_by 1 0 h Sel p.name=´Socrates´ v pipl. Access(p) 26

  27. π s.semester Example 2 pipl. NL-Join s.sid=h.sid 5 0 s NL-Join v.cid=h.cid 1 0 NL-Join p.id=v.taught_by 1 0 h Sel p.name=´Socrates´ v pipl. Access(p) 27

  28. Example 3a π s.semester Index-Join s.sid=h.sid Merge-Join v.cid=h.cid s Sort v.cid Sort h.cid Index-Join p.id=v.taught_by h Sel p.name=´Socrates´ v Access(p) taught_by 28

  29. Example 3a π s.semester pipl. Index-Join s.sid=h.sid e.g. 2 5 Merge-Join v.cid=h.cid s 0 Sort v.cid Sort h.cid 1 20+1 pipl. Index-Join p.id=v.taught_by h 1 Sel p.name=´Socrates´ v pipl. Access(p) taught_by 29

  30. Example 3a π s.semester pipl. Index-Join s.sid=h.sid e.g. 2 5 Merge-Join v.cid=h.cid s 0 Sort v.cid Sort h.cid 1 20+1 pipl. Index-Join p.id=v.taught_by h 1 Sel p.name=´Socrates´ v pipl. Access(p) taught_by 30

  31. Example 3a π s.semester pipl. Index-Join s.sid=h.sid e.g. 2 5 Merge-Join v.cid=h.cid s 0 Sort v.cid Sort h.cid 1 20+1 pipl. Index-Join p.id=v.taught_by h 1 Sel p.name=´Socrates´ v pipl. Access(p) taught_by 31

  32. Example 3a π s.semester pipl. Index-Join s.sid=h.sid e.g. 2 5 Merge-Join v.cid=h.cid s 0 Sort v.cid Sort h.cid 1 20+1 pipl. Index-Join p.id=v.taught_by h 1 Sel p.name=´Socrates´ v pipl. Access(p) taught_by 32

  33. Example 3a π s.semester pipl. Index-Join s.sid=h.sid e.g. 2 5 Merge-Join v.cid=h.cid s 0 Sort v.cid Sort h.cid 1 20+1 pipl. Index-Join p.id=v.taught_by h 1 Sel p.name=´Socrates´ v pipl. Access(p) taught_by 33

  34. Example 3a pipl. 18 pipl. π s.semester pipl. Index-Join s.sid=h.sid e.g. 2 5 Merge-Join v.cid=h.cid s 0 Sort v.cid Sort h.cid 1 20+1 pipl. Index-Join p.id=v.taught_by h 1 Sel p.name=´Socrates´ v pipl. Access(p) taught_by 34

  35. Example 3a pipl. 18 pipl. π s.semester pipl. Index-Join s.sid=h.sid e.g. 2 5 Merge-Join v.cid=h.cid s 0 Sort v.cid Sort h.cid 1 20+1 pipl. Index-Join p.id=v.taught_by h 1 Sel p.name=´Socrates´ v pipl. Access(p) taught_by 35

  36. Example 3a π s.semester pipl. Index-Join s.sid=h.sid e.g. 2 5 Merge-Join v.cid=h.cid s 0 Sort v.cid Sort h.cid 1 20+1 pipl. Index-Join p.id=v.taught_by h 1 Sel p.name=´Socrates´ v pipl. Access(p) taught_by 36

  37. IndexDup Example 3b pipl. 3 Hash pipl. π s.semester pipl. Index-Join s.sid=h.sid e.g. 2 5 Merge-Join v.cid=h.cid s 0 Sort v.cid Sort h.cid 1 20+1 pipl. Index-Join p.id=v.taught_by h 1 Sel p.name=´Socrates´ v pipl. Access(p) taught_by 37

Download Presentation
Download Policy: The content available on the website is offered to you 'AS IS' for your personal information and use only. It cannot be commercialized, licensed, or distributed on other websites without prior consent from the author. To download a presentation, simply click this link. If you encounter any difficulties during the download process, it's possible that the publisher has removed the file from their server.

Recommend

Query Optimization Through the Looking Glass Some Lessons From Building an LLVM-Based Query

Query Optimization Through the Looking Glass Some Lessons From Building an LLVM-Based Query

Query Optimization Through the Looking Glass Some Lessons From Building an LLVM-Based Query Compiler Viktor Leis Technische Universitt Mnchen Introduction: Query Optimization HJ B cardinality cost INL B T SELECT ... estimation

679 views • 25 slides
Data Management Systems Query Processing Introduction Execution models Optimization I

Data Management Systems Query Processing Introduction Execution models Optimization I

Data Management Systems Query Processing Introduction Execution models Optimization I heuristics & rewriting Optimization II cost models Optimization III - Operators Gustavo Alonso Institute of Computing Platforms

327 views • 7 slides
SAMPLING-BASED QUERY RE- OPTIMIZATION Wentao Wu Microsoft Research Background 2 Query

SAMPLING-BASED QUERY RE- OPTIMIZATION Wentao Wu Microsoft Research Background 2 Query

1 SAMPLING-BASED QUERY RE- OPTIMIZATION Wentao Wu Microsoft Research Background 2 Query optimization remains challenging despite of decades of efforts and progresses. Cardinality estimation is the key challenge. Selectivity of

400 views • 28 slides
CAS CS 460/660 Introduction to Database Systems Query Evaluation II 1.1 Cost-based Query

CAS CS 460/660 Introduction to Database Systems Query Evaluation II 1.1 Cost-based Query

CAS CS 460/660 Introduction to Database Systems Query Evaluation II 1.1 Cost-based Query Sub-System Select * Queries From Blah B Where B.blah = blah Query Parser Query Optimizer Plan Plan Cost Catalog Manager Generator Estimator

711 views • 44 slides
Outline Query Processing Overview Algorithms for basic operations Sorting Selection

Outline Query Processing Overview Algorithms for basic operations Sorting Selection

Outline Query Processing Overview Algorithms for basic operations Sorting Selection Selection Join Projection Query optimization Heuristics Cost"based optimization 19 Estimate I/O Cost for

696 views • 38 slides
Introduction Query Execution Engine Implements a set of physical operators 2 key

Introduction Query Execution Engine Implements a set of physical operators 2 key

Overview of Query Optimization in Relational Systems An overview of current query optimization techniques Overview of Query Optimization Provides fundamentals of query in Relational Systems optimization Presenter: Albert Wong

294 views • 4 slides
Making Cost-Based Query Optimization Asymmetry-Aware Daniel Bausch, Ilia Petrov, and Alejandro

Making Cost-Based Query Optimization Asymmetry-Aware Daniel Bausch, Ilia Petrov, and Alejandro

Making Cost-Based Query Optimization Asymmetry-Aware Daniel Bausch, Ilia Petrov, and Alejandro Buchmann {bausch, petrov, buchmann}@dvs.tu-darmstadt.de 2012-05-21 | Computer Science Department | Databases and Distributed Systems Group | Daniel

400 views • 20 slides
Parametric Query Optimization for Linear and Piecewise Linear Cost Functions Arvind Hulgeri S.

Parametric Query Optimization for Linear and Piecewise Linear Cost Functions Arvind Hulgeri S.

Parametric Query Optimization for Linear and Piecewise Linear Cost Functions Arvind Hulgeri S. Sudarshan Dept of Computer Science and Engg. Indian Institute of Technology Bombay Parametric query: An example Select * From A, B Where A.x =

720 views • 29 slides
Data Management Systems Query Processing Execution models Understanding the data

Data Management Systems Query Processing Execution models Understanding the data

Data Management Systems Query Processing Execution models Understanding the data Optimization I heuristics & Calculating Costs rewriting Rule Based Optimizer Optimization II cost models Optimization III -

1.18k views • 51 slides
Review: Case where index is useful CS5208: Query Optimization 2 1 Query Optimization Since

Review: Case where index is useful CS5208: Query Optimization 2 1 Query Optimization Since

Query Optimization in Relational Database Systems It is safer to accept any chance that offers itself, and extemporize a procedure to fit it, than to get a g good plan matured, and wait p , for a chance of using it. Thomas Hardy (1874) in

526 views • 50 slides
CS4224/CS5424 Lecture 9 Distributed Query Processing Query Processing Translates query into a

CS4224/CS5424 Lecture 9 Distributed Query Processing Query Processing Translates query into a

CS4224/CS5424 Lecture 9 Distributed Query Processing Query Processing Translates query into a query plan that minimizes some cost function Minimize total cost CPU cost, I/O cost, & communication cost Minimize response time

841 views • 52 slides
Query Execution 2 and Query Optimization Instructor: Matei Zaharia cs245.stanford.edu Query

Query Execution 2 and Query Optimization Instructor: Matei Zaharia cs245.stanford.edu Query

Query Execution 2 and Query Optimization Instructor: Matei Zaharia cs245.stanford.edu Query Execution Overview Query representation (e.g. SQL) Logical query plan (e.g. relational algebra) Query optimization Optimized logical plan Physical

1.17k views • 92 slides
Introduction to Database Systems CS4320 Instructor: Christoph Koch koch@cs.cornell.edu CS 4320

Introduction to Database Systems CS4320 Instructor: Christoph Koch koch@cs.cornell.edu CS 4320

Introduction to Database Systems CS4320 Instructor: Christoph Koch koch@cs.cornell.edu CS 4320 1 CS4320/1: Introduction to Database Systems Underlying theme: How do I build a data management system? CS4320 will deal with the underlying

920 views • 34 slides
Cost-Based Plan Selection Enumerate, Estimate, Select 304 Cost-Based Plan Selection Execution

Cost-Based Plan Selection Enumerate, Estimate, Select 304 Cost-Based Plan Selection Execution

Cost-Based Plan Selection Enumerate, Estimate, Select 304 Cost-Based Plan Selection Execution Query Compiler Engine SQL Logical Optimized Physical Result query plan logical query plan query plan Translation Logical plan Physical

658 views • 47 slides
Data Management Systems Query Processing Execution models Optimization heuristics

Data Management Systems Query Processing Execution models Optimization heuristics

Data Management Systems Query Processing Execution models Optimization heuristics & Access to base tables rewriting Sorting and Aggregation Optimization cost models Joins Operators Gustavo Alonso Institute of

761 views • 57 slides
Optimizing Join Enumeration in Transformation-based Query

Optimizing Join Enumeration in Transformation-based Query

Optimizing Join Enumeration in Transformation-based Query Optimizers ANIL SHANBHAG, S. SUDARSHAN IIT BOMBAY anils@mit.edu, sudarsha@cse.iitb.ac.in VLDB 2014 Query Optimization: Quick

576 views • 18 slides
Escalation Sometimes when agencies work together there may be times when a decision made by a

Escalation Sometimes when agencies work together there may be times when a decision made by a

Escalation Sometimes when agencies work together there may be times when a decision made by a worker from another agency is either not safe or not in the best interests of a child. This may relate to professional involvement in early help

682 views • 4 slides
Transf ansformat ormation ion Road Map oad Map for Children and Young Peoples Me Ment

Transf ansformat ormation ion Road Map oad Map for Children and Young Peoples Me Ment

Transf ansformat ormation ion Road Map oad Map for Children and Young Peoples Me Ment ntal al Hea Healt lth h an and d Wel ellb lbeing eing Towe wer Hamle lets s Transf sformat mation ion Road M d Map p for Chil ildr

270 views • 7 slides
Creating a new vision for children and families Claire Cameron and Katie Hollingworth A study for

Creating a new vision for children and families Claire Cameron and Katie Hollingworth A study for

Creating a new vision for children and families Claire Cameron and Katie Hollingworth A study for SOS Childrens Villages UK http://www.ucl.ac.uk/ioe/de partments- centres/centres/thomas- coram-research-unit Aims What are the issues and

502 views • 11 slides
From court proceedings to care Analysis of North Yorkshire County Councils CYPS data Applying

From court proceedings to care Analysis of North Yorkshire County Councils CYPS data Applying

From court proceedings to care Analysis of North Yorkshire County Councils CYPS data Applying Outcomes of Care proceedings for Children Study Method Judith Masson UoB, Sean Jenny and Sharon Lawn, NYCC Coram Data Visualization 10th December

311 views • 16 slides
Tweet: #SAIF Accessible Information Get your message across to your intended audience.

Tweet: #SAIF Accessible Information Get your message across to your intended audience.

Information explosion, information inclusion Jean Alcock, SAIF Chairperson Mairi Whannel, Project Co-ordinator Michelle Davitt, Forum member Tweet: #SAIF Accessible Information Get your message across to your intended audience. Include

275 views • 14 slides
The California Middle Class Scholarship Southern San Joaquin Valley Cal-SOAP Consortium The

The California Middle Class Scholarship Southern San Joaquin Valley Cal-SOAP Consortium The

The California Middle Class Scholarship Southern San Joaquin Valley Cal-SOAP Consortium The California Middle Class Scholarship Starts in 2014-2015 for undergraduate students who are: Attending a CSU or UC campus (Current, Incoming First

361 views • 10 slides
Development of National Hospital Accreditation System 37-year Experience of Taiwan Dr.

Development of National Hospital Accreditation System 37-year Experience of Taiwan Dr.

2015/9/23 Development of National Hospital Accreditation System 37-year Experience of Taiwan Dr. Wui-Chiang Lee, MD, PhD, MHS President, Asian Society for Quality in Healthcare (ASQua) Former CEO, Joint Commission of Taiwan Former

625 views • 23 slides
www.antitraffic.government.bg COORDINATING ACTION AMONGST STATE ENTITIES AND WORKING TOGETHER

www.antitraffic.government.bg COORDINATING ACTION AMONGST STATE ENTITIES AND WORKING TOGETHER

www.antitraffic.government.bg COORDINATING ACTION AMONGST STATE ENTITIES AND WORKING TOGETHER WITH EMPLOYMENT AGENCIES TO COUNTER LABOUR TRAFFICKING: THE EXAMPLE OF BULGARIA Bulgaria is a source and transit country for victims of

148 views • 13 slides

More recommend