explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wW4K

Settings
# exclusive inclusive rows x rows loops node
1. 1,963.865 42,323.501 ↑ 1.0 100 1

Limit (cost=4,732,654.32..4,733,111.82 rows=100 width=654) (actual time=42,319.421..42,323.501 rows=100 loops=1)

  • Functions: 15
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 14.828 ms, Inlining 39.778 ms, Optimization 1140.102 ms, Emission 782.955 ms, Total 1977.663 ms
2. 4.171 40,359.636 ↑ 11.9 100 1

Result (cost=4,732,654.32..4,738,104.93 rows=1,194 width=654) (actual time=40,355.569..40,359.636 rows=100 loops=1)

3. 0.571 40,355.465 ↑ 11.9 100 1

Sort (cost=4,732,654.32..4,732,657.31 rows=1,194 width=700) (actual time=40,355.452..40,355.465 rows=100 loops=1)

  • Sort Key: ((parse_flight_duration(connections.elapsed_flight_time) + parse_flight_duration(connections_1.elapsed_flight_time))) DESC
  • Sort Method: top-N heapsort Memory: 129kB
4. 7.080 40,354.894 ↑ 5.9 202 1

Nested Loop (cost=494.48..4,732,593.30 rows=1,194 width=700) (actual time=1,296.912..40,354.894 rows=202 loops=1)

5. 107.130 153.756 ↑ 5.2 974 1

Bitmap Heap Scan on connections (cost=307.54..90,511.22 rows=5,067 width=295) (actual time=48.927..153.756 rows=974 loops=1)

  • Recheck Cond: ((departure_airport = 'DTW'::text) AND (departure_dates @> '{2020-08-22}'::date[]))
  • Filter: ((departure_time >= '06:00:00'::time without time zone) AND (departure_time < '16:00:00'::time without time zone) AND (parse_flight_distance(flight_distance) < 600))
  • Rows Removed by Filter: 16,240
  • Heap Blocks: exact=16,756
6. 46.626 46.626 ↑ 1.3 17,214 1

Bitmap Index Scan on by_departure_airport_i (cost=0.00..306.27 rows=22,227 width=0) (actual time=46.626..46.626 rows=17,214 loops=1)

  • Index Cond: ((departure_airport = 'DTW'::text) AND (departure_dates @> '{2020-08-22}'::date[]))
7. 1,067.504 40,194.058 ↓ 0.0 0 974

Bitmap Heap Scan on connections connections_1 (cost=186.94..916.01 rows=1 width=341) (actual time=41.232..41.267 rows=0 loops=974)

  • Recheck Cond: ((departure_airport = connections.arrival_airport) AND (arrival_airport = 'YYZ'::text) AND (departure_dates && '{2020-08-22,2020-08-21,2020-08-22,2020-08-23,2020-08-24,2020-08-25}'::date[]))
  • Filter: ((parse_flight_distance(flight_distance) < 600) AND ((parse_flight_distance(connections.flight_distance) + parse_flight_distance(flight_distance)) < 600) AND ((((compute_leg2_earliest_departure('2020-08-22 06:00:00'::timestamp without time zone, '2020-08-22 16:00:00'::timestamp without time zone, connections.departure_time, connections.arrival_time, 10, connections.flight_arrival_day_indicator))::date = (compute_leg2_latest_departure('2020-08-22 06:00:00'::timestamp without time zone, '2020-08-22 16:00:00'::timestamp without time zone, connections.departure_time, connections.arrival_time, 730, connections.flight_arrival_day_indicator))::date) AND (departure_dates @> ARRAY[(compute_leg2_earliest_departure('2020-08-22 06:00:00'::timestamp without time zone, '2020-08-22 16:00:00'::timestamp without time zone, connections.departure_time, connections.arrival_time, 10, connections.flight_arrival_day_indicator))::date]) AND (departure_time >= (compute_leg2_earliest_departure('2020-08-22 06:00:00'::timestamp without time zone, '2020-08-22 16:00:00'::timestamp without time zone, connections.departure_time, connections.arrival_time, 10, connections.flight_arrival_day_indicator))::time without time zone) AND (departure_time < (compute_leg2_latest_departure('2020-08-22 06:00:00'::timestamp without time zone, '2020-08-22 16:00:00'::timestamp without time zone, connections.departure_time, connections.arrival_time, 730, connections.flight_arrival_day_indicator))::time without time zone)) OR (((compute_leg2_earliest_departure('2020-08-22 06:00:00'::timestamp without time zone, '2020-08-22 16:00:00'::timestamp without time zone, connections.departure_time, connections.arrival_time, 10, connections.flight_arrival_day_indicator))::date <> (compute_leg2_latest_departure('2020-08-22 06:00:00'::timestamp without time zone, '2020-08-22 16:00:00'::timestamp without time zone, connections.departure_time, connections.arrival_time, 730, connections.flight_arrival_day_indicator))::date) AND (((departure_dates @> ARRAY[(compute_leg2_earliest_departure('2020-08-22 06:00:00'::timestamp without time zone, '2020-08-22 16:00:00'::timestamp without time zone, connections.departure_time, connections.arrival_time, 10, connections.flight_arrival_day_indicator))::date]) AND (departure_time >= (compute_leg2_earliest_departure('2020-08-22 06:00:00'::timestamp without time zone, '2020-08-22 16:00:00'::timestamp without time zone, connections.departure_time, connections.arrival_time, 10, connections.flight_arrival_day_indicator))::time without time zone)) OR ((departure_dates @> ARRAY[(compute_leg2_latest_departure('2020-08-22 06:00:00'::timestamp without time zone, '2020-08-22 16:00:00'::timestamp without time zone, connections.departure_time, connections.arrival_time, 730, connections.flight_arrival_day_indicator))::date]) AND (departure_time < (compute_leg2_latest_departure('2020-08-22 06:00:00'::timestamp without time zone, '2020-08-22 16:00:00'::timestamp without time zone, connections.departure_time, connections.arrival_time, 730, connections.flight_arrival_day_indicator))::time without time zone))))))
  • Rows Removed by Filter: 207
  • Heap Blocks: exact=170,223
8. 39,126.554 39,126.554 ↓ 2.1 208 974

Bitmap Index Scan on byroute_i (cost=0.00..186.94 rows=98 width=0) (actual time=40.171..40.171 rows=208 loops=974)

  • Index Cond: ((departure_airport = connections.arrival_airport) AND (arrival_airport = 'YYZ'::text) AND (departure_dates && '{2020-08-22,2020-08-21,2020-08-22,2020-08-23,2020-08-24,2020-08-25}'::date[]))
Execution time : 42,338.625 ms