# Пример оптимизации запроса

Задача: вычислить среднюю стоимость билетов на перелеты, выполняемые различными типами самолетов. При этом исключить самые дешевые и самые дорогие билеты.

Сразу отключим параллельное выполнение. В виртуальной машине с одним ядром оно не поможет, а параллельные планы сложнее читать.

    => SET max_parallel_workers_per_gather = 0;

    SET

Начинаем со следующего запроса, который решает задачу, но работает медленно:

    => \timing on

    Timing is on.

    => SELECT a.aircraft_code, (
      SELECT round(avg(tf.amount))
      FROM flights f
        JOIN ticket_flights tf ON tf.flight_id = f.flight_id
      WHERE f.aircraft_code = a.aircraft_code
        AND tf.amount > (SELECT min(amount) FROM ticket_flights)
        AND tf.amount < (SELECT max(amount) FROM ticket_flights)
    )
    FROM aircrafts a
    GROUP BY a.aircraft_code;

     aircraft_code | round
    ---------------+-------
     319           | 51546
     320           |
     321           | 15666
     733           | 16545
     763           | 34262
     773           | 23783
     CN1           |  6586
     CR2           | 13228
     SU9           | 14265
    (9 rows)

    Time: 36559,895 ms (00:36,560)

    => \timing off

    Timing is off.

Посмотрим схему плана выполнения:

    => EXPLAIN (COSTS OFF) SELECT a.aircraft_code, (
      SELECT round(avg(tf.amount))
      FROM flights f
        JOIN ticket_flights tf ON tf.flight_id = f.flight_id
      WHERE f.aircraft_code = a.aircraft_code
        AND tf.amount > (SELECT min(amount) FROM ticket_flights)
        AND tf.amount < (SELECT max(amount) FROM ticket_flights)
    )
    FROM aircrafts a
    GROUP BY a.aircraft_code;

                                   QUERY PLAN
    ------------------------------------------------------------------------
     Group
       Group Key: ml.aircraft_code
       ->  Index Only Scan using aircrafts_pkey on aircrafts_data ml
       SubPlan 3
         ->  Aggregate
               InitPlan 1 (returns $0)
                 ->  Aggregate
                       ->  Seq Scan on ticket_flights
               InitPlan 2 (returns $1)
                 ->  Aggregate
                       ->  Seq Scan on ticket_flights ticket_flights_1
               ->  Hash Join
                     Hash Cond: (tf.flight_id = f.flight_id)
                     ->  Seq Scan on ticket_flights tf
                           Filter: ((amount > $0) AND (amount < $1))
                     ->  Hash
                           ->  Seq Scan on flights f
                                 Filter: (aircraft_code = ml.aircraft_code)
    (18 rows)

- Основной запрос состоит из сканирования aircrafts_data по индексу и группировка.
- Узел SubPlan соответствует подзапросу, вычисляющему стоимость - он выполняется в цикле.
- Узлы InitPlan соответствуют подзапросам, вычисляющим минимум и максимум - они выполняются один раз (но для каждого выполнения SubPlan, в который они вложены).

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

    => CREATE INDEX ON ticket_flights(amount);

    CREATE INDEX

    => EXPLAIN (ANALYZE, TIMING OFF) SELECT a.aircraft_code, (
      SELECT round(avg(tf.amount))
      FROM flights f
        JOIN ticket_flights tf ON tf.flight_id = f.flight_id
      WHERE f.aircraft_code = a.aircraft_code
        AND tf.amount > (SELECT min(amount) FROM ticket_flights)
        AND tf.amount < (SELECT max(amount) FROM ticket_flights)
    )
    FROM aircrafts a
    GROUP BY a.aircraft_code;

                                                                                               QUERY PLAN
    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
     Group  (cost=0.14..613333.61 rows=9 width=48) (actual rows=9 loops=1)
       Group Key: ml.aircraft_code
       ->  Index Only Scan using aircrafts_pkey on aircrafts_data ml  (cost=0.14..12.27 rows=9 width=16) (actual rows=9 loops=1)
             Heap Fetches: 9
       SubPlan 5
         ->  Aggregate  (cost=68146.80..68146.81 rows=1 width=32) (actual rows=1 loops=9)
               InitPlan 2 (returns $1)
                 ->  Result  (cost=0.49..0.50 rows=1 width=32) (actual rows=1 loops=1)
                       InitPlan 1 (returns $0)
                         ->  Limit  (cost=0.43..0.49 rows=1 width=6) (actual rows=1 loops=1)
                               ->  Index Only Scan using ticket_flights_amount_idx on ticket_flights  (cost=0.43..488182.63 rows=8391852 width=6) (actual rows=1 loops=1)
                                     Index Cond: (amount IS NOT NULL)
                                     Heap Fetches: 1
               InitPlan 4 (returns $3)
                 ->  Result  (cost=0.49..0.50 rows=1 width=32) (actual rows=1 loops=1)
                       InitPlan 3 (returns $2)
                         ->  Limit  (cost=0.43..0.49 rows=1 width=6) (actual rows=1 loops=1)
                               ->  Index Only Scan Backward using ticket_flights_amount_idx on ticket_flights ticket_flights_1  (cost=0.43..488182.63 rows=8391852 width=6) (actual rows=1 loops=1)
                                     Index Cond: (amount IS NOT NULL)
                                     Heap Fetches: 1
               ->  Hash Join  (cost=6332.08..68132.68 rows=5245 width=6) (actual rows=925529 loops=9)
                     Hash Cond: (tf.flight_id = f.flight_id)
                     ->  Bitmap Heap Scan on ticket_flights tf  (cost=894.51..62584.97 rows=41959 width=10) (actual rows=8329761 loops=8)
                           Recheck Cond: ((amount > $1) AND (amount < $3))
                           Heap Blocks: exact=528624
                           ->  Bitmap Index Scan on ticket_flights_amount_idx  (cost=0.00..884.02 rows=41959 width=0) (actual rows=8329761 loops=8)
                                 Index Cond: ((amount > $1) AND (amount < $3))
                     ->  Hash  (cost=5101.84..5101.84 rows=26858 width=4) (actual rows=23874 loops=9)
                           Buckets: 65536 (originally 32768)  Batches: 1 (originally 1)  Memory Usage: 1551kB
                           ->  Seq Scan on flights f  (cost=0.00..5101.84 rows=26858 width=4) (actual rows=23874 loops=9)
                                 Filter: (aircraft_code = ml.aircraft_code)
                                 Rows Removed by Filter: 190993
     Planning time: 1.396 ms
     Execution time: 67516.750 ms
    (34 rows)

Все только ухудшилось. Почему?

Теперь все три обращения к таблице ticket_flights выполняются с помощью индекса. Но в случае tf планировщик сильно ошибается с оценкой кардинальности: вместо ожидаемых 40 тыс строк читаются практически все строки из 8 млн. Очевидно, что использовать для этого индекс неэффективно.

Запретить индексный доступ можно разными средствами. Например, переписать условие так, чтобы индекс не мог применяться (добавить 0 к amount слева от оператора сравнения).

Но лучше исправить оценку планировщика. Сейчас он не знает значений, которые будут выбраны подзапросами, потому оценивает "из общих соображений", но их можно вычислить заранее и подставить в основной запрос.

Чтобы получить согласованные данные, используем уровень изоляции Repeatable Read:

    => BEGIN ISOLATION LEVEL REPEATABLE READ;

    BEGIN

    => SELECT max(amount) AS a_max, min(amount) AS a_min
    FROM ticket_flights \gset

    => EXPLAIN (ANALYZE, TIMING OFF) SELECT a.aircraft_code, (
      SELECT round(avg(tf.amount))
      FROM flights f
        JOIN ticket_flights tf ON tf.flight_id = f.flight_id
      WHERE f.aircraft_code = a.aircraft_code
        AND tf.amount > :a_min
        AND tf.amount < :a_max
    )
    FROM aircrafts a
    GROUP BY a.aircraft_code;

                                                              QUERY PLAN
    -------------------------------------------------------------------------------------------------------------------------------
     Group  (cost=0.14..1996648.19 rows=9 width=48) (actual rows=9 loops=1)
       Group Key: ml.aircraft_code
       ->  Index Only Scan using aircrafts_pkey on aircrafts_data ml  (cost=0.14..12.27 rows=9 width=16) (actual rows=9 loops=1)
             Heap Fetches: 9
       SubPlan 1
         ->  Aggregate  (cost=221848.42..221848.43 rows=1 width=32) (actual rows=1 loops=9)
               ->  Hash Join  (cost=5437.56..219246.89 rows=1040610 width=6) (actual rows=925529 loops=9)
                     Hash Cond: (tf.flight_id = f.flight_id)
                     ->  Seq Scan on ticket_flights tf  (cost=0.00..191955.78 rows=8324997 width=10) (actual rows=8329761 loops=8)
                           Filter: ((amount > 3000.00) AND (amount < 203300.00))
                           Rows Removed by Filter: 62091
                     ->  Hash  (cost=5101.84..5101.84 rows=26858 width=4) (actual rows=23874 loops=9)
                           Buckets: 65536 (originally 32768)  Batches: 1 (originally 1)  Memory Usage: 1551kB
                           ->  Seq Scan on flights f  (cost=0.00..5101.84 rows=26858 width=4) (actual rows=23874 loops=9)
                                 Filter: (aircraft_code = ml.aircraft_code)
                                 Rows Removed by Filter: 190993
     Planning time: 1.758 ms
     Execution time: 27912.127 ms
    (18 rows)

Теперь оценки кардинальности исправились и планировщик сам предпочел полное сканирование таблицы tf.

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

    => EXPLAIN (ANALYZE, TIMING OFF) SELECT a.aircraft_code, round(avg(tf.amount))
    FROM aircrafts a
      LEFT JOIN flights f ON f.aircraft_code = a.aircraft_code
      LEFT JOIN ticket_flights tf ON tf.flight_id = f.flight_id
        AND tf.amount > :a_min
        AND tf.amount < :a_max
    GROUP BY a.aircraft_code;

                                                             QUERY PLAN
    -----------------------------------------------------------------------------------------------------------------------------
     HashAggregate  (cost=369679.77..369679.90 rows=9 width=48) (actual rows=9 loops=1)
       Group Key: ml.aircraft_code
       ->  Hash Right Join  (cost=8091.71..328054.78 rows=8324997 width=22) (actual rows=8394051 loops=1)
             Hash Cond: (f.aircraft_code = ml.aircraft_code)
             ->  Hash Right Join  (cost=8090.51..295909.84 rows=8324997 width=10) (actual rows=8394050 loops=1)
                   Hash Cond: (tf.flight_id = f.flight_id)
                   ->  Seq Scan on ticket_flights tf  (cost=0.00..191955.78 rows=8324997 width=10) (actual rows=8329761 loops=1)
                         Filter: ((amount > 3000.00) AND (amount < 203300.00))
                         Rows Removed by Filter: 62091
                   ->  Hash  (cost=4564.67..4564.67 rows=214867 width=8) (actual rows=214867 loops=1)
                         Buckets: 131072  Batches: 2  Memory Usage: 3459kB
                         ->  Seq Scan on flights f  (cost=0.00..4564.67 rows=214867 width=8) (actual rows=214867 loops=1)
             ->  Hash  (cost=1.09..1.09 rows=9 width=16) (actual rows=9 loops=1)
                   Buckets: 1024  Batches: 1  Memory Usage: 5kB
                   ->  Seq Scan on aircrafts_data ml  (cost=0.00..1.09 rows=9 width=16) (actual rows=9 loops=1)
     Planning time: 33.632 ms
     Execution time: 10323.508 ms
    (17 rows)

Вот теперь запрос выполняется эффективно, необходимые данные читаются только один раз.

Можно еще немного улучшить результат, увеличив значение параметра work_mem, чтобы хеш-соединение таблиц flights и ticket_flights выполнялось за один проход без временных файлов.

    => SET work_mem = '8MB';

    SET

    => EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, BUFFERS) SELECT a.aircraft_code, round(avg(tf.amount))
    FROM aircrafts a
      LEFT JOIN flights f ON f.aircraft_code = a.aircraft_code
      LEFT JOIN ticket_flights tf ON tf.flight_id = f.flight_id
        AND tf.amount > :a_min
        AND tf.amount < :a_max
    GROUP BY a.aircraft_code;

                                      QUERY PLAN
    -------------------------------------------------------------------------------
     HashAggregate (actual rows=9 loops=1)
       Group Key: ml.aircraft_code
       Buffers: shared hit=16284 read=52211
       ->  Hash Right Join (actual rows=8394051 loops=1)
             Hash Cond: (f.aircraft_code = ml.aircraft_code)
             Buffers: shared hit=16284 read=52211
             ->  Hash Right Join (actual rows=8394050 loops=1)
                   Hash Cond: (tf.flight_id = f.flight_id)
                   Buffers: shared hit=16283 read=52211
                   ->  Seq Scan on ticket_flights tf (actual rows=8329761 loops=1)
                         Filter: ((amount > 3000.00) AND (amount < 203300.00))
                         Rows Removed by Filter: 62091
                         Buffers: shared hit=13867 read=52211
                   ->  Hash (actual rows=214867 loops=1)
                         Buckets: 262144  Batches: 1  Memory Usage: 6900kB
                         Buffers: shared hit=2416
                         ->  Seq Scan on flights f (actual rows=214867 loops=1)
                               Buffers: shared hit=2416
             ->  Hash (actual rows=9 loops=1)
                   Buckets: 1024  Batches: 1  Memory Usage: 5kB
                   Buffers: shared hit=1
                   ->  Seq Scan on aircrafts_data ml (actual rows=9 loops=1)
                         Buffers: shared hit=1
     Planning time: 0.368 ms
     Execution time: 9585.179 ms
    (25 rows)

На этом можно остановиться, поскольку запрос не делает лишних чтений:

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

      sum
    -------
     68494
    (1 row)

Дальнейших путей для оптимизации запроса не видно (хотя и остаются пути для оптимизации настроек сервера, что является темой курса DBA2).

    => END;

    COMMIT

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