Result (cost=321155.20..321155.22 rows=1 width=48) (actual time=7098.553..7098.594 rows=1 loops=1)
CTE dates_to_be
-> Nested Loop (cost=526.60..305422.97 rows=3218 width=56) (actual time=122.063..1036.124 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.timezon
e, ((dt.dt)::date + r.scheduled_time)) < upper(routes.validity)))
Rows Removed by Join Filter: 680711
-> Hash Join (cost=526.58..1318.82 rows=5792 width=93) (actual time=60.819..117.054 rows=25118 loops=1)
Hash Cond: (ml.timezone = ml_1.timezone)
-> Hash Join (cost=294.82..1071.64 rows=5792 width=78) (actual time=52.662..96.762 rows=25118 loops=1)
Hash Cond: (r.validity = routes.validity)
-> ProjectSet (cost=0.29..484.61 rows=23168 width=56) (actual time=2.183..27.647 rows=25118 loops=1)
-> Nested Loop (cost=0.29..325.33 rows=5792 width=90) (actual time=2.173..16.775 rows=5792 loops=1)
-> Seq Scan on routes r (cost=0.00..155.92 rows=5792 width=79) (actual time=0.977..5.227 rows=5792 loops=1)
-> Memoize (cost=0.29..0.34 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 Scan using airports_data_pkey on airports_data ml (cost=0.28..0.33 rows=1 width=19) (actual time=0.030..0.030 rows=1 loops=73)
Index Cond: (airport_code = r.departure_airport)
-> Hash (cost=222.13..222.13 rows=5792 width=22) (actual time=50.411..50.413 rows=20 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 66kB
-> HashAggregate (cost=164.21..222.13 rows=5792 width=22) (actual time=50.321..50.382 rows=20 loops=1)
Group Key: routes.validity
Batches: 1 Memory Usage: 217kB
-> Index Only Scan using routes_route_no_validity_excl on routes (cost=0.15..149.73 rows=5792 width=22) (actual time=40.852..47.890 rows=5792 loops=1)
Heap Fetches: 0
-> Hash (cost=227.87..227.87 rows=311 width=15) (actual time=8.128..8.130 rows=311 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 23kB
-> HashAggregate (cost=224.76..227.87 rows=311 width=15) (actual time=7.998..8.044 rows=311 loops=1)
Group Key: ml_1.timezone
Batches: 1 Memory Usage: 61kB
-> Seq Scan on airports_data ml_1 (cost=0.00..211.01 rows=5501 width=15) (actual time=0.881..6.353 rows=5501 loops=1)
-> Function Scan on generate_series dt (cost=0.02..10.02 rows=1000 width=8) (actual time=0.013..0.015 rows=31 loops=25118)
CTE absent
-> Nested Loop Anti Join (cost=2519.30..4216.02 rows=1070 width=104) (actual time=6554.975..6554.983 rows=0 loops=1)
InitPlan 2
-> Aggregate (cost=2518.44..2518.45 rows=1 width=8) (actual time=47.305..47.307 rows=1 loops=1)
-> Seq Scan on flights (cost=0.00..2247.35 rows=108435 width=8) (actual time=0.749..33.984 rows=108435 loops=1)
-> CTE Scan on dates_to_be dtb (cost=0.00..72.41 rows=1073 width=104) (actual time=169.383..1183.977 rows=108433 loops=1)
Filter: (scheduled_dep < (InitPlan 2).col1)
Rows Removed by Filter: 808
-> Nested Loop (cost=0.85..1.50 rows=1 width=37) (actual time=0.049..0.049 rows=1 loops=108433)
-> Nested Loop (cost=0.56..1.20 rows=1 width=41) (actual time=0.046..0.046 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..1.02 rows=1 width=15) (actual time=0.005..0.005 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.039..0.039 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.39..11366.23 rows=1151 width=52) (actual time=543.514..543.526 rows=0 loops=1)
-> Hash Anti Join (cost=121.09..11312.85 rows=1151 width=41) (actual time=543.513..543.522 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.42..11171.32 rows=1176 width=41) (actual time=0.086..390.445 rows=108435 loops=1)
-> Seq Scan on routes r_2 (cost=0.00..155.92 rows=5792 width=33) (actual time=0.024..1.198 rows=5792 loops=1)
-> Index Only Scan using flights_route_no_scheduled_departure_key on flights f_1 (cost=0.42..1.89 rows=1 width=15) (actual time=0.036..0.064 rows=19 loops=5792)
Index Cond: (route_no = r_2.route_no)
Filter: (r_2.validity @> scheduled_departure)
Rows Removed by Filter: 184
Heap Fetches: 0
-> Hash (cost=64.36..64.36 rows=3218 width=72) (actual time=75.384..75.385 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.027..26.568 rows=109241 loops=1)
-> Memoize (cost=0.29..0.34 rows=1 width=19) (never executed)
Cache Key: r_2.departure_airport
Cache Mode: logical
-> Index Scan using airports_data_pkey on airports_data ml_3 (cost=0.28..0.33 rows=1 width=19) (never executed)
Index Cond: (airport_code = r_2.departure_airport)
InitPlan 5
-> Aggregate (cost=24.08..24.09 rows=1 width=8) (actual time=6554.986..6554.987 rows=1 loops=1)
-> CTE Scan on absent (cost=0.00..21.40 rows=1070 width=0) (actual time=6554.978..6554.978 rows=0 loops=1)
InitPlan 6
-> Aggregate (cost=25.90..25.91 rows=1 width=8) (actual time=543.534..543.534 rows=1 loops=1)
-> CTE Scan on excess (cost=0.00..23.02 rows=1151 width=0) (actual time=543.517..543.517 rows=0 loops=1)
InitPlan 7
-> Aggregate (cost=24.08..24.09 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=1)
-> CTE Scan on absent absent_1 (cost=0.00..21.40 rows=1070 width=0) (actual time=0.002..0.002 rows=0 loops=1)
InitPlan 8
-> 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 9
-> 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 10
-> Aggregate (cost=25.90..25.91 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)
-> CTE Scan on excess excess_2 (cost=0.00..23.02 rows=1151 width=0) (actual time=0.000..0.001 rows=0 loops=1)
Planning Time: 45.294 ms
Execution Time: 7108.159 ms
(90 rows)