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

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
PostgreSQL Query Optimization Step by step techniques Ilya Kosmodemiansky (ik@dataegret.com)

PostgreSQL Query Optimization Step by step techniques Ilya Kosmodemiansky (ik@dataegret.com)

PostgreSQL Query Optimization Step by step techniques Ilya Kosmodemiansky (ik@dataegret.com) Agenda 2 1. What is a slow query? 2. How to chose queries to optimize? 3. What is a query plan? 4. Optimization tools 5. Optimization examples

666 views • 50 slides
Mobile Computing theme introduction Nick Filer (nfiler@cs.man.ac.uk) Dirk Koch

Mobile Computing theme introduction Nick Filer (nfiler@cs.man.ac.uk) Dirk Koch

17/09/2013 Mobile Computing theme introduction Nick Filer (nfiler@cs.man.ac.uk) Dirk Koch (dirk.koch@manchester.ac.uk) Mobile Computing theme intro 1 Mobile computing Human interaction with battery powered portable devices.

407 views • 13 slides
Data Management Systems Query Processing Execution models Views and Schemas

Data Management Systems Query Processing Execution models Views and Schemas

Data Management Systems Query Processing Execution models Views and Schemas Optimization I heuristics & Query rewriting rewriting Optimization II cost models Basic optimization heuristics Optimization III -

490 views • 45 slides
Relational Query Optimization [R&G] Chapter 15 CS4320 1 Highlights of System R Optimizer

Relational Query Optimization [R&G] Chapter 15 CS4320 1 Highlights of System R Optimizer

Relational Query Optimization [R&G] Chapter 15 CS4320 1 Highlights of System R Optimizer Impact: Most widely used currently; works well for < 10 joins. Cost estimation: Approximate art at best. Statistics, maintained in

444 views • 19 slides
Flow-based Cost Query draft-gao-alto-fcs-01 Kai Gao 1 J. Jensen Zhang 2 J. Austin Wang 2 Qiao

Flow-based Cost Query draft-gao-alto-fcs-01 Kai Gao 1 J. Jensen Zhang 2 J. Austin Wang 2 Qiao

Flow-based Cost Query draft-gao-alto-fcs-01 Kai Gao 1 J. Jensen Zhang 2 J. Austin Wang 2 Qiao Xiang 3 Y. Richard Yang 3 1 Tsinghua University 2 Tongji University 3 Yale University March 31@IETF 98 Flow-based Design in a Nutshell Cost Services:

767 views • 29 slides
CSE 232A Graduate Database Systems Arun Kumar Topic 4: Query Optimization Chapters 12 and

CSE 232A Graduate Database Systems Arun Kumar Topic 4: Query Optimization Chapters 12 and

CSE 232A Graduate Database Systems Arun Kumar Topic 4: Query Optimization Chapters 12 and 15 of Cow Book Slide ACKs: Jignesh Patel, Paris Koutris 1 Lifecycle of a Query Query Result Query Database Server Query Execute Parser

735 views • 50 slides
XPath Reference XPath leashed, Michael Benedikt and Christoph Koch, TR, 2006 1

XPath Reference XPath leashed, Michael Benedikt and Christoph Koch, TR, 2006 1

XPath Reference XPath leashed, Michael Benedikt and Christoph Koch, TR, 2006 1 Expressivity of XPath Formal setting XPath interpreted in a logical structure t with a finite set of labels and a finite set of Attributes @Ai (functions

587 views • 15 slides
Server Operational Cost Optimization for Cloud Computing Service Providers over a Time Horizon

Server Operational Cost Optimization for Cloud Computing Service Providers over a Time Horizon

Server Operational Cost Optimization for Cloud Computing Service Providers over a Time Horizon Haiyang(Ocean)Qian and Deep Medhi Networking and Telecommunication Research Lab (NeTReL) University of Missouri-Kansas City USENIX Hot-ICE 2011

323 views • 20 slides

More recommend