Database Reference
In-Depth Information
Join ordering
When the number of joins increases, so does the complexity associated in controlling
it in query tuning and the optimizer. Though the query optimizer can choose
plans to execute these joins in several orders with identical results, yet the most
inexpensive one will be utilized. However, performing these searches for best plans
can be time consuming and error prone for decisions. You can coerce the optimizer
to utilize the order you consider is the optimal way to join the tables, thus reducing
the orchestrating time when performing a series of explicit join operations. In this
way, other plans will not be considered. This can be a subsidiary, considering if the
query-construction time is huge for a complex join or when the order selected by the
optimizer is worthless.
Query planning
How well you plan will decide how optimized the solution is.
To know why we need query planning is to irst understand that queries are
presented in a logical way of SQL statements that state to achieve a goal but queries
are executed physically, and for this, even a simple-looking query at front might
have to consider many constraints internally to ind the most eficient way to execute
a query. This, undoubtedly, is the single-line objective of the PostgreSQL query
planner, that is, to determine the best way to evaluate a query.
Let's peep into what query planning has to consider before generating a plan.
We will later see how this feature is exposed through
EXPLAIN
in detail.
Let's recall the query worklow in the following manner:
1. The SQL query is transformed into a
query tree
.
2. The query tree is tailored by the rewriter in such a way that it looks for
keywords in the query tree and expands further with the provided deinition.
3. The planner takes the modiied parse tree as an input and generates all the
feasible query paths. The planner further evaluates the path to determine the
optimal path and develops a query plan for this path.
4. The query plan is modiied into executable SQL queries and processed to
achieve desired results.
Hence, we can summarize two basic tasks of the query planner. They are as follows:
• List the set of plans for a given query
• Predict the cost of executing a certain query plan
