Result (cost=320322.22..320322.24 rows=1 width=48) (actual time=7273.048..7273.080 rows=1 loops=1)
CTE dates_to_be
-> Nested Loop (cost=382.21..305276.56 rows=3218 width=56) (actual time=6.424..799.256 rows=109241 loops=1)
Join Filter: ((((unnest(r.days_of_week)))::numeric = EXTRACT(isodow FROM dt.dt)) AND (timezone(ml.timezone, ((dt.dt)::date + r.scheduled_time)) >= lower(routes.validity)) AND (timezone(ml.timezone, ((dt.dt)::date + r.scheduled
_time)) < upper(routes.validity)))
Rows Removed by Join Filter: 680711
-> Hash Join (cost=382.19..1172.41 rows=5792 width=93) (actual time=6.325..53.776 rows=25118 loops=1)
Hash Cond: (ml.timezone = ml_1.timezone)
-> Hash Join (cost=272.95..1047.75 rows=5792 width=78) (actual time=4.453..41.110 rows=25118 loops=1)
Hash Cond: (r.validity = routes.validity)
-> ProjectSet (cost=0.29..482.58 rows=23168 width=56) (actual time=0.141..19.832 rows=25118 loops=1)
-> Nested Loop (cost=0.29..323.30 rows=5792 width=90) (actual time=0.127..10.876 rows=5792 loops=1)
-> Seq Scan on routes r (cost=0.00..155.92 rows=5792 width=79) (actual time=0.034..2.579 rows=5792 loops=1)
-> Memoize (cost=0.29..0.32 rows=1 width=19) (actual time=0.001..0.001 rows=1 loops=5792)
Cache Key: r.departure_airport
Cache Mode: logical
Hits: 5719 Misses: 73 Evictions: 0 Overflows: 0 Memory Usage: 9kB
-> Index Only Scan using idx_airports_data_code_timezone on airports_data ml (cost=0.28..0.31 rows=1 width=19) (actual time=0.014..0.014 rows=1 loops=73)
Index Cond: (airport_code = r.departure_airport)
Heap Fetches: 0
-> Hash (cost=200.26..200.26 rows=5792 width=22) (actual time=4.276..4.278 rows=20 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 66kB
-> HashAggregate (cost=142.34..200.26 rows=5792 width=22) (actual time=4.202..4.264 rows=20 loops=1)
Group Key: routes.validity
Batches: 1 Memory Usage: 217kB
-> Index Only Scan using idx_routes_route_no_validity on routes (cost=0.28..127.86 rows=5792 width=22) (actual time=0.023..1.780 rows=5792 loops=1)
Heap Fetches: 0
-> Hash (cost=105.35..105.35 rows=311 width=15) (actual time=1.833..1.835 rows=311 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 23kB
-> Unique (cost=0.28..105.35 rows=311 width=15) (actual time=0.110..1.752 rows=311 loops=1)
-> Index Only Scan using idx_airports_data_timezone on airports_data ml_1 (cost=0.28..91.60 rows=5501 width=15) (actual time=0.105..1.031 rows=5501 loops=1)
Heap Fetches: 0
-> Function Scan on generate_series dt (cost=0.02..10.02 rows=1000 width=8) (actual time=0.007..0.010 rows=31 loops=25118)
CTE absent
-> Nested Loop Anti Join (cost=2205.65..3530.57 rows=1070 width=104) (actual time=6061.647..6061.655 rows=0 loops=1)
-> Nested Loop (cost=2204.81..2309.40 rows=1073 width=104) (actual time=44.269..950.579 rows=108433 loops=1)
Join Filter: (dtb.scheduled_dep < (array_agg(flights.scheduled_departure ORDER BY flights.scheduled_departure DESC))[1])
Rows Removed by Join Filter: 808
-> Aggregate (cost=2204.81..2204.82 rows=1 width=32) (actual time=37.831..37.833 rows=1 loops=1)
-> Index Only Scan using idx_flights_scheduled_departure on flights (cost=0.29..1933.72 rows=108435 width=8) (actual time=0.095..23.309 rows=108435 loops=1)
Heap Fetches: 0
-> CTE Scan on dates_to_be dtb (cost=0.00..64.36 rows=3218 width=104) (actual time=6.428..881.187 rows=109241 loops=1)
-> Nested Loop (cost=0.85..1.13 rows=1 width=37) (actual time=0.047..0.047 rows=1 loops=108433)
-> Nested Loop (cost=0.56..0.82 rows=1 width=41) (actual time=0.043..0.043 rows=1 loops=108433)
Join Filter: (dtb.validity = r_1.validity)
-> Index Only Scan using flights_route_no_scheduled_departure_key on flights f (cost=0.42..0.64 rows=1 width=15) (actual time=0.004..0.004 rows=1 loops=108433)
Index Cond: ((route_no = dtb.route_no) AND (scheduled_departure = dtb.scheduled_dep))
Heap Fetches: 0
-> Index Scan using routes_route_no_validity_excl on routes r_1 (cost=0.15..0.17 rows=1 width=33) (actual time=0.038..0.038 rows=1 loops=108433)
Index Cond: ((route_no = f.route_no) AND (validity @> f.scheduled_departure))
-> Index Only Scan using airports_data_pkey on airports_data ml_2 (cost=0.28..0.30 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=108433)
Index Cond: (airport_code = r_1.departure_airport)
Heap Fetches: 0
CTE excess
-> Nested Loop (cost=121.26..11365.11 rows=1151 width=52) (actual time=1211.355..1211.358 rows=0 loops=1)
-> Hash Anti Join (cost=120.97..11313.75 rows=1151 width=41) (actual time=1211.353..1211.355 rows=0 loops=1)
Hash Cond: ((f_1.route_no = dtb_1.route_no) AND (r_2.validity = dtb_1.validity) AND (f_1.scheduled_departure = dtb_1.scheduled_dep))
-> Nested Loop (cost=0.29..11172.22 rows=1176 width=41) (actual time=0.076..1055.050 rows=108435 loops=1)
-> Seq Scan on routes r_2 (cost=0.00..155.92 rows=5792 width=33) (actual time=0.030..1.534 rows=5792 loops=1)
-> Index Scan using idx_flights_route_no on flights f_1 (cost=0.29..1.89 rows=1 width=15) (actual time=0.061..0.179 rows=19 loops=5792)
Index Cond: (route_no = r_2.route_no)
Filter: (r_2.validity @> scheduled_departure)
Rows Removed by Filter: 184
-> Hash (cost=64.36..64.36 rows=3218 width=72) (actual time=69.346..69.347 rows=109241 loops=1)
Buckets: 131072 (originally 4096) Batches: 1 (originally 1) Memory Usage: 8706kB
-> CTE Scan on dates_to_be dtb_1 (cost=0.00..64.36 rows=3218 width=72) (actual time=0.030..26.166 rows=109241 loops=1)
-> Memoize (cost=0.29..0.32 rows=1 width=19) (never executed)
Cache Key: r_2.departure_airport
Cache Mode: logical
-> Index Only Scan using idx_airports_data_code_timezone on airports_data ml_3 (cost=0.28..0.31 rows=1 width=19) (never executed)
Index Cond: (airport_code = r_2.departure_airport)
Heap Fetches: 0
InitPlan 4
-> Aggregate (cost=24.08..24.09 rows=1 width=8) (actual time=6061.655..6061.656 rows=1 loops=1)
-> CTE Scan on absent (cost=0.00..21.40 rows=1070 width=0) (actual time=6061.649..6061.649 rows=0 loops=1)
InitPlan 5
-> Aggregate (cost=25.90..25.91 rows=1 width=8) (actual time=1211.364..1211.364 rows=1 loops=1)
-> CTE Scan on excess (cost=0.00..23.02 rows=1151 width=0) (actual time=1211.357..1211.357 rows=0 loops=1)
InitPlan 6
-> Aggregate (cost=24.08..24.09 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=1)
-> CTE Scan on absent absent_1 (cost=0.00..21.40 rows=1070 width=0) (actual time=0.001..0.001 rows=0 loops=1)
InitPlan 7
-> Aggregate (cost=25.90..25.91 rows=1 width=8) (never executed)
-> CTE Scan on excess excess_1 (cost=0.00..23.02 rows=1151 width=0) (never executed)
InitPlan 8
-> Aggregate (cost=24.08..24.09 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=1)
-> CTE Scan on absent absent_2 (cost=0.00..21.40 rows=1070 width=0) (actual time=0.000..0.001 rows=0 loops=1)
InitPlan 9
-> Aggregate (cost=25.90..25.91 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)
-> CTE Scan on excess excess_2 (cost=0.00..23.02 rows=1151 width=0) (actual time=0.001..0.001 rows=0 loops=1)
Planning Time: 22.980 ms
Execution Time: 7280.800 ms
(91 rows)