PgConf.Russia 2017

Currently, to execute SQL queries PostgreSQL uses interpreter, which implements Volcano-style iteration model. At the same time it’s possible to get significant speedup by dynamically JIT-compiling query “on-the-fly”. In this case it’s possible to generate code that is specialized for given SQL query, and perform compiler optimizations using the information about table structure and data types that is already known at run time. This approach is especially important for complex queries, which performance is CPU-bound.

Query optimization is an important problem, which solution has a great influence on DBMS performance, especially for complex queries. In this talk we consider PostgreSQL query optimizer and specifically cardinality estimation problem for correlated clauses, which is one of the most well-known drawbacks of query optimizers in general. In the talk we propose our solution for this problem which involves machine learning methods and is available for PostgreSQL 9.6 as an extension with a patch. We discuss the experimental evaluation, advantages, disadvantages, and fields of application of the proposed approach as well.

VIDEO

This tutorial shows how to properly monitor PostgreSQL. We will discuss the mamonsu utility, see how to configure it, examine its hidden features and learn how to extend them.