Schedule - PGConf.EU 2018
AUTO PLAN TUNING USING FEEDBACK LOOP
Date: 2018-10-26
Time: 10:50–11:40
Room: Berlin
Level: Intermediate
Efficient plans are important, however, as is often seen in OLAP and batch processing queries, the more complex the join, filtering, and aggregates contained in a query, the more there is a possibility of row count estimation errors, leading to the planner choosing inefficient plans.
NTT, where I come from, is a telecommunications company that supports Japan's telecommunications infrastructure and has spent a lot of time planning to meet strict performance requirements of query execution time in system development. Plan tuning is hard to do it without significant investment in skill, time, and environment. Therefore, many people want auto-tuning.
I developed a tool called 'pg_plan_advsr', which is currently in PoC stage and implemented as an PostgreSQL extension using some Executor hooks. It has three functions of accumulating, analyzing, and correcting the plan, and the user can obtain a plan auto-tuned simply by executing the query with the EXPLAIN ANALYZE command. Of course, since all the information of auto-tuning is stored in the history table, it is possible to check the tuning process and to reproduce and verify the plan at a certain point in time.
pg_plan_advsr is inspired by "control engineering" principles, whereby the process of plan optimization is enhanced using feedback loop. The information about the actual execution of plan for a given query, when repeatedly fed back into the optimization process, can reduce the error in estimation row counts that affect the planning process and the resulting plan. For feeding that information into the optimization process, pg_plan_advsr uses another tool we have open sourced called pg_hint_plan, which is used to feed the necessary information as optimizer hints (AWS and PGPro are known to use pg_hint_plan, by the way). To verify the effect of auto-tuning using pg_plan_advsr, I used join order benchmark (composed of 113 queries including 3 to 16 joins), and it was confirmed that the total execution time was shortened to about 50% (about 200% performance improvement) on PG 10.4.
pg_plan_advsr brings merit of simplification and generalization of plan tuning work, and it will be a great help for user who needs plan tuning. Also, for planner researchers and PG developers, knowledge gained by this extension should be useful in terms of improving plan searching and implementing autonomous databases. In this session, I will cover the following:
Conventional plan tuning method - List of conventional improvement methods and features
Auto tuning by pg_plan_advsr - Overview of the extension - Estimation correction mechanism, how to compensate estimate for each plan node? - Demonstration of benefits using Join order benchmark and analysis of the results - Is it the optimum plan if estimation error disappears? - Use case of extension - Points to keep in mind
Toward the future PostgreSQL - Proposal of plan advisor to PostgreSQL core - Ideas for autonomous databases using plan advisor
Slides
The following slides have been made available for this session:
