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

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

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

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

Можно заметить, что для вычисления минимума и максимума выполняется полное сканирование таблицы 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




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