Database Reference
In-Depth Information
Query Optimization
Query optimization is fundamental and the most important concept for a database
developer to utilize the database eficiently, and this is the consequent part of
database tuning. In general, query optimization is not the responsibility of the user,
because after submitting the query, it's the database server optimizer's responsibility
to optimize the query in the best possible way. Therefore, PostgreSQL provides
ways to optimize the query. In this chapter, we will explore all these ways in
detail. The important part of query optimization is studying the parameters, which
involves query creation; we will discuss these parameters in detail in this chapter.
We will also study query optimization by exploring the query construction and
the commands that can be used to get the information of the inner working of the
planner and optimizer. This is required to deduce why the query is slow and how to
improve its performance; this is the main objective of this chapter. There are many
ways in PostgreSQL to detect and ine-tune the query performance. This includes
composing the eficient query and tuning PostgreSQL's coniguration parameters.
The topics we will discuss in the following sections are:
• Query detail using
EXPLAIN
• Query plan structure
• How to detect the query performance problem
• Different ways to improve the database performance
