15. Query Optimization.ppt

15. Query Optimization System Design Parsing Examples Modern Optimizers EXPLAIN – the output of an optimizer Overview of internals Dynamic programming VP – view the internals Examples Variants Top Down Optimization Optimizer Hints Unnesting Queries Learning Objectives Explain EXPLAIN Explain VP’s output Explain each variant Flatten a nested query. Overview of Query Processing Now we focus on the top of this diagram Detail of the top Parsing and Optimization The Parser Verifies that the SQL query is syntactically correct, that the tables and attributes exist, and that the user has the appropriate permissions. Translates the SQL query into a simple query tree (operators: relational algebra plus a few other ones) The Optimizer: Generates other, equivalent query trees (Actually builds these trees bottom up) For each query tree generated: Selects algorithms for each operator (producing a query plan) estimates the cost of the plan Chooses the plan with lowest cost (of the plans considered, which is not necessarily all possible plans) Schema for Examples Download Postgres source from /download Logical and physical schema is at src/test/bench/create.source Simplified version is at /587/create.bench Log into the database at /pgCS587/ Click on PostgreSQL User CS587, Password 587dbms, see notes view Browse the tables, especially tenk1 and its attributes unique1, unique2, stringu1 Here’s what the parser does Practice: Parse a Query Describe the parsers output when the input is SELECT stringu2 FROM tenk1 JOIN tenk2 USING unique1 WHERE tenk1.stringu1=abc; How Can We View the Optimizer? Postgres calls its optimizer the Planner Postgres planner algorithm [668] is the same as all modern DBMSs optimizer algorithms Except SQL Server We have good news and good news. We can see both the planners output AND its internal processing. Its output is available to anyone; its internal processing is avaliable only to us and a few others. The output is displayed by the EXPL