explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W2ky

Settings
# exclusive inclusive rows x rows loops node
1. 3.038 630.369 ↓ 0.0 0 1

Update on temp_posting tp (cost=337,042.43..349,201.81 rows=936,436 width=69) (actual time=630.368..630.369 rows=0 loops=1)

2. 30.996 627.331 ↑ 109.4 8,557 1

Hash Right Join (cost=337,042.43..349,201.81 rows=936,436 width=69) (actual time=599.343..627.331 rows=8,557 loops=1)

  • Hash Cond: (lr_carrier_point.load_detail_id = ld.id)
3. 2.482 2.482 ↑ 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..2.482 rows=7,072 loops=1)

  • Filter: (type = 8202)
  • Rows Removed by Filter: 7811
4. 6.504 593.853 ↑ 109.4 8,557 1

Hash (cost=314,362.98..314,362.98 rows=936,436 width=70) (actual time=593.853..593.853 rows=8,557 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 536kB
5. 8.941 587.349 ↑ 109.4 8,557 1

Merge Join (cost=54,184.26..314,362.98 rows=936,436 width=70) (actual time=463.642..587.349 rows=8,557 loops=1)

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

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

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

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

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

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

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

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

  • Hash Cond: (p.id = pf.posting_id)
10. 17.998 17.998 ↑ 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..17.998 rows=100,067 loops=1)

11. 19.129 40.191 ↑ 1.0 100,067 1

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

  • Buckets: 131072 Batches: 2 Memory Usage: 3366kB
12. 21.062 21.062 ↑ 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.006..21.062 rows=100,067 loops=1)

13. 9.856 323.865 ↑ 1.0 99,839 1

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

14. 71.718 314.009 ↑ 1.0 99,839 1

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

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

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

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

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

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

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

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

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

  • Merge Cond: (ld.id = lr_nucor.load_detail_id)
19. 24.492 24.492 ↑ 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.010..24.492 rows=101,401 loops=1)

20. 3.866 3.866 ↑ 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.011..3.866 rows=7,072 loops=1)

  • Filter: (type = 8201)
  • Rows Removed by Filter: 7811
21. 38.821 38.821 ↓ 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.008..38.821 rows=101,525 loops=1)

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

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

  • Buckets: 65536 Batches: 2 Memory Usage: 3238kB
23. 24.343 24.343 ↓ 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.019..24.343 rows=99,715 loops=1)

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

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

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

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

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

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

27. 11.714 11.714 ↑ 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.016..11.714 rows=27,120 loops=1)

28. 1.952 2.470 ↓ 1.3 8,775 1

Sort (cost=533.48..550.53 rows=6,820 width=14) (actual time=1.438..2.470 rows=8,775 loops=1)

  • Sort Key: tp.id
  • Sort Method: quicksort Memory: 404kB
29. 0.518 0.518 ↑ 1.5 4,513 1

Seq Scan on temp_posting tp (cost=0.00..99.20 rows=6,820 width=14) (actual time=0.009..0.518 rows=4,513 loops=1)

Planning time : 2.099 ms
Execution time : 632.044 ms