# Оптимизация запроса

Отключим параллельное выполнение.

    => SET max_parallel_workers_per_gather = 0;

    SET

    => EXPLAIN (ANALYZE, TIMING OFF) SELECT t.*
    FROM  tickets t,
          ticket_flights tf,
          flights f
    WHERE tf.ticket_no = t.ticket_no
      AND f.flight_id = tf.flight_id
      AND tf.fare_conditions = 'Business'
      AND f.actual_departure > f.scheduled_departure + interval '5 hour';

                                                               QUERY PLAN
    ---------------------------------------------------------------------------------------------------------------------------------
     Merge Join  (cost=210524.16..361374.43 rows=285603 width=104) (actual rows=2 loops=1)
       Merge Cond: (t.ticket_no = tf.ticket_no)
       ->  Index Scan using tickets_pkey on tickets t  (cost=0.43..138478.98 rows=2949570 width=104) (actual rows=1336684 loops=1)
       ->  Materialize  (cost=210523.63..211951.65 rows=285603 width=14) (actual rows=2 loops=1)
             ->  Sort  (cost=210523.63..211237.64 rows=285603 width=14) (actual rows=2 loops=1)
                   Sort Key: tf.ticket_no
                   Sort Method: quicksort  Memory: 17kB
                   ->  Hash Join  (cost=6534.28..179760.29 rows=285603 width=14) (actual rows=2 loops=1)
                         Hash Cond: (tf.flight_id = f.flight_id)
                         ->  Seq Scan on ticket_flights tf  (cost=0.00..170976.83 rows=856814 width=18) (actual rows=859656 loops=1)
                               Filter: ((fare_conditions)::text = 'Business'::text)
                               Rows Removed by Filter: 7532196
                         ->  Hash  (cost=5639.00..5639.00 rows=71622 width=4) (actual rows=1 loops=1)
                               Buckets: 131072  Batches: 1  Memory Usage: 513kB
                               ->  Seq Scan on flights f  (cost=0.00..5639.00 rows=71622 width=4) (actual rows=1 loops=1)
                                     Filter: (actual_departure > (scheduled_departure + '05:00:00'::interval))
                                     Rows Removed by Filter: 214866
     Planning time: 18.513 ms
     Execution time: 4485.808 ms
    (19 rows)

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

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

    => CREATE INDEX ON flights ((actual_departure - scheduled_departure));

    CREATE INDEX

    => ANALYZE flights;

    ANALYZE

    => EXPLAIN (ANALYZE, TIMING OFF) SELECT t.*
    FROM  tickets t,
          ticket_flights tf,
          flights f
    WHERE tf.ticket_no = t.ticket_no
      AND f.flight_id = tf.flight_id
      AND tf.fare_conditions = 'Business'
      AND f.actual_departure - f.scheduled_departure > interval '5 hour';

                                                           QUERY PLAN
    -------------------------------------------------------------------------------------------------------------------------
     Nested Loop  (cost=31.97..173277.94 rows=28 width=104) (actual rows=2 loops=1)
       ->  Hash Join  (cost=31.54..173257.54 rows=28 width=14) (actual rows=2 loops=1)
             Hash Cond: (tf.flight_id = f.flight_id)
             ->  Seq Scan on ticket_flights tf  (cost=0.00..170976.83 rows=856814 width=18) (actual rows=859656 loops=1)
                   Filter: ((fare_conditions)::text = 'Business'::text)
                   Rows Removed by Filter: 7532196
             ->  Hash  (cost=31.45..31.45 rows=7 width=4) (actual rows=1 loops=1)
                   Buckets: 1024  Batches: 1  Memory Usage: 5kB
                   ->  Bitmap Heap Scan on flights f  (cost=4.47..31.45 rows=7 width=4) (actual rows=1 loops=1)
                         Recheck Cond: ((actual_departure - scheduled_departure) > '05:00:00'::interval)
                         Heap Blocks: exact=1
                         ->  Bitmap Index Scan on flights_expr_idx  (cost=0.00..4.47 rows=7 width=0) (actual rows=1 loops=1)
                               Index Cond: ((actual_departure - scheduled_departure) > '05:00:00'::interval)
       ->  Index Scan using tickets_pkey on tickets t  (cost=0.43..0.73 rows=1 width=104) (actual rows=1 loops=2)
             Index Cond: (ticket_no = tf.ticket_no)
     Planning time: 6.338 ms
     Execution time: 2097.192 ms
    (17 rows)

Теперь займемся таблицей ticket_flights, которая тоже сканируется полностью, хотя из нее читается незначительная часть строк.

Помог бы индекс по классам обслуживания fare_conditions, но лучше создать индекс по столбцу flight_id, что позволит эффективно выполнять соединение вложенным циклом с flights:

    => CREATE INDEX ON ticket_flights(flight_id);

    CREATE INDEX

    => EXPLAIN (ANALYZE, TIMING OFF) SELECT t.*
    FROM  tickets t,
          ticket_flights tf,
          flights f
    WHERE tf.ticket_no = t.ticket_no
      AND f.flight_id = tf.flight_id
      AND tf.fare_conditions = 'Business'
      AND f.actual_departure - f.scheduled_departure > interval '5 hour';

                                                                      QUERY PLAN
    ----------------------------------------------------------------------------------------------------------------------------------------------
     Nested Loop  (cost=5.34..2801.29 rows=28 width=104) (actual rows=2 loops=1)
       ->  Nested Loop  (cost=4.91..2780.90 rows=28 width=14) (actual rows=2 loops=1)
             ->  Bitmap Heap Scan on flights f  (cost=4.47..31.45 rows=7 width=4) (actual rows=1 loops=1)
                   Recheck Cond: ((actual_departure - scheduled_departure) > '05:00:00'::interval)
                   Heap Blocks: exact=1
                   ->  Bitmap Index Scan on flights_expr_idx  (cost=0.00..4.47 rows=7 width=0) (actual rows=1 loops=1)
                         Index Cond: ((actual_departure - scheduled_departure) > '05:00:00'::interval)
             ->  Index Scan using ticket_flights_flight_id_idx on ticket_flights tf  (cost=0.43..392.68 rows=10 width=18) (actual rows=2 loops=1)
                   Index Cond: (flight_id = f.flight_id)
                   Filter: ((fare_conditions)::text = 'Business'::text)
                   Rows Removed by Filter: 10
       ->  Index Scan using tickets_pkey on tickets t  (cost=0.43..0.73 rows=1 width=104) (actual rows=1 loops=2)
             Index Cond: (ticket_no = tf.ticket_no)
     Planning time: 7.072 ms
     Execution time: 6.965 ms
    (15 rows)

Время выполнения уменьшилось до миллисекунд.