Be Good Friends with Query Planner: How to Get, Analyze, and Control Plans
Presented by:
Atsushi Torikoshi
No video of the event yet, sorry!
PostgreSQL's planner is our excellent colleague. When we write SQL queries, the planner devises the optimal plans to get the results.
Unfortunately, just as there is no perfect human, the planner sometimes selects suboptimal plans. Consequently, the query execution takes a long time and we DBAs are woken up by phone calls in the middle of the night to know the chosen plan.
Getting the plan itself is not difficult. Using tools like EXPLAIN or auto_explain allow us to retrieve it. For simple queries, it is often easy to understand the output of EXPLAIN, identify problems, and make improvements.
However, the situation changes in a live production environment with complex queries. For instance, the following questions arise:
- How can we get the necessary information around the plan without causing performance impact?
- If we have a long-running query and want to know the plan without waiting for the query to finish, can we get it from the running query?
- We have obtained the plan, but it is very long and complex. How can we efficiently analyze it? Are there commonly overlooked aspects?
- We understand that the plan needs to be changed. What should we do to ensure the planner selects the right plan without causing problematic side effects?
In this presentation, I will explain how PostgreSQL core and publicly available extensions can be used to address these questions and foster a good relationship with the planner. I will also discuss some of the issues that cannot be addressed at the moment and how PostgreSQL core developers, including myself, are proposing features to make future versions of PostgreSQL more user-friendly.
- Date:
- Duration:
- 45 min
- Room:
- Conference:
- PGConf India, 2025
- Language:
- Track:
- Database Administration
- Difficulty:
- Medium