explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4UDq

Settings
# exclusive inclusive rows x rows loops node
1. 0.265 1,914.817 ↑ 1.0 1,000 1
→

Limit (cost=2,664.98..2,708,121.35 rows=1,000 width=2,027) (actual time=446.795..1,914.817 rows=1,000 loops=1)

2. 1.754 1,914.552 ↑ 124.9 1,000 1
→

Nested Loop Left Join (cost=2,664.98..337,865,468.17 rows=124,882 width=2,027) (actual time=446.794..1,914.552 rows=1,000 loops=1)

3. 0.653 865.798 ↑ 124.9 1,000 1
→

Nested Loop Left Join (cost=7.59..5,997,164.70 rows=124,882 width=1,987) (actual time=442.338..865.798 rows=1,000 loops=1)

4. 1.246 863.145 ↑ 124.9 1,000 1
→

Nested Loop Left Join (cost=7.31..5,784,520.54 rows=124,882 width=1,929) (actual time=442.335..863.145 rows=1,000 loops=1)

5. 0.951 856.899 ↑ 124.9 1,000 1
→

Nested Loop Left Join (cost=6.87..4,817,985.81 rows=124,882 width=1,900) (actual time=442.330..856.899 rows=1,000 loops=1)

6. 1.122 852.948 ↑ 124.9 1,000 1
→

Merge Left Join (cost=6.59..4,601,381.47 rows=124,882 width=1,892) (actual time=442.322..852.948 rows=1,000 loops=1)

  • Merge Cond: (flights.id = prep_adjusted_yield_3.id)
  • Join Filter: (prep_adjusted_yield_3.observation_date = ((prep_adjusted_yield.observation_date - '3 days'::interval))::date)
  • Rows Removed by Join Filter: 12
7. 1.058 841.532 ↑ 124.9 1,000 1
→

Nested Loop Left Join (cost=6.30..4,594,013.24 rows=124,882 width=1,820) (actual time=431.558..841.532 rows=1,000 loops=1)

8. 4.967 831.474 ↑ 124.9 1,000 1
→

Merge Left Join (cost=5.87..4,277,519.28 rows=124,882 width=1,675) (actual time=431.186..831.474 rows=1,000 loops=1)

  • Merge Cond: (flights.id = prep_infare_3.id)
  • Join Filter: (prep_infare_3.observation_date = ((prep_infare.observation_date - '3 days'::interval))::date)
  • Rows Removed by Join Filter: 1496
9. 0.620 784.670 ↑ 124.9 1,000 1
→

Nested Loop Left Join (cost=5.44..4,058,982.96 rows=124,882 width=1,671) (actual time=393.433..784.670 rows=1,000 loops=1)

10. 1.102 767.050 ↑ 124.9 1,000 1
→

Nested Loop Left Join (cost=5.01..3,732,005.91 rows=124,882 width=1,667) (actual time=393.428..767.050 rows=1,000 loops=1)

11. 1.262 764.948 ↑ 124.9 1,000 1
→

Nested Loop Left Join (cost=4.57..3,405,028.86 rows=124,882 width=1,659) (actual time=393.421..764.948 rows=1,000 loops=1)

12. 1.444 762.686 ↑ 124.9 1,000 1
→

Nested Loop Left Join (cost=4.13..3,078,051.81 rows=124,882 width=1,651) (actual time=393.399..762.686 rows=1,000 loops=1)

13. 1.982 760.242 ↑ 124.9 1,000 1
→

Nested Loop Left Join (cost=3.69..2,751,074.76 rows=124,882 width=1,631) (actual time=393.384..760.242 rows=1,000 loops=1)

14. 0.723 747.260 ↑ 124.9 1,000 1
→

Nested Loop Left Join (cost=3.26..2,424,097.71 rows=124,882 width=1,623) (actual time=392.360..747.260 rows=1,000 loops=1)

15. 1.778 726.537 ↑ 124.9 1,000 1
→

Nested Loop Left Join (cost=2.83..2,174,117.55 rows=124,882 width=1,619) (actual time=391.459..726.537 rows=1,000 loops=1)

16. 0.737 684.759 ↑ 124.9 1,000 1
→

Merge Left Join (cost=2.41..1,896,570.99 rows=124,882 width=595) (actual time=390.824..684.759 rows=1,000 loops=1)

  • Merge Cond: (flights.id = prep_adjusted_yield.id)
17. 0.426 681.842 ↑ 124.9 1,000 1
→

Merge Left Join (cost=2.13..1,895,911.19 rows=124,882 width=509) (actual time=388.369..681.842 rows=1,000 loops=1)

  • Merge Cond: (flights.id = prep_sf_forecast.id)
18. 0.830 681.409 ↑ 124.9 1,000 1
→

Merge Left Join (cost=1.97..1,894,709.85 rows=124,882 width=505) (actual time=388.360..681.409 rows=1,000 loops=1)

  • Merge Cond: (flights.id = prep_held.id)
19. 0.939 679.443 ↑ 124.9 1,000 1
→

Nested Loop Left Join (cost=1.69..1,891,636.22 rows=124,882 width=290) (actual time=387.098..679.443 rows=1,000 loops=1)

20. 1.775 468.504 ↑ 124.9 1,000 1
→

Merge Left Join (cost=1.27..1,645,175.88 rows=124,882 width=201) (actual time=386.450..468.504 rows=1,000 loops=1)

  • Merge Cond: (flights.id = prep_infare.id)
21. 3.793 93.431 ↑ 124.9 1,000 1
→

Nested Loop (cost=0.85..1,487,157.03 rows=124,882 width=128) (actual time=76.563..93.431 rows=1,000 loops=1)

22. 29.842 29.842 ↑ 95.4 4,983 1
→

Index Scan using prep_flight_forecast_id on prep_flight_forecast (cost=0.42..405,341.31 rows=475,595 width=56) (actual time=0.007..29.842 rows=4,983 loops=1)

23. 59.796 59.796 ↓ 0.0 0 4,983
→

Index Scan using flights_id_uindex on flights (cost=0.43..2.27 rows=1 width=76) (actual time=0.012..0.012 rows=0 loops=4,983)

  • Index Cond: (id = prep_flight_forecast.id)
  • Filter: (departuredate >= (timezone('AESST'::text, CURRENT_TIMESTAMP))::date)
  • Rows Removed by Filter: 1
24. 373.298 373.298 ↑ 66.5 2,863 1
→

Index Scan using prep_infare_id on prep_infare (cost=0.42..157,110.24 rows=190,532 width=77) (actual time=0.010..373.298 rows=2,863 loops=1)

25. 210.000 210.000 ↑ 1.0 1 1,000
→

Index Scan using prep_od_availability_id on prep_od_availability (cost=0.42..1.97 rows=1 width=93) (actual time=0.210..0.210 rows=1 loops=1,000)

  • Index Cond: (flights.id = id)
26. 1.136 1.136 ↑ 2.0 1,759 1
→

Index Scan using prep_held_id on prep_held (cost=0.28..2,750.32 rows=3,546 width=219) (actual time=0.013..1.136 rows=1,759 loops=1)

27. 0.007 0.007 ↓ 0.0 0 1
→

Index Scan using prep_sf_forecast_id on prep_sf_forecast (cost=0.15..882.75 rows=2,040 width=8) (actual time=0.007..0.007 rows=0 loops=1)

28. 2.180 2.180 ↑ 1.3 3,341 1
→

Index Scan using prep_adjusted_yield_id on prep_adjusted_yield (cost=0.28..333.89 rows=4,377 width=90) (actual time=0.014..2.180 rows=3,341 loops=1)

29. 40.000 40.000 ↑ 1.0 1 1,000
→

Index Scan using id on prep_sf_outliers_averages (cost=0.42..2.22 rows=1 width=1,028) (actual time=0.040..0.040 rows=1 loops=1,000)

  • Index Cond: (flights.id = id)
30. 20.000 20.000 ↑ 1.0 1 1,000
→

Index Scan using flights_ty_ly_id on flights_ty_ly (cost=0.43..2.00 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=1,000)

  • Index Cond: (flights.id = id)
31. 11.000 11.000 ↓ 0.0 0 1,000
→

Index Scan using prep_flight_forecast_multi_id_obs_date on prep_flight_forecast_multi prep_flt_fcst_3 (cost=0.44..2.62 rows=1 width=16) (actual time=0.011..0.011 rows=0 loops=1,000)

  • Index Cond: ((id = flights.id) AND (observation_date = ((prep_flight_forecast.observation_date - '3 days'::interval))::date))
32. 1.000 1.000 ↓ 0.0 0 1,000
→

Index Scan using prep_flight_forecast_multi_id_obs_date on prep_flight_forecast_multi prep_flt_fcst_7 (cost=0.44..2.62 rows=1 width=28) (actual time=0.001..0.001 rows=0 loops=1,000)

  • Index Cond: ((id = flights.id) AND (observation_date = ((prep_flight_forecast.observation_date - '7 days'::interval))::date))
33. 1.000 1.000 ↓ 0.0 0 1,000
→

Index Scan using prep_flight_forecast_multi_id_obs_date on prep_flight_forecast_multi prep_flt_fcst_14 (cost=0.44..2.62 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1,000)

  • Index Cond: ((id = flights.id) AND (observation_date = ((prep_flight_forecast.observation_date - '14 days'::interval))::date))
34. 1.000 1.000 ↓ 0.0 0 1,000
→

Index Scan using prep_flight_forecast_multi_id_obs_date on prep_flight_forecast_multi prep_flt_fcst_21 (cost=0.44..2.62 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=1,000)

  • Index Cond: ((id = flights.id) AND (observation_date = ((prep_flight_forecast.observation_date - '21 days'::interval))::date))
35. 17.000 17.000 ↓ 0.0 0 1,000
→

Index Scan using prep_flight_forecast_multi_id_obs_date on prep_flight_forecast_multi prep_flt_fcst_ly (cost=0.44..2.62 rows=1 width=16) (actual time=0.017..0.017 rows=0 loops=1,000)

  • Index Cond: ((id = flights_ty_ly.ly_id) AND (observation_date = ((prep_flight_forecast.observation_date - '364 days'::interval))::date))
36. 41.837 41.837 ↑ 6.8 56,890 1
→

Index Scan using prep_infare_multi_id_obs_date on prep_infare_multi prep_infare_3 (cost=0.42..216,828.62 rows=387,328 width=16) (actual time=0.013..41.837 rows=56,890 loops=1)

37. 9.000 9.000 ↓ 0.0 0 1,000
→

Index Scan using prep_od_availability_multi_id_obs_date on prep_od_availability_multi prep_od_avl_3 (cost=0.44..2.53 rows=1 width=157) (actual time=0.009..0.009 rows=0 loops=1,000)

  • Index Cond: ((id = flights.id) AND (observation_date = ((prep_od_availability.observation_date - '3 days'::interval))::date))
38. 10.294 10.294 ↑ 1.3 8,700 1
→

Index Scan using prep_adjusted_yield_multi_id_obs_date on prep_adjusted_yield_multi prep_adjusted_yield_3 (cost=0.29..7,014.96 rows=11,400 width=84) (actual time=0.011..10.294 rows=8,700 loops=1)

39. 3.000 3.000 ↓ 0.0 0 1,000
→

Index Scan using prep_flight_forecast_travelled_id on prep_flight_forecast_travelled travelled (cost=0.29..1.73 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=1,000)

  • Index Cond: (id = flights_ty_ly.ly_id)
40. 5.000 5.000 ↓ 0.0 0 1,000
→

Index Scan using prep_held_multi_id_obs_date on prep_held_multi prep_held_ly (cost=0.43..7.74 rows=1 width=41) (actual time=0.005..0.005 rows=0 loops=1,000)

  • Index Cond: ((id = flights_ty_ly.ly_id) AND (observation_date = ((prep_held.observation_date - '364 days'::interval))::date))
41. 2.000 2.000 ↓ 0.0 0 1,000
→

Index Scan using prep_held_travelled_id on prep_held_travelled held_travelled (cost=0.28..1.70 rows=1 width=66) (actual time=0.002..0.002 rows=0 loops=1,000)

  • Index Cond: (id = flights_ty_ly.ly_id)
42. 68.000 1,047.000 ↑ 1.0 1 1,000
→

Aggregate (cost=2,657.39..2,657.40 rows=1 width=32) (actual time=1.047..1.047 rows=1 loops=1,000)

43. 80.000 979.000 ↑ 6.4 22 1,000
→

GroupAggregate (cost=2,650.04..2,654.59 rows=140 width=86) (actual time=0.907..0.979 rows=22 loops=1,000)

  • Group Key: connecting_flights.od, connecting_flights.flightsequence, ((prep_od_connecting_availability.availability)::jsonb)
44. 95.000 899.000 ↑ 3.2 44 1,000
→

Sort (cost=2,650.04..2,650.39 rows=140 width=58) (actual time=0.897..0.899 rows=44 loops=1,000)

  • Sort Key: connecting_flights.od, connecting_flights.flightsequence, ((prep_od_connecting_availability.availability)::jsonb)
  • Sort Method: quicksort Memory: 37kB
45. 95.554 804.000 ↑ 3.2 44 1,000
→

Nested Loop Left Join (cost=27.60..2,645.04 rows=140 width=58) (actual time=0.124..0.804 rows=44 loops=1,000)

46. 11.385 575.000 ↑ 3.2 22 1,000
→

Nested Loop Left Join (cost=14.31..878.73 rows=70 width=100) (actual time=0.109..0.575 rows=22 loops=1,000)

47. 183.000 230.000 ↑ 3.2 22 1,000
→

Bitmap Heap Scan on connecting_flights (cost=13.88..287.40 rows=70 width=38) (actual time=0.071..0.230 rows=22 loops=1,000)

  • Recheck Cond: ((flights.id = flight_id_first) OR (flights.id = flight_id_last) OR (flights.id = flight_id_middle))
  • Heap Blocks: exact=9937
48. 1.000 47.000 ↓ 0.0 0 1,000
→

BitmapOr (cost=13.88..13.88 rows=70 width=0) (actual time=0.047..0.047 rows=0 loops=1,000)

49. 23.000 23.000 ↑ 3.3 11 1,000
→

Bitmap Index Scan on connecting_flights_flight_id_first_index (cost=0.00..4.70 rows=36 width=0) (actual time=0.023..0.023 rows=11 loops=1,000)

  • Index Cond: (flights.id = flight_id_first)
50. 21.000 21.000 ↑ 2.8 12 1,000
→

Bitmap Index Scan on connecting_flights_flight_id_last_index (cost=0.00..4.68 rows=33 width=0) (actual time=0.021..0.021 rows=12 loops=1,000)

  • Index Cond: (flights.id = flight_id_last)
51. 2.000 2.000 ↓ 0.0 0 1,000
→

Bitmap Index Scan on connecting_flights_flight_id_middle_index (cost=0.00..4.44 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1,000)

  • Index Cond: (flights.id = flight_id_middle)
52. 333.615 333.615 ↑ 1.0 1 22,241
→

Index Scan using prep_od_connecting_availability_id on prep_od_connecting_availability (cost=0.43..8.45 rows=1 width=70) (actual time=0.015..0.015 rows=1 loops=22,241)

  • Index Cond: (connecting_flights.id = id)
53. 44.482 133.446 ↑ 1.5 2 22,241
→

Bitmap Heap Scan on prep_flight_forecast cnx (cost=13.29..25.19 rows=3 width=8) (actual time=0.005..0.006 rows=2 loops=22,241)

  • Recheck Cond: ((id = connecting_flights.flight_id_first) OR (id = connecting_flights.flight_id_last) OR (id = connecting_flights.flight_id_middle))
  • Heap Blocks: exact=44303
54. 22.241 88.964 ↓ 0.0 0 22,241
→

BitmapOr (cost=13.29..13.29 rows=3 width=0) (actual time=0.004..0.004 rows=0 loops=22,241)

55. 22.241 22.241 ↑ 1.0 1 22,241
→

Bitmap Index Scan on prep_flight_forecast_id (cost=0.00..4.43 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=22,241)

  • Index Cond: (id = connecting_flights.flight_id_first)
56. 44.482 44.482 ↑ 1.0 1 22,241
→

Bitmap Index Scan on prep_flight_forecast_id (cost=0.00..4.43 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=22,241)

  • Index Cond: (id = connecting_flights.flight_id_last)
57. 0.000 0.000 ↓ 0.0 0 22,241
→

Bitmap Index Scan on prep_flight_forecast_id (cost=0.00..4.43 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=22,241)

  • Index Cond: (id = connecting_flights.flight_id_middle)