explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lh9c

Settings
# exclusive inclusive rows x rows loops node
1. 3.839 633.267 ↓ 0.0 0 1

Update on temp_posting tp (cost=324,345.81..332,545.74 rows=619,668 width=69) (actual time=633.267..633.267 rows=0 loops=1)

2. 35.652 629.428 ↑ 72.4 8,557 1

Hash Right Join (cost=324,345.81..332,545.74 rows=619,668 width=69) (actual time=598.851..629.428 rows=8,557 loops=1)

  • Hash Cond: (lr_carrier_point.load_detail_id = ld.id)
3. 4.120 4.120 ↑ 1.0 7,072 1

Seq Scan on load_reference lr_carrier_point (cost=0.00..365.04 rows=7,072 width=14) (actual time=0.014..4.120 rows=7,072 loops=1)

  • Filter: (type = 8202)
  • Rows Removed by Filter: 7811
4. 6.279 589.656 ↑ 72.4 8,557 1

Hash (cost=309,337.96..309,337.96 rows=619,668 width=70) (actual time=589.656..589.656 rows=8,557 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 536kB
5. 9.519 583.377 ↑ 72.4 8,557 1

Merge Join (cost=53,651.06..309,337.96 rows=619,668 width=70) (actual time=459.260..583.377 rows=8,557 loops=1)

  • Merge Cond: (pf.posting_id = tp.id)
6. 20.195 572.975 ↑ 111.8 122,878 1

Merge Left Join (cost=53,650.77..265,433.14 rows=13,739,928 width=72) (actual time=459.047..572.975 rows=122,878 loops=1)

  • Merge Cond: (p.id = pl.posting_id)
7. 38.198 536.452 ↑ 1.0 100,191 1

Merge Left Join (cost=53,650.49..55,921.62 rows=101,327 width=66) (actual time=459.024..536.452 rows=100,191 loops=1)

  • Merge Cond: (p.id = pi.posting_id)
8. 63.729 173.672 ↑ 1.0 100,067 1

Sort (cost=22,659.47..22,909.63 rows=100,067 width=28) (actual time=159.052..173.672 rows=100,067 loops=1)

  • Sort Key: p.id
  • Sort Method: external merge Disk: 4104kB
9. 53.028 109.943 ↑ 1.0 100,067 1

Hash Join (cost=5,587.51..11,951.10 rows=100,067 width=28) (actual time=40.228..109.943 rows=100,067 loops=1)

  • Hash Cond: (p.id = pf.posting_id)
10. 16.854 16.854 ↑ 1.0 100,067 1

Seq Scan on posting p (cost=0.00..3,520.67 rows=100,067 width=14) (actual time=0.010..16.854 rows=100,067 loops=1)

11. 19.328 40.061 ↑ 1.0 100,067 1

Hash (cost=3,847.67..3,847.67 rows=100,067 width=14) (actual time=40.061..40.061 rows=100,067 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 3366kB
12. 20.733 20.733 ↑ 1.0 100,067 1

Seq Scan on posting_fact pf (cost=0.00..3,847.67 rows=100,067 width=14) (actual time=0.005..20.733 rows=100,067 loops=1)

13. 9.563 324.582 ↑ 1.0 99,839 1

Materialize (cost=30,991.02..31,492.81 rows=100,358 width=46) (actual time=299.966..324.582 rows=99,839 loops=1)

14. 71.846 315.019 ↑ 1.0 99,839 1

Sort (cost=30,991.02..31,241.91 rows=100,358 width=46) (actual time=299.962..315.019 rows=99,839 loops=1)

  • Sort Key: pi.posting_id
  • Sort Method: external merge Disk: 4920kB
15. 15.051 243.173 ↑ 1.0 99,839 1

Hash Left Join (cost=4,485.98..19,563.38 rows=100,358 width=46) (actual time=47.321..243.173 rows=99,839 loops=1)

  • Hash Cond: (pi.id = aaa.parent_id)
16. 68.053 228.040 ↑ 1.0 99,839 1

Hash Right Join (cost=4,478.02..19,177.31 rows=100,358 width=48) (actual time=47.228..228.040 rows=99,839 loops=1)

  • Hash Cond: (pil.posting_item_id = pi.id)
17. 34.087 112.836 ↓ 1.0 101,525 1

Merge Right Join (cost=0.87..11,147.32 rows=101,401 width=34) (actual time=0.041..112.836 rows=101,525 loops=1)

  • Merge Cond: (ld.id = pil.link_id)
18. 14.177 42.997 ↓ 1.0 101,525 1

Merge Left Join (cost=0.58..5,089.79 rows=101,401 width=20) (actual time=0.027..42.997 rows=101,525 loops=1)

  • Merge Cond: (ld.id = lr_nucor.load_detail_id)
19. 24.936 24.936 ↑ 1.0 101,401 1

Index Scan using idx_posting_item_load_detail_id on load_detail ld (cost=0.29..4,133.14 rows=101,401 width=14) (actual time=0.013..24.936 rows=101,401 loops=1)

20. 3.884 3.884 ↑ 1.0 7,072 1

Index Scan using idx_posting_item_load_reference_posting_item_load_detail_id on load_reference lr_nucor (cost=0.29..614.76 rows=7,072 width=14) (actual time=0.013..3.884 rows=7,072 loops=1)

  • Filter: (type = 8201)
  • Rows Removed by Filter: 7811
21. 35.752 35.752 ↓ 1.0 101,525 1

Index Scan using idx_posting_item_link_test1 on posting_item_link pil (cost=0.29..4,536.56 rows=101,401 width=22) (actual time=0.011..35.752 rows=101,525 loops=1)

  • Filter: ((status = 7201) AND (link_type = 1713))
22. 21.921 47.151 ↓ 1.0 99,715 1

Hash (cost=2,650.68..2,650.68 rows=99,478 width=22) (actual time=47.151..47.151 rows=99,715 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 3238kB
23. 25.230 25.230 ↓ 1.0 99,715 1

Seq Scan on posting_item pi (cost=0.00..2,650.68 rows=99,478 width=22) (actual time=0.018..25.230 rows=99,715 loops=1)

  • Filter: ((type = 6401) AND (status = 6301))
  • Rows Removed by Filter: 797
24. 0.034 0.082 ↑ 1.0 177 1

Hash (cost=5.75..5.75 rows=177 width=14) (actual time=0.082..0.082 rows=177 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
25. 0.048 0.048 ↑ 1.0 177 1

Seq Scan on auto_accept_algo aaa (cost=0.00..5.75 rows=177 width=14) (actual time=0.012..0.048 rows=177 loops=1)

  • Filter: ((parent_type = 1714) AND (status = 9101))
  • Rows Removed by Filter: 6
26. 4.348 16.328 ↓ 1.0 27,175 1

Materialize (cost=0.29..3,226.89 rows=27,120 width=14) (actual time=0.021..16.328 rows=27,175 loops=1)

27. 11.980 11.980 ↑ 1.0 27,120 1

Index Scan using idx_posting_link_posting_id on posting_link pl (cost=0.29..3,159.09 rows=27,120 width=14) (actual time=0.017..11.980 rows=27,120 loops=1)

28. 0.535 0.883 ↓ 1.9 8,775 1

Materialize (cost=0.28..271.26 rows=4,513 width=14) (actual time=0.043..0.883 rows=8,775 loops=1)

29. 0.348 0.348 ↑ 8.9 507 1

Index Scan using idx_temp_posting_test_1 on temp_posting tp (cost=0.28..259.98 rows=4,513 width=14) (actual time=0.042..0.348 rows=507 loops=1)

Planning time : 2.998 ms
Execution time : 634.781 ms