linux.conf.au Presentations
Inside the PostgreSQL Query Optimizer
Seminar
Given a SQL query, the query optimizer is the component of the database management system that is tasked with determining the most efficient way to execute the query. Unfortunately, the barrier to understanding how this process works is high, and most of the work on the PostgreSQL optimizer to date has been done by a select few hackers. This presentation aims to present the central data structures and algorithms of the optimizer in a clear and concise fashion.
The talk will be aimed at two primary groups of people: advanced users of PostgreSQL, and potential PostgreSQL developers. Users will benefit by gaining a deeper understanding of how PostgreSQL is evaluating their queries. Potential developers will be introduced to a challenging and exciting part of the PostgreSQL backend.
Specific topics will include:
- Introduction to PostgreSQL internals: an outline of how a query is processed
- Introduction to query optimization: what is query optimization, and what is the optimizer trying to achieve?
- How to read a PostgreSQL query plan produced by EXPLAIN
- The basic query optimization algorithm
- Optimizing joins: join types, join order, the System R algorithm for constructing a query plan
- Optimizing predicates: database statistics
- Optimizing subselects
- Optimizing functions
- Q & A
Given any extra time, additional topics might include areas for improvement in the optimizer, and the genetic algorithm ("GEQO") used to handle optimization of queries containing many (12+) joins.
Neil Conway
Neil Conway has contributed to the development of PostgreSQL for the past 3 years. His work on PostgreSQL has covered a wide variety of areas; his first major contribution was prepared statements. He continues to actively contribute to PostgreSQL.
In real life, he is an undergraduate university student at Queen's University in Kingston, Ontario. He is taking twelve months off school to work full-time on PostgreSQL as an employee of Fujitsu Australia Software Technology in Sydney, Australia.
