Auto plan tuning using feedback loop
As is often seen in OLAP and batch processing workloads, the more complex a query (containing many joins, filters, aggregates), the more there is a possibility of row count estimation errors, which leads to planner choosing an inefficient execution plan.
To address that problem, I developed a tool called pg_plan_advsr as a PostgreSQL extension, which corrects the estimation errors by repeatedly feeding back the information collected during query execution to the planner.
The tool has three features:
- Automatic plan tuning by repeatedly feeding execution information to planner
- Preserve all plans generated during plan tuning in a history table
- Create and store optimizer hints to be able to reproduce plans generated during tuning process
I verified the effectiveness of pg_plan_advsr by enabling it when running the join order benchmark (JOB) against PG 10.4 and observed its execution time shortening to 50% of the original. Therefore, it is useful for user who would like to do plan tuning for OLAP and batch processing.
I will talk about the following things in this presentation:
- Principles behind pg_plan_advsr and its architecture
- Detailed information about the measurements done with JOB
- Possible future enhancements
- Using aqo and pg_plan_advsr together (experimental)