Auto plan tuning using feedback loop

Tatsuro Yamada


Senior expert

Duration 22 min

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:

  1. Automatic plan tuning by repeatedly feeding execution information to planner
  2. Preserve all plans generated during plan tuning in a history table
  3. 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)

Slides →