Отключим параллельное выполнение.
=> 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)
Время выполнения уменьшилось до миллисекунд.