Seamless SQL optimization - Nikolay Samokhvalov: PGCon 2020

Описание: From https://www.pgcon.org/events/pgcon_20...

There are two types of analysis and optimization.

The first, macro-analysis, is analyzing the workload as a whole. Usually, it is done using pg_stat_statements or pgBadger

The second one is micro-analysis and the central tool here is the EXPLAIN command.

And there is a huge gap between them, partially covered by auto_explain and pg_qualstats extensions.

In this tutorial, we learn how to establish a smooth and seamless SQL optimization process in your organization? Topics we'll cover:

What are the pros and cons of using pg_stat_statements compared to log analysis performed by pgBadger?

What are the key metrics in macro-analysis and how to choose the most applicable in each case (is it total time consumed by an SQL query group? or average timing, or maybe shared buffers hit and read by the query group per second?);

Closing the gap: how to switch from macro-analysis to micro-analysis (you identified a "bad" SQL group, how to start optimizing it and what is needed to make this process faster / more automated?).

How to simplify the process of adaption of using EXPLAIN command by a wide range of backend developers, what metrics matter and how (timing vs buffers involved).

EXPLAIN visualization technics and their pros and cons (explain.depesz.com, PEV, and explain.dalibo.com, explain.tensor.ru, FlameGraphs for EXPLAIN).

How to accumulate knowledge about SQL optimization, share it with teammates and improve collaboration.