# Подготовка

Подключим расширение 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

- Первым идет основной запрос, который детализируется ниже. К сожалению, нет признака, вложенный это запрос или внешний.
- Вторым идет запрос из функции qty - он вызывался 27 раз.
- Третий - запрос, по которому работает цикл.

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

    => 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)

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

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