explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KfoE

Settings
# exclusive inclusive rows x rows loops node
1. 11,623.148 113,955.519 ↓ 1.0 7,455,524 1

WindowAgg (cost=9,554,745.07..9,795,328.69 rows=7,402,573 width=572) (actual time=99,006.580..113,955.519 rows=7,455,524 loops=1)

2. 45,543.047 102,332.371 ↓ 1.0 7,455,524 1

Sort (cost=9,554,745.07..9,573,251.50 rows=7,402,573 width=463) (actual time=99,006.562..102,332.371 rows=7,455,524 loops=1)

  • Sort Key: (COALESCE(air.usn, bs.usn, vs.lh_spot_id)), (CASE WHEN (air.usn IS NOT NULL) THEN 1 ELSE 0 END) DESC
  • Sort Method: external merge Disk: 2,853,896kB
3. 3,224.125 56,789.324 ↓ 1.0 7,455,524 1

Hash Left Join (cost=761,297.61..4,079,899.47 rows=7,402,573 width=463) (actual time=6,644.911..56,789.324 rows=7,455,524 loops=1)

  • Hash Cond: (vs.lh_spot_id = ac.spot_id)
4. 3,066.460 53,564.360 ↓ 1.0 7,455,524 1

Hash Left Join (cost=761,169.18..4,051,989.69 rows=7,402,573 width=447) (actual time=6,644.045..53,564.360 rows=7,455,524 loops=1)

  • Hash Cond: (vs.lh_spot_id = la.spot_id)
5. 4,516.087 50,497.006 ↓ 1.0 7,455,524 1

Hash Left Join (cost=761,059.80..4,024,096.78 rows=7,402,573 width=431) (actual time=6,643.117..50,497.006 rows=7,455,524 loops=1)

  • Hash Cond: ((vs.deal_cd)::text = (d.deal_cd)::text)
  • Filter: (((vs.deal_cd IS NOT NULL) AND (d.deal_cd IS NOT NULL)) OR (vs.deal_cd IS NULL))
  • Rows Removed by Filter: 42,203
6. 14,340.935 45,947.123 ↓ 1.0 7,497,727 1

Hash Left Join (cost=757,335.69..3,918,247.75 rows=7,496,324 width=420) (actual time=6,609.174..45,947.123 rows=7,497,727 loops=1)

  • Hash Cond: (vs.aired_spot_id = air.aired_spot_id)
7. 6,539.938 28,547.459 ↓ 1.0 7,497,727 1

Hash Left Join (cost=411,130.08..2,617,301.66 rows=7,496,324 width=389) (actual time=3,550.172..28,547.459 rows=7,497,727 loops=1)

  • Hash Cond: (vs.booked_spot_id = bs.booked_spot_id)
8. 13,304.526 18,641.839 ↓ 1.0 7,497,727 1

Hash Left Join (cost=17,854.92..1,281,430.61 rows=7,496,324 width=381) (actual time=180.660..18,641.839 rows=7,497,727 loops=1)

  • Hash Cond: (vs.order_id = cl.bias_number)
9. 5,164.234 5,164.234 ↓ 1.0 7,497,727 1

Seq Scan on stage_spot vs (cost=0.00..412,034.24 rows=7,496,324 width=377) (actual time=0.122..5,164.234 rows=7,497,727 loops=1)

10. 85.046 173.079 ↑ 1.0 450,574 1

Hash (cost=10,461.74..10,461.74 rows=450,574 width=8) (actual time=173.079..173.079 rows=450,574 loops=1)

  • Buckets: 131,072 Batches: 8 Memory Usage: 3,231kB
11. 88.033 88.033 ↑ 1.0 450,574 1

Seq Scan on change_log_entries cl (cost=0.00..10,461.74 rows=450,574 width=8) (actual time=0.121..88.033 rows=450,574 loops=1)

12. 1,365.277 3,365.682 ↓ 1.0 7,495,283 1

Hash (cost=263,004.85..263,004.85 rows=7,494,185 width=12) (actual time=3,365.682..3,365.682 rows=7,495,283 loops=1)

  • Buckets: 131,072 Batches: 128 Memory Usage: 3,390kB
13. 2,000.405 2,000.405 ↓ 1.0 7,495,283 1

Seq Scan on booked_spot bs (cost=0.00..263,004.85 rows=7,494,185 width=12) (actual time=0.078..2,000.405 rows=7,495,283 loops=1)

14. 1,275.565 3,058.729 ↑ 1.0 5,562,900 1

Hash (cost=233,190.05..233,190.05 rows=5,563,805 width=35) (actual time=3,058.728..3,058.729 rows=5,562,900 loops=1)

  • Buckets: 65,536 Batches: 128 Memory Usage: 3,032kB
15. 1,783.164 1,783.164 ↑ 1.0 5,562,900 1

Seq Scan on aired_spot air (cost=0.00..233,190.05 rows=5,563,805 width=35) (actual time=0.081..1,783.164 rows=5,562,900 loops=1)

16. 4.192 33.796 ↑ 1.0 20,009 1

Hash (cost=3,473.97..3,473.97 rows=20,011 width=23) (actual time=33.796..33.796 rows=20,009 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,351kB
17. 4.882 29.604 ↑ 1.0 20,009 1

Hash Join (cost=2,366.17..3,473.97 rows=20,011 width=23) (actual time=13.934..29.604 rows=20,009 loops=1)

  • Hash Cond: ((d.deal_cd)::text = (dd.source_deal_id)::text)
18. 9.570 18.006 ↑ 1.0 20,011 1

Hash Join (cost=819.25..1,874.51 rows=20,011 width=35) (actual time=7.083..18.006 rows=20,011 loops=1)

  • Hash Cond: ((d.deal_cd)::text = (ldp.deal_cd)::text)
19. 1.565 1.565 ↑ 1.0 20,011 1

Seq Scan on deal d (cost=0.00..780.11 rows=20,011 width=21) (actual time=0.077..1.565 rows=20,011 loops=1)

20. 3.514 6.871 ↑ 1.0 20,011 1

Hash (cost=569.11..569.11 rows=20,011 width=14) (actual time=6.871..6.871 rows=20,011 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,156kB
21. 3.357 3.357 ↑ 1.0 20,011 1

Seq Scan on deal_properties ldp (cost=0.00..569.11 rows=20,011 width=14) (actual time=0.079..3.357 rows=20,011 loops=1)

22. 3.316 6.716 ↑ 1.0 20,041 1

Hash (cost=1,296.41..1,296.41 rows=20,041 width=11) (actual time=6.716..6.716 rows=20,041 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,118kB
23. 3.400 3.400 ↑ 1.0 20,041 1

Seq Scan on dim_deal dd (cost=0.00..1,296.41 rows=20,041 width=11) (actual time=0.079..3.400 rows=20,041 loops=1)

24. 0.378 0.894 ↑ 1.0 2,417 1

Hash (cost=79.17..79.17 rows=2,417 width=16) (actual time=0.894..0.894 rows=2,417 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 146kB
25. 0.516 0.516 ↑ 1.0 2,417 1

Seq Scan on lighthouse_adjustment la (cost=0.00..79.17 rows=2,417 width=16) (actual time=0.005..0.516 rows=2,417 loops=1)

26. 0.324 0.839 ↑ 1.0 2,197 1

Hash (cost=100.97..100.97 rows=2,197 width=16) (actual time=0.839..0.839 rows=2,197 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 135kB
27. 0.515 0.515 ↑ 1.0 2,197 1

Seq Scan on adjustment_comment ac (cost=0.00..100.97 rows=2,197 width=16) (actual time=0.003..0.515 rows=2,197 loops=1)

Planning time : 2.532 ms
Execution time : 148,752.705 ms