Lecture

System-R Optimizer: Query Optimization and Cost Estimation

Graph Chat

Chat with Graph Search

Ask any question about EPFL courses, lectures, exercises, research, news, etc. or try the example questions below.

DISCLAIMER: The Graph Chatbot is not programmed to provide explicit or categorical answers to your questions. Rather, it transforms your questions into API requests that are distributed across the various IT services officially administered by EPFL. Its purpose is solely to collect and recommend relevant references to content that you can explore to help you answer your questions.

In course

CS-422: Database systems

This course is intended for students who want to understand modern large-scale data analysis systems and database systems. It covers a wide range of topics and technologies, and will prepare students to be able to build such systems as well as read and und ...

Description

This lecture covers the System-R Optimizer, a seminal project from the 70s with a significant influence on subsequent database systems. It explains the high-level idea of iterating over possible plans, estimating costs, and returning the cheapest to the user. The lecture details the steps involved in breaking queries into blocks, generating logical operators, and selecting the best access paths for tables. It also delves into join orderings, cost estimation, and the principle of optimality in query optimization. The instructor emphasizes the importance of interesting orders, selectivity estimates, and the challenges in cardinality estimation. The lecture concludes with a summary of the System R optimizer, highlighting its reliance on heuristics and cost-based optimization.

Instructor

Anastasia Ailamaki

Expertise Database management systems, scientific applications, computer architecture Teaching & PhD PhD Students Hamish Nicholson, Anna Herlihy, Yi Jiang, Eleni Zapridou, Georgiy Konstantinovich Lebedev, Ioanna Tsakalidou Past EPFL PhD Students Farhan Tauheed, Radu Ioan Stoica, Manos Athanassoulis, Pinar Tözün, Adrian Daniel Popescu, Ioannis Alagiannis, Renata Borovica-Gajic, Danica Porobic, Iraklis Psaroudakis, Manos Karpathiotakis, Matthaios Alexandros Olma, Mirjana Pavlovic, Eleni Tzirita Zacharatou,

Official source

Related concepts (32)

Database

In computing, a database is an organized collection of data (also known as a data store) stored and accessed electronically through the use of a database management system. Small databases can be stored on a , while large databases are hosted on computer clusters or cloud storage. The design of databases spans formal techniques and practical considerations, including data modeling, efficient data representation and storage, query languages, security and privacy of sensitive data, and distributed computing issues, including supporting concurrent access and fault tolerance.

Logical connective

In logic, a logical connective (also called a logical operator, sentential connective, or sentential operator) is a logical constant. They can be used to connect logical formulas. For instance in the syntax of propositional logic, the binary connective can be used to join the two atomic formulas and , rendering the complex formula . Common connectives include negation, disjunction, conjunction, implication, and equivalence.

Logical conjunction

In logic, mathematics and linguistics, and () is the truth-functional operator of conjunction or logical conjunction. The logical connective of this operator is typically represented as or or (prefix) or or in which is the most modern and widely used. The and of a set of operands is true if and only if all of its operands are true, i.e., is true if and only if is true and is true. An operand of a conjunction is a conjunct.

Relational database

A relational database is a (most commonly digital) database based on the relational model of data, as proposed by E. F. Codd in 1970. A system used to maintain relational databases is a relational database management system (RDBMS). Many relational database systems are equipped with the option of using SQL (Structured Query Language) for querying and updating the database. The term "relational database" was first defined by E. F. Codd at IBM in 1970. Codd introduced the term in his research paper "A Relational Model of Data for Large Shared Data Banks".

Logical disjunction

In logic, disjunction, also known as logical disjunction or logical or or logical addition or inclusive disjunction , is a logical connective typically notated as and read aloud as "or". For instance, the English language sentence "it is sunny or it is warm" can be represented in logic using the disjunctive formula , assuming that abbreviates "it is sunny" and abbreviates "it is warm". In classical logic, disjunction is given a truth functional semantics according to which a formula is true unless both and are false.

Related lectures (64)

Cost-based Query Optimization

Explores cost-based query optimization in database systems, covering cost estimation, selectivity estimation, and join cardinality.

Query Operators: Joins and Query Processing Techniques

Discusses advanced query operators, focusing on join techniques and their processing methods in relational databases.

Query Processing: Relational Operations and Models

Discusses query processing models and relational operations, focusing on selections, projections, and joins in data-intensive systems.

Query Optimization: Heuristics and Cost-based Strategies

Explores heuristic-based query optimization, join ordering, and cost estimation strategies in database systems.

Materialization Problems in Database Systems