Bruce Momjian, a longtime Postgres developer, educator and consultant joins Robert Blumen for a discussion of the SQL query planner in the Postgres relational database. Topics covered include what is a query? How queries are evaluated by the server; the phases of query evaluation; SQL as a declarative language; why declarative query evaluation is simpler and more productive that it would be for a developer to write queries in an imperative language; what is a query plan? The elements of a query plan; the impact of table ordering on query performance; joins; indexes; how the query planner works; size of the query plan space; optimizing plans versus good enough; balancing the time spent query planning against the time spent executing; what is the cost function? And does the cost function correspond to real-world query performance?; challenges in planning sorts and aggregations; debugging query plans; testing and regression of the query planner; how to test and validate query plans in the software development process; can this model be used to other SQL back ends than a file-based database? (example Hadoop); research on query planning; the open source Postgres project.
Show Notes
Related Links
- Episode 310: Kirk Pepperdine on Performance Optimization
- Episode 225: Brendan Gregg on Systems Performance
- Episode 125: Performance Engineering with Chris Grindstaff
- Postgres web site
- Bruce Momjian’s web site
- Bruce Momjian’s slide decks on Postgres performance
- Oracle Optimizer Blog
- Inside the SQL Server Query Optimizer by Brian Nevarez
- Book: SQL Server Execution Plans