explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2dZW

Settings
# exclusive inclusive rows x rows loops node
1. 0.926 25,514.650 ↑ 1.0 20 1

Limit (cost=121,453.17..121,453.22 rows=20 width=2,562) (actual time=25,513.553..25,514.650 rows=20 loops=1)

2. 481.986 25,513.724 ↑ 10,330.4 20 1

Sort (cost=121,453.17..121,969.69 rows=206,608 width=2,562) (actual time=25,513.536..25,513.724 rows=20 loops=1)

  • Sort Key: (COALESCE(flightlog_actualdatetimecommit.committed_on, flight_diversiontypecommit.committed_on, flightlog_actualairportcommit.committed_on)) DESC
  • Sort Method: top-N heapsort Memory: 45kB
3. 785.307 25,031.738 ↑ 4.1 50,527 1

Hash Left Join (cost=15,259.06..115,955.40 rows=206,608 width=2,562) (actual time=3,047.312..25,031.738 rows=50,527 loops=1)

  • Hash Cond: (flightlog_actualairportcommit.airport_value_id = t13.id)
4. 793.846 24,235.505 ↑ 4.1 50,527 1

Hash Left Join (cost=15,229.35..113,084.84 rows=206,608 width=2,287) (actual time=3,036.370..24,235.505 rows=50,527 loops=1)

  • Hash Cond: (flight.operating_under_id = operating_under.id)
5. 752.863 23,441.590 ↑ 4.1 50,527 1

Hash Left Join (cost=15,228.31..110,880.94 rows=206,608 width=2,215) (actual time=3,036.287..23,441.590 rows=50,527 loops=1)

  • Hash Cond: (flight.type_of_operations_id = operation.id)
6. 805.591 22,688.619 ↑ 4.1 50,527 1

Hash Left Join (cost=15,227.19..108,676.42 rows=206,608 width=1,277) (actual time=3,036.166..22,688.619 rows=50,527 loops=1)

  • Hash Cond: (t8.location_id = t9.id)
7. 930.115 21,872.151 ↑ 4.1 50,527 1

Hash Left Join (cost=15,203.71..105,854.08 rows=206,608 width=1,253) (actual time=3,025.269..21,872.151 rows=50,527 loops=1)

  • Hash Cond: (flight.id = flight_log.flight_id)
8. 781.178 19,989.887 ↑ 4.1 50,527 1

Hash Left Join (cost=11,132.21..41,362.81 rows=206,608 width=1,107) (actual time=2,073.004..19,989.887 rows=50,527 loops=1)

  • Hash Cond: (flight.destination_id = t8.id)
9. 1,976.533 19,196.076 ↑ 4.1 50,527 1

Hash Left Join (cost=11,102.50..39,129.56 rows=206,608 width=840) (actual time=2,060.348..19,196.076 rows=50,527 loops=1)

  • Hash Cond: (flight_flightstatuslog.id = flightlog_actualairportcommit.log_id)
  • Filter: ((flightlog_actualdatetimecommit.id IS NOT NULL) OR (flight_diversiontypecommit.id IS NOT NULL) OR (flightlog_actualairportcommit.id IS NOT NULL))
  • Rows Removed by Filter: 156082
10. 3,141.939 17,218.579 ↓ 1.0 206,609 1

Hash Left Join (cost=11,100.15..38,351.83 rows=206,608 width=805) (actual time=2,059.372..17,218.579 rows=206,609 loops=1)

  • Hash Cond: (flight_flightstatuslog.id = flight_diversiontypecommit.log_id)
11. 3,188.189 14,071.389 ↓ 1.0 206,609 1

Hash Left Join (cost=11,088.14..37,561.48 rows=206,608 width=774) (actual time=2,054.100..14,071.389 rows=206,609 loops=1)

  • Hash Cond: (flight.origin_id = airport.id)
12. 3,347.253 10,847.194 ↓ 1.0 206,609 1

Hash Join (cost=11,024.29..35,294.02 rows=206,608 width=483) (actual time=2,018.071..10,847.194 rows=206,609 loops=1)

  • Hash Cond: (flight_flightstatuslog.flight_id = flight.id)
13. 3,484.865 5,483.150 ↓ 1.0 206,609 1

Merge Left Join (cost=0.79..10,076.66 rows=206,608 width=76) (actual time=0.035..5,483.150 rows=206,609 loops=1)

  • Merge Cond: (flight_flightstatuslog.id = flightlog_actualdatetimecommit.log_id)
14. 1,609.488 1,609.488 ↓ 1.0 206,609 1

Index Scan using flight_flightstatuslog_pkey on flight_flightstatuslog (cost=0.42..7,103.54 rows=206,608 width=37) (actual time=0.008..1,609.488 rows=206,609 loops=1)

15. 388.797 388.797 ↑ 1.0 50,111 1

Index Scan using flightlog_actualdatetimecommit_log_id_key on flightlog_actualdatetimecommit (cost=0.29..1,830.21 rows=50,111 width=39) (actual time=0.007..388.797 rows=50,111 loops=1)

16. 1,030.276 2,016.791 ↑ 1.0 115,800 1

Hash (cost=3,469.00..3,469.00 rows=115,800 width=407) (actual time=2,016.776..2,016.791 rows=115,800 loops=1)

  • Buckets: 16384 Batches: 16 Memory Usage: 1336kB
17. 986.515 986.515 ↑ 1.0 115,800 1

Seq Scan on flight (cost=0.00..3,469.00 rows=115,800 width=407) (actual time=0.022..986.515 rows=115,800 loops=1)

18. 6.023 36.006 ↑ 1.0 787 1

Hash (cost=54.01..54.01 rows=787 width=291) (actual time=35.992..36.006 rows=787 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 123kB
19. 12.024 29.983 ↑ 1.0 787 1

Hash Left Join (cost=23.48..54.01 rows=787 width=291) (actual time=12.125..29.983 rows=787 loops=1)

  • Hash Cond: (airport.location_id = location.id)
20. 5.876 5.876 ↑ 1.0 787 1

Seq Scan on airport (cost=0.00..19.87 rows=787 width=267) (actual time=0.015..5.876 rows=787 loops=1)

21. 6.071 12.083 ↑ 1.0 777 1

Hash (cost=13.77..13.77 rows=777 width=24) (actual time=12.069..12.083 rows=777 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
22. 6.012 6.012 ↑ 1.0 777 1

Seq Scan on location (cost=0.00..13.77 rows=777 width=24) (actual time=0.016..6.012 rows=777 loops=1)

23. 2.686 5.251 ↑ 1.0 356 1

Hash (cost=7.56..7.56 rows=356 width=31) (actual time=5.237..5.251 rows=356 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
24. 2.565 2.565 ↑ 1.0 356 1

Seq Scan on flight_diversiontypecommit (cost=0.00..7.56 rows=356 width=31) (actual time=0.030..2.565 rows=356 loops=1)

25. 0.453 0.964 ↑ 1.0 60 1

Hash (cost=1.60..1.60 rows=60 width=35) (actual time=0.948..0.964 rows=60 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
26. 0.511 0.511 ↑ 1.0 60 1

Seq Scan on flightlog_actualairportcommit (cost=0.00..1.60 rows=60 width=35) (actual time=0.013..0.511 rows=60 loops=1)

27. 6.232 12.633 ↑ 1.0 787 1

Hash (cost=19.87..19.87 rows=787 width=267) (actual time=12.618..12.633 rows=787 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 102kB
28. 6.401 6.401 ↑ 1.0 787 1

Seq Scan on airport t8 (cost=0.00..19.87 rows=787 width=267) (actual time=0.014..6.401 rows=787 loops=1)

29. 484.038 952.149 ↓ 1.0 59,579 1

Hash (cost=2,046.78..2,046.78 rows=59,578 width=146) (actual time=952.135..952.149 rows=59,579 loops=1)

  • Buckets: 32768 Batches: 4 Memory Usage: 3110kB
30. 468.111 468.111 ↓ 1.0 59,579 1

Seq Scan on flight_log (cost=0.00..2,046.78 rows=59,578 width=146) (actual time=0.018..468.111 rows=59,579 loops=1)

31. 5.469 10.877 ↑ 1.0 777 1

Hash (cost=13.77..13.77 rows=777 width=24) (actual time=10.863..10.877 rows=777 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
32. 5.408 5.408 ↑ 1.0 777 1

Seq Scan on location t9 (cost=0.00..13.77 rows=777 width=24) (actual time=0.013..5.408 rows=777 loops=1)

33. 0.048 0.108 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=938) (actual time=0.094..0.108 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 0.060 0.060 ↑ 1.0 5 1

Seq Scan on operation (cost=0.00..1.05 rows=5 width=938) (actual time=0.014..0.060 rows=5 loops=1)

35. 0.027 0.069 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=72) (actual time=0.055..0.069 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
36. 0.042 0.042 ↑ 1.0 2 1

Seq Scan on operating_under (cost=0.00..1.02 rows=2 width=72) (actual time=0.014..0.042 rows=2 loops=1)

37. 5.539 10.926 ↑ 1.0 787 1

Hash (cost=19.87..19.87 rows=787 width=267) (actual time=10.913..10.926 rows=787 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 102kB
38. 5.387 5.387 ↑ 1.0 787 1

Seq Scan on airport t13 (cost=0.00..19.87 rows=787 width=267) (actual time=0.014..5.387 rows=787 loops=1)