explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r9jW

Settings
# exclusive inclusive rows x rows loops node
1. 3.278 878.983 ↓ 0.0 0 1

Update on temp_posting tp (cost=331,680.64..341,571.93 rows=755,190 width=69) (actual time=878.983..878.983 rows=0 loops=1)

2. 33.280 875.705 ↑ 88.3 8,557 1

Hash Right Join (cost=331,680.64..341,571.93 rows=755,190 width=69) (actual time=852.425..875.705 rows=8,557 loops=1)

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

  • Filter: (type = 8202)
  • Rows Removed by Filter: 7811
4. 8.193 839.835 ↑ 88.3 8,557 1

Hash (cost=313,390.77..313,390.77 rows=755,190 width=70) (actual time=839.835..839.835 rows=8,557 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 536kB
5. 8.687 831.642 ↑ 88.3 8,557 1

Merge Join (cost=55,930.73..313,390.77 rows=755,190 width=70) (actual time=708.531..831.642 rows=8,557 loops=1)

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

Merge Left Join (cost=55,509.04..267,291.40 rows=13,739,928 width=72) (actual time=705.226..819.399 rows=122,878 loops=1)

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

Merge Left Join (cost=55,508.75..57,779.88 rows=101,327 width=66) (actual time=705.187..782.020 rows=100,191 loops=1)

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

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

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

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

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

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

11. 26.083 60.822 ↑ 1.0 100,067 1

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

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

13. 9.754 512.356 ↑ 1.0 99,839 1

Materialize (cost=32,849.28..33,351.07 rows=100,358 width=46) (actual time=487.361..512.356 rows=99,839 loops=1)

14. 86.690 502.602 ↑ 1.0 99,839 1

Sort (cost=32,849.28..33,100.18 rows=100,358 width=46) (actual time=487.349..502.602 rows=99,839 loops=1)

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

Hash Left Join (cost=9,222.22..21,421.64 rows=100,358 width=46) (actual time=113.639..415.912 rows=99,839 loops=1)

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

Hash Right Join (cost=9,214.26..21,035.57 rows=100,358 width=48) (actual time=113.408..400.118 rows=99,839 loops=1)

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

Hash Right Join (cost=4,737.10..13,005.59 rows=101,401 width=34) (actual time=57.274..175.985 rows=101,525 loops=1)

  • Hash Cond: (ld.id = pil.link_id)
18. 15.987 55.292 ↓ 1.0 101,525 1

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

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

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

  • Filter: (type = 8201)
  • Rows Removed by Filter: 7811
21. 25.852 56.730 ↑ 1.0 101,401 1

Hash (cost=2,874.01..2,874.01 rows=101,401 width=22) (actual time=56.730..56.730 rows=101,401 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 3295kB
22. 30.878 30.878 ↑ 1.0 101,401 1

Seq Scan on posting_item_link pil (cost=0.00..2,874.01 rows=101,401 width=22) (actual time=0.016..30.878 rows=101,401 loops=1)

  • Filter: ((status = 7201) AND (link_type = 1713))
23. 26.756 55.671 ↓ 1.0 99,715 1

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

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

  • Filter: ((type = 6401) AND (status = 6301))
  • Rows Removed by Filter: 797
25. 0.071 0.209 ↑ 1.0 177 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
26. 0.138 0.138 ↑ 1.0 177 1

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

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

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

28. 13.992 13.992 ↑ 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.032..13.992 rows=27,120 loops=1)

29. 2.222 3.556 ↓ 1.6 8,775 1

Sort (cost=421.69..435.44 rows=5,500 width=14) (actual time=2.725..3.556 rows=8,775 loops=1)

  • Sort Key: tp.id
  • Sort Method: quicksort Memory: 404kB
30. 1.334 1.334 ↑ 1.2 4,513 1

Seq Scan on temp_posting tp (cost=0.00..80.00 rows=5,500 width=14) (actual time=0.114..1.334 rows=4,513 loops=1)