Schedule - PostgreSQL Development Conference 2024

Adaptive query optimization in PostgreSQL

Date: 2024-05-29
Time: 11:30–12:20
Room: Sauder(2270)
Level: Intermediate

Currently, applications with auto-generated queries have been becoming increasingly distributed, however it leads the fact that the optimizers of modern DBMS, due to some limitations, cannot find the optimal plan for their execution. Therefore it force to complete them for a long time. Mostly this error occurs due to incorrect cardinality estimation, which is even worse, the error can be repeated by the optimizer again and again.

We would like to explain the mechanism of Adaptive Query Optimization (AQO), which has a built-in "planner memory". The planner's memory is filled by analyzing the query execution tree with saving the actual cardinality. If the problem case is repeated again (same query or another query with the same query subtree), the planner will use this knowledge, which may give it the opportunity to build a correct plan that might give, which will result in faster query execution. We will talk about the principles of design and the interface of the extension, including own statistics included the analyze of query performance. We also show how the extension works with specific examples.