Query Tuning, Indexes, and Performance Optimization
Performance tuning is one of the most complex tasks in the daily job of a database administrator (DBA). SQL is a declarative language, and therefore it does not define how to access the underlying data – that responsibility is left to the database engine. PostgreSQL, therefore, must select, for every statement, the best available access to the data.
A particular component, the planner, is responsible for deciding on the best out of all the available paths to the underlying data, while another component, the optimizer, is responsible for executing the statement with such a particular access plan.
The aim of this chapter is to teach you how PostgreSQL executes a query, how the planner computes the best execution plan, and how you can help in improving the performance by means of indexes.
You will learn about the following topics in this chapter:
- Execution of a statement
- Indexes
- The
EXPLAIN...
