explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iy0e

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 157,002.983 ↑ 1.0 20 1

Limit (cost=38,966.43..38,966.48 rows=20 width=1,222) (actual time=157,002.980..157,002.983 rows=20 loops=1)

2. 0.504 157,002.980 ↑ 10.8 20 1

Sort (cost=38,966.43..38,966.97 rows=217 width=1,222) (actual time=157,002.979..157,002.980 rows=20 loops=1)

  • Sort Key: (COALESCE(b.actualdeparturetime, b.estimateddeparturetime, b.filed_departuretime, b.filed_time, b.actualarrivaltime, b.cancellation)) DESC, b.diverted DESC
  • Sort Method: top-N heapsort Memory: 41kB
3. 5.456 157,002.476 ↓ 2.6 554 1

HashAggregate (cost=38,958.48..38,960.65 rows=217 width=1,222) (actual time=157,001.673..157,002.476 rows=554 loops=1)

  • Group Key: i.ident, ((a.aircrafttype)::character varying), ((b.aircraftprefix)::character varying), ((b.aircraftsuffix)::character varying), ((SubPlan 1)), ((SubPlan 2)), b.estimateddeparturetime, b.estimatedarrivaltime, (COALESCE(b.actualarrivaltime, b.act_block_in)), (COALESCE(b.actualarrivaltime, b.cancellation, b.act_block_in)), b.actualdeparturetime, (COALESCE(b.filed_departuretime, b.actualdeparturetime)), (round(date_part('epoch'::text, b.filed_ete))), b.filed_ete, b.filed_altitude, b.filed_airspeed_kts, b.filed_airspeed_mach, b.filed_time, r.route, b.userclass, b.physclass, b.cancellation, b.id, b.flight_id, (barr_lookup(i.ident, (b.filed_departuretime)::date)), b.filed, ((b.filed_departuretime + b.filed_ete)), b.fp_id, b.est_block_in, b.est_block_out, b.sch_block_in, b.sch_block_out, b.act_block_in, b.act_block_out, b.delays, i2.ident, b.atc_ident, b.modes_hex, r.route_distance, b.gate_orig, b.gate_dest, b.terminal_orig, b.terminal_dest, b.bag_claim, b.diverted, ((SubPlan 3)), (CASE WHEN b.diverted THEN (SubPlan 5) ELSE NULL::character varying END), b.true_cancel, b.meal_service, b.seats_cabin_first, b.seats_cabin_business, b.seats_cabin_coach, b.fs_act_block_in, b.fs_act_block_out, b.fs_act_facility_destination, b.fs_act_facility_origin, b.fs_sch_facility_destination, b.fs_sch_facility_origin, b.fs_avionics_off, b.fs_avionics_on, b.act_runway_origin, b.act_runway_destination, b.predicted_out, b.predicted_off, b.predicted_on, b.predicted_in, o.status, o.comments, o.user_id, (COALESCE(b.actualdeparturetime, b.estimateddeparturetime, b.filed_departuretime, b.filed_time, b.actualarrivaltime, b.cancellation)), b.pedigree, b.view_www, b.adhoc, (CASE WHEN ((b.result_unknown IS TRUE) OR ((o.status)::text = 'UNKNOWN'::text) OR ((b.actualdeparturetime IS NOT NULL) AND (b.actualarrivaltime = b.actualdeparturetime))) THEN true ELSE false END), (false), b.last_seen, (round(date_part('epoch'::text, timezone('UTC'::text, COALESCE(b.sch_block_out, b.filed_departuretime))))), (round(date_part('epoch'::text, timezone('UTC'::text, COALESCE(b.sch_block_in, b.estimatedarrivaltime))))), (round(date_part('epoch'::text, timezone('UTC'::text, COALESCE(b.est_block_out, b.sch_block_out, b.estimateddeparturetime, b.filed_departuretime))))), (round(date_part('epoch'::text, COALESCE(b.filed_departuretime, b.filed_time, b.actualdeparturetime))))
4. 0.335 156,997.020 ↓ 3.6 788 1

Append (cost=2.56..38,915.08 rows=217 width=1,222) (actual time=3.365..156,997.020 rows=788 loops=1)

5. 43.960 57.840 ↓ 2.9 400 1

Nested Loop Left Join (cost=2.56..12,495.27 rows=138 width=874) (actual time=3.364..57.840 rows=400 loops=1)

6. 0.379 8.624 ↓ 2.9 400 1

Nested Loop Left Join (cost=2.29..9,193.55 rows=138 width=581) (actual time=0.089..8.624 rows=400 loops=1)

7. 0.324 5.445 ↓ 2.9 400 1

Nested Loop Left Join (cost=1.73..9,112.91 rows=138 width=490) (actual time=0.085..5.445 rows=400 loops=1)

8. 0.378 4.321 ↓ 2.9 400 1

Nested Loop Left Join (cost=1.29..9,050.80 rows=138 width=487) (actual time=0.080..4.321 rows=400 loops=1)

9. 0.234 3.143 ↓ 2.9 400 1

Nested Loop (cost=1.01..9,008.71 rows=138 width=485) (actual time=0.074..3.143 rows=400 loops=1)

10. 0.031 0.031 ↑ 1.0 1 1

Index Scan using bd_ident_index on bd_ident i (cost=0.43..2.65 rows=1 width=11) (actual time=0.030..0.031 rows=1 loops=1)

  • Index Cond: ((ident)::text = 'N605KA'::text)
11. 2.878 2.878 ↑ 14.4 400 1

Index Scan using bdata_ident on bdata_forks b (cost=0.57..8,948.55 rows=5,751 width=482) (actual time=0.040..2.878 rows=400 loops=1)

  • Index Cond: (ident_id = i.id)
  • Filter: (((actualdeparturetime IS NOT NULL) OR (actualarrivaltime IS NOT NULL) OR (true_cancel IS NOT NULL)) AND ((view_www IS NULL) OR (view_www IS TRUE)))
  • Rows Removed by Filter: 444
12. 0.800 0.800 ↑ 1.0 1 400

Index Scan using bd_aircraft_pkey on bd_aircraft a (cost=0.29..0.31 rows=1 width=10) (actual time=0.002..0.002 rows=1 loops=400)

  • Index Cond: (id = b.aircraft_id)
13. 0.800 0.800 ↑ 1.0 1 400

Index Scan using bd_ident_pkey on bd_ident i2 (cost=0.43..0.45 rows=1 width=11) (actual time=0.002..0.002 rows=1 loops=400)

  • Index Cond: (id = b.reg_id)
14. 2.800 2.800 ↑ 1.0 1 400

Index Scan using bd_route_pkey on bd_route r (cost=0.56..0.58 rows=1 width=99) (actual time=0.007..0.007 rows=1 loops=400)

  • Index Cond: (id = b.route_id)
15. 0.800 0.800 ↓ 0.0 0 400

Index Scan using flight_status_override_flight_id_idx on flight_status_override o (cost=0.27..0.29 rows=1 width=80) (actual time=0.002..0.002 rows=0 loops=400)

  • Index Cond: ((flight_id)::text = (b.flight_id)::text)
  • Filter: ((deleted IS NULL) AND (enabled IS TRUE))
16.          

SubPlan (for Nested Loop Left Join)

17. 1.200 1.200 ↑ 1.0 1 400

Index Scan using bd_airport_pkey on bd_airport (cost=0.57..2.79 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=400)

  • Index Cond: (id = b.origin_id)
18. 1.200 1.200 ↑ 1.0 1 400

Index Scan using bd_airport_pkey on bd_airport bd_airport_1 (cost=0.57..2.79 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=400)

  • Index Cond: (id = b.destination_id)
19. 2.000 2.000 ↓ 0.0 0 400

Index Scan using bdata_flight_id_new on bdata_forks b_2 (cost=0.70..13.04 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=400)

  • Index Cond: ((flight_id)::text = (b.flight_id)::text)
  • Filter: (diverted AND (id <> b.id) AND (cancellation < COALESCE((b.cancellation)::timestamp with time zone, CURRENT_TIMESTAMP)))
  • Rows Removed by Filter: 1
20. 0.007 0.056 ↑ 1.0 1 7

Limit (cost=0.70..4.71 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=7)

21. 0.028 0.049 ↑ 10.0 1 7

Index Scan using bdata_flight_id_new on bdata_forks b_3 (cost=0.70..40.82 rows=10 width=32) (actual time=0.007..0.007 rows=1 loops=7)

  • Index Cond: ((flight_id)::text = (b.flight_id)::text)
  • Filter: ((diverted IS NULL) AND (id <> b.id))
  • Rows Removed by Filter: 0
22.          

SubPlan (for Index Scan)

23. 0.021 0.021 ↑ 1.0 1 7

Index Scan using bd_airport_pkey on bd_airport bd_airport_2 (cost=0.57..2.79 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=7)

  • Index Cond: (id = b_3.destination_id)
24. 89.107 156,938.845 ↓ 4.9 388 1

Nested Loop Left Join (cost=2.56..26,416.56 rows=79 width=874) (actual time=1,580.002..156,938.845 rows=388 loops=1)

25. 1.064 156,840.737 ↓ 4.9 388 1

Nested Loop Left Join (cost=2.29..24,526.44 rows=79 width=581) (actual time=1,579.723..156,840.737 rows=388 loops=1)

26. 1.157 156,836.181 ↓ 4.9 388 1

Nested Loop Left Join (cost=1.73..24,480.18 rows=79 width=490) (actual time=1,579.718..156,836.181 rows=388 loops=1)

27. 1.757 156,833.084 ↓ 4.9 388 1

Nested Loop Left Join (cost=1.44..24,456.08 rows=79 width=488) (actual time=1,579.713..156,833.084 rows=388 loops=1)

28. 1.130 156,829.387 ↓ 4.9 388 1

Nested Loop (cost=1.01..24,420.52 rows=79 width=485) (actual time=1,579.699..156,829.387 rows=388 loops=1)

29. 0.026 0.026 ↑ 1.0 1 1

Index Scan using bd_ident_index on bd_ident i2_1 (cost=0.43..2.65 rows=1 width=11) (actual time=0.004..0.026 rows=1 loops=1)

  • Index Cond: ((ident)::text = 'N605KA'::text)
30. 156,828.231 156,828.231 ↑ 15.7 388 1

Index Scan using bdata_reg_all on bdata_forks b_1 (cost=0.57..24,356.95 rows=6,092 width=482) (actual time=1,579.690..156,828.231 rows=388 loops=1)

  • Index Cond: (reg_id = i2_1.id)
  • Filter: (((actualdeparturetime IS NOT NULL) OR (actualarrivaltime IS NOT NULL) OR (true_cancel IS NOT NULL)) AND (pedigree_is_best(flight_id, 7711652) IS TRUE))
  • Rows Removed by Filter: 433
31. 1.940 1.940 ↑ 1.0 1 388

Index Scan using bd_ident_pkey on bd_ident i_1 (cost=0.43..0.45 rows=1 width=11) (actual time=0.005..0.005 rows=1 loops=388)

  • Index Cond: (id = b_1.ident_id)
32. 1.940 1.940 ↑ 1.0 1 388

Index Scan using bd_aircraft_pkey on bd_aircraft a_1 (cost=0.29..0.31 rows=1 width=10) (actual time=0.005..0.005 rows=1 loops=388)

  • Index Cond: (id = b_1.aircraft_id)
33. 3.492 3.492 ↑ 1.0 1 388

Index Scan using bd_route_pkey on bd_route r_1 (cost=0.56..0.59 rows=1 width=99) (actual time=0.009..0.009 rows=1 loops=388)

  • Index Cond: (id = b_1.route_id)
34. 1.940 1.940 ↓ 0.0 0 388

Index Scan using flight_status_override_flight_id_idx on flight_status_override o_1 (cost=0.27..0.29 rows=1 width=80) (actual time=0.005..0.005 rows=0 loops=388)

  • Index Cond: ((flight_id)::text = (b_1.flight_id)::text)
  • Filter: ((deleted IS NULL) AND (enabled IS TRUE))
35.          

SubPlan (for Nested Loop Left Join)

36. 1.552 1.552 ↑ 1.0 1 388

Index Scan using bd_airport_pkey on bd_airport bd_airport_3 (cost=0.57..2.79 rows=1 width=20) (actual time=0.003..0.004 rows=1 loops=388)

  • Index Cond: (id = b_1.origin_id)
37. 1.164 1.164 ↑ 1.0 1 388

Index Scan using bd_airport_pkey on bd_airport bd_airport_4 (cost=0.57..2.79 rows=1 width=20) (actual time=0.002..0.003 rows=1 loops=388)

  • Index Cond: (id = b_1.destination_id)
38. 4.268 4.268 ↓ 0.0 0 388

Index Scan using bdata_flight_id_new on bdata_forks b_4 (cost=0.70..13.04 rows=1 width=0) (actual time=0.011..0.011 rows=0 loops=388)

  • Index Cond: ((flight_id)::text = (b_1.flight_id)::text)
  • Filter: (diverted AND (id <> b_1.id) AND (cancellation < COALESCE((b_1.cancellation)::timestamp with time zone, CURRENT_TIMESTAMP)))
  • Rows Removed by Filter: 1
39. 0.014 0.077 ↑ 1.0 1 7

Limit (cost=0.70..4.71 rows=1 width=32) (actual time=0.011..0.011 rows=1 loops=7)

40. 0.042 0.063 ↑ 10.0 1 7

Index Scan using bdata_flight_id_new on bdata_forks b_5 (cost=0.70..40.82 rows=10 width=32) (actual time=0.009..0.009 rows=1 loops=7)

  • Index Cond: ((flight_id)::text = (b_1.flight_id)::text)
  • Filter: ((diverted IS NULL) AND (id <> b_1.id))
  • Rows Removed by Filter: 0
41.          

SubPlan (for Index Scan)

42. 0.021 0.021 ↑ 1.0 1 7

Index Scan using bd_airport_pkey on bd_airport bd_airport_5 (cost=0.57..2.79 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=7)

  • Index Cond: (id = b_5.destination_id)
Planning time : 9.245 ms
Execution time : 157,005.070 ms