title

text

T
Tatsuro Yamada NTT Comware
16:00 06 February
22 мин

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:

  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)

Материалы к докладу

Слайды

Видео

Другие доклады

  • Konstantin Evteev
    Konstantin Evteev X5 FoodTech
    45 мин

    Standby in production

    My talk will be about different cases of usage and setup of the standby server; examples how to setup standby linked to your archive( to make an opportunity to recreate standby from archive after primary crashing and promoting your old standby); Avito experience of usage of standby server for read-only queries: problems and solutions; monitoring of standby.

  • Aleksander Sheludchenkov
    Aleksander Sheludchenkov ГК "Митра"
    22 мин

    Non-standard 1C cluster

    • Migration of the standard 1C cluster to MPI environment - "machine to machine migration of services".
    • PostgreSQL migration to GPU powered machine.

  • Andrey Borodin
    Andrey Borodin Яндекс
    45 мин

    DIY database index

    I'm going to talk about emerging technologies in the area of general purpose RDBMS indexing. I will describe different approaches suitable for different workloads. We will discuss ideas from academic researches and corresponding industrial response from developers, communities, and companies. There will be the short live-coding session on creating DIY index in PostgreSQL.

  • Alex Lustin
    Alex Lustin SilverBulleters, LLC
    22 мин

    Analysis of troublesome queries as a means of recurrent refactoring of 1C code

    1. Principles of searching for troublesome queries in PostgreSQL.
    2. Evaluation of hypothetical indexes and their impact on query plans.
    3. The most common errors in 1C-programming.
    4. Basic methods of code refactoring, taking into account the features of PostgreSQL.
    5. Storing analytical information from the PostgreSQL log to assess the quality of refactoring