Query manager

This part is where the power of a database lies. During this part, an ill-written query is transformed into a fast executable code. The code is then executed and the results are returned to the client manager. It’s a multiple-step operation:

• the query is first parsed to see if it’s valid
• it’s then rewritten to remove useless operations and add some pre-optimizations
• it’s then optimized to improve the performances and transformed into an execution and data access plan.
• then the plan is compiled
• at last, it’s executed

In this part, I won’t talk a lot about the last 2 points because they’re less important.

After reading this part, if you want a better understanding I recommend reading:

• The initial research paper (1979) on cost based optimization: Access Path Selection in a Relational Database Management System. This article is only 12 pages and understandable with an average level in computer science.
• A very good and in-depth presentation on how DB2 9.X optimizes queries here
• A very good presentation on how PostgreSQL optimizes queries here. It’s the most accessible document since it’s more a presentation on “let’s see what query plans PostgreSQL gives in these situations“ than a “let’s see the algorithms used by PostgreSQL”.
• The official SQLite documentation about optimization. It’s “easy” to read because SQLite uses simple rules. Moreover, it’s the only official documentation that really explains how it works.
• A good presentation on how SQL Server 2005 optimizes queries here
• A white paper about optimization in Oracle 12c here
• 2 theoretical courses on query optimization from the authors of the book “ DATABASE SYSTEM CONCEPTS” here and there. A good read that focuses on disk I/O cost but a good level in CS is required.
• Another theoretical course that I find more accessible but that only focuses on join operators and disk I/O.