Подготовка

Подключим расширение pg_stat_statements, с помощью которого будем строить профиль. 

=> CREATE EXTENSION pg_stat_statements;

CREATE EXTENSION

=> ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';

ALTER SYSTEM

Подключение библиотеки требует перезагрузки. 

=> \q

student$ sudo pg_ctlcluster 10 main restart

student$ psql demo

Настроим расширение так, чтобы собиралась информация о всех запросах, в том числе вложенных. 

=> SET pg_stat_statements.track = 'all';

SET

Создадим представление, чтобы смотреть на собранную статистику выполнения операторов: 

=> CREATE VIEW statements_v AS
SELECT substring(regexp_replace(query,' +',' ','g') FOR 55) AS query,
       calls,
       round(total_time)/1000 AS time_sec,
       shared_blks_hit + shared_blks_read + shared_blks_written AS shared_blks
FROM pg_stat_statements
ORDER BY total_time DESC;

CREATE VIEW

Здесь мы для простоты показываем только часть полей и выводим только общее число страниц кэша.

Профиль выполнения

Задача: построить отчет, выводящий сводную таблицу количества перевезенных пассажиров: строками отчета должны быть модели самолетов, а столбцами - категории обслуживания.

Рассмотрим следующую реализацию. Сначала создадим функцию, возвращающую число пассажиров для заданной модели и категории обслуживания: 

=> CREATE FUNCTION qty(aircraft_code char, fare_conditions varchar)
RETURNS bigint AS $$
  SELECT count(*)
  FROM flights f 
    JOIN boarding_passes bp ON bp.flight_id = f.flight_id 
    JOIN seats s ON s.aircraft_code = f.aircraft_code AND s.seat_no = bp.seat_no 
  WHERE f.aircraft_code = qty.aircraft_code AND s.fare_conditions = qty.fare_conditions;
$$ STABLE LANGUAGE sql;

CREATE FUNCTION

Для отчета создадим функцию, возвращающую набор строк: 

=> CREATE FUNCTION report()
RETURNS TABLE(model text, economy bigint, comfort bigint, business bigint)
AS $$
DECLARE
  r record;
BEGIN 
  FOR r IN SELECT a.aircraft_code, a.model FROM aircrafts a ORDER BY a.model LOOP
    report.model := r.model;
    report.economy := qty(r.aircraft_code, 'Economy');
    report.comfort := qty(r.aircraft_code, 'Comfort');
    report.business := qty(r.aircraft_code, 'Business');
    RETURN NEXT;
  END LOOP;
END;
$$ STABLE LANGUAGE plpgsql;

CREATE FUNCTION

Сбросим статистику. 

=> SELECT pg_stat_statements_reset();

 pg_stat_statements_reset 
--------------------------
 
(1 row)


=> SELECT * FROM report();

        model        | economy | comfort | business 
---------------------+---------+---------+----------
 Аэробус A319-100    |  337129 |       0 |    70232
 Аэробус A320-200    |       0 |       0 |        0
 Аэробус A321-200    |  649388 |       0 |   127982
 Боинг 737-300       |  589901 |       0 |    59829
 Боинг 767-300       |  817621 |       0 |   127947
 Боинг 777-300       |  895896 |  132571 |    83080
 Бомбардье CRJ-200   | 1155683 |       0 |        0
 Сессна 208 Караван  |  111096 |       0 |        0
 Сухой Суперджет-100 | 2425034 |       0 |   342423
(9 rows)

Посмотрим, какую статистику мы получили: 

=> SELECT * FROM statements_v \gx

-[ RECORD 1 ]--------------------------------------------------------
query       | SELECT * FROM report()
calls       | 1
time_sec    | 25.752
shared_blks | 946027
-[ RECORD 2 ]--------------------------------------------------------
query       | SELECT count(*)                                        +
            |  FROM flights f                                        +
            |  JOIN boarding_passes 
calls       | 27
time_sec    | 25.726
shared_blks | 945587
-[ RECORD 3 ]--------------------------------------------------------
query       | SELECT a.aircraft_code, a.model FROM aircrafts a ORDER 
calls       | 1
time_sec    | 0.002
shared_blks | 21
-[ RECORD 4 ]--------------------------------------------------------
query       | SELECT pg_stat_statements_reset()
calls       | 1
time_sec    | 0
shared_blks | 0

Сбросим статистику и попробуем вывести тот же отчет одним запросом: 

=> SELECT pg_stat_statements_reset();

 pg_stat_statements_reset 
--------------------------
 
(1 row)


=> WITH t AS (
  SELECT f.aircraft_code, 
    count(*) FILTER (WHERE s.fare_conditions = 'Economy') economy,
    count(*) FILTER (WHERE s.fare_conditions = 'Comfort') comfort,
    count(*) FILTER (WHERE s.fare_conditions = 'Business') business 
  FROM flights f 
    JOIN boarding_passes bp ON bp.flight_id = f.flight_id 
    JOIN seats s ON s.aircraft_code = f.aircraft_code AND s.seat_no = bp.seat_no 
  GROUP BY f.aircraft_code
)
SELECT a.model,
       coalesce(t.economy,0) economy, 
       coalesce(t.comfort,0) comfort, 
       coalesce(t.business,0) business 
  FROM aircrafts a LEFT JOIN t ON a.aircraft_code = t.aircraft_code 
  ORDER BY a.model;

        model        | economy | comfort | business 
---------------------+---------+---------+----------
 Аэробус A319-100    |  337129 |       0 |    70232
 Аэробус A320-200    |       0 |       0 |        0
 Аэробус A321-200    |  649388 |       0 |   127982
 Боинг 737-300       |  589901 |       0 |    59829
 Боинг 767-300       |  817621 |       0 |   127947
 Боинг 777-300       |  895896 |  132571 |    83080
 Бомбардье CRJ-200   | 1155683 |       0 |        0
 Сессна 208 Караван  |  111096 |       0 |        0
 Сухой Суперджет-100 | 2425034 |       0 |   342423
(9 rows)

Посмотрим статистику теперь: 

=> SELECT * FROM statements_v \gx

-[ RECORD 1 ]----------------------------------
query       | WITH t AS (                      +
            |  SELECT f.aircraft_code,         +
            |  count(*) FILTER 
calls       | 1
time_sec    | 8.061
shared_blks | 57086
-[ RECORD 2 ]----------------------------------
query       | SELECT pg_stat_statements_reset()
calls       | 1
time_sec    | 0
shared_blks | 0

Обратите внимание, как уменьшилось время выполнения запроса и насколько меньше страниц пришлось прочитать благодаря устранению избыточных чтений.

Можно посчитать общее количество страниц во всех задействованных таблицах: 

=> SELECT sum(relpages)
  FROM pg_class 
  WHERE relname IN ('flights','boarding_passes','aircrafts','seats');

  sum  
-------
 57085
(1 row)

Это число может служить грубой оценкой сверху для запроса, которому требуются все строки: обработка существенно большего числа страниц может говорить о том, что данные перебираются по нескольку раз.

Конец демонстрации.