explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1l5I

Settings
# exclusive inclusive rows x rows loops node
1. 0.013 157.390 ↑ 1.0 20 1

Limit (cost=37,094.33..37,094.38 rows=20 width=1,222) (actual time=157.377..157.390 rows=20 loops=1)

2. 0.497 157.377 ↑ 10.8 20 1

Sort (cost=37,094.33..37,094.88 rows=217 width=1,222) (actual time=157.376..157.377 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. 4.141 156.880 ↓ 2.6 554 1

HashAggregate (cost=37,086.39..37,088.56 rows=217 width=1,222) (actual time=156.291..156.880 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.179 152.739 ↓ 3.6 788 1

Append (cost=2.29..37,042.99 rows=217 width=1,222) (actual time=0.507..152.739 rows=788 loops=1)

5. 47.893 64.850 ↓ 2.9 400 1

Nested Loop Left Join (cost=2.29..10,717.26 rows=137 width=897) (actual time=0.507..64.850 rows=400 loops=1)

  • Join Filter: ((o.flight_id)::text = (b.flight_id)::text)
  • Rows Removed by Join Filter: 110,800
6. 0.306 6.494 ↓ 2.9 400 1

Nested Loop Left Join (cost=2.29..8,222.95 rows=137 width=590) (actual time=0.049..6.494 rows=400 loops=1)

7. 0.541 4.188 ↓ 2.9 400 1

Nested Loop Left Join (cost=1.73..8,142.89 rows=137 width=500) (actual time=0.047..4.188 rows=400 loops=1)

8. 0.371 2.847 ↓ 2.9 400 1

Nested Loop Left Join (cost=1.29..8,081.23 rows=137 width=497) (actual time=0.044..2.847 rows=400 loops=1)

9. 0.298 1.676 ↓ 2.9 400 1

Nested Loop (cost=1.01..8,039.44 rows=137 width=495) (actual time=0.041..1.676 rows=400 loops=1)

10. 0.017 0.017 ↑ 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.016..0.017 rows=1 loops=1)

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

Index Scan using bdata_ident on bdata_forks b (cost=0.57..7,986.87 rows=4,992 width=492) (actual time=0.020..1.361 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: (b.aircraft_id = 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: (b.reg_id = id)
14. 2.000 2.000 ↑ 1.0 1 400

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

  • Index Cond: (b.route_id = id)
15. 5.132 5.200 ↓ 277.0 277 400

Materialize (cost=0.00..8.86 rows=1 width=100) (actual time=0.000..0.013 rows=277 loops=400)

16. 0.068 0.068 ↓ 277.0 277 1

Seq Scan on flight_status_override o (cost=0.00..8.85 rows=1 width=100) (actual time=0.009..0.068 rows=277 loops=1)

  • Filter: ((deleted IS NULL) AND (enabled IS TRUE))
  • Rows Removed by Filter: 8
17.          

SubPlan (for Nested Loop Left Join)

18. 1.600 1.600 ↑ 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.004 rows=1 loops=400)

  • Index Cond: (id = b.origin_id)
19. 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)
20. 2.400 2.400 ↓ 0.0 0 400

Index Scan using bdata_flight_id_new on bdata_forks b_2 (cost=0.70..7.43 rows=1 width=0) (actual time=0.006..0.006 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
21. 0.007 0.063 ↑ 1.0 1 7

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

22. 0.035 0.056 ↑ 5.0 1 7

Index Scan using bdata_flight_id_new on bdata_forks b_3 (cost=0.70..21.32 rows=5 width=32) (actual time=0.007..0.008 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
23.          

SubPlan (for Index Scan)

24. 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)
25. 54.201 87.710 ↓ 4.8 388 1

Nested Loop Left Join (cost=2.29..26,322.47 rows=80 width=897) (actual time=0.471..87.710 rows=388 loops=1)

  • Join Filter: ((o_1.flight_id)::text = (b_1.flight_id)::text)
  • Rows Removed by Join Filter: 107,476
26. 0.501 23.739 ↓ 4.8 388 1

Nested Loop Left Join (cost=2.29..24,862.26 rows=80 width=590) (actual time=0.161..23.739 rows=388 loops=1)

27. 0.412 21.686 ↓ 4.8 388 1

Nested Loop Left Join (cost=1.73..24,815.40 rows=80 width=500) (actual time=0.152..21.686 rows=388 loops=1)

28. 0.545 19.722 ↓ 4.8 388 1

Nested Loop Left Join (cost=1.44..24,791.00 rows=80 width=498) (actual time=0.149..19.722 rows=388 loops=1)

29. 0.449 17.625 ↓ 4.8 388 1

Nested Loop (cost=1.01..24,754.99 rows=80 width=495) (actual time=0.142..17.625 rows=388 loops=1)

30. 0.005 0.005 ↑ 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.005 rows=1 loops=1)

  • Index Cond: ((ident)::text = 'N605KA'::text)
31. 17.171 17.171 ↑ 16.0 388 1

Index Scan using bdata_reg_all on bdata_forks b_1 (cost=0.57..24,690.41 rows=6,193 width=492) (actual time=0.136..17.171 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
32. 1.552 1.552 ↑ 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.004..0.004 rows=1 loops=388)

  • Index Cond: (b_1.ident_id = id)
33. 1.552 1.552 ↑ 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.004..0.004 rows=1 loops=388)

  • Index Cond: (b_1.aircraft_id = id)
34. 1.552 1.552 ↑ 1.0 1 388

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

  • Index Cond: (b_1.route_id = id)
35. 4.601 4.656 ↓ 277.0 277 388

Materialize (cost=0.00..8.86 rows=1 width=100) (actual time=0.000..0.012 rows=277 loops=388)

36. 0.055 0.055 ↓ 277.0 277 1

Seq Scan on flight_status_override o_1 (cost=0.00..8.85 rows=1 width=100) (actual time=0.006..0.055 rows=277 loops=1)

  • Filter: ((deleted IS NULL) AND (enabled IS TRUE))
  • Rows Removed by Filter: 8
37.          

SubPlan (for Nested Loop Left Join)

38. 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.004..0.004 rows=1 loops=388)

  • Index Cond: (id = b_1.origin_id)
39. 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.003..0.003 rows=1 loops=388)

  • Index Cond: (id = b_1.destination_id)
40. 2.328 2.328 ↓ 0.0 0 388

Index Scan using bdata_flight_id_new on bdata_forks b_4 (cost=0.70..7.43 rows=1 width=0) (actual time=0.006..0.006 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
41. 0.007 0.070 ↑ 1.0 1 7

Limit (cost=0.70..4.82 rows=1 width=32) (actual time=0.009..0.010 rows=1 loops=7)

42. 0.035 0.063 ↑ 5.0 1 7

Index Scan using bdata_flight_id_new on bdata_forks b_5 (cost=0.70..21.32 rows=5 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
43.          

SubPlan (for Index Scan)

44. 0.028 0.028 ↑ 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.004..0.004 rows=1 loops=7)

  • Index Cond: (id = b_5.destination_id)
Planning time : 5.035 ms
Execution time : 157.815 ms