explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9hv9

Settings
# exclusive inclusive rows x rows loops node
1. 4.185 469.978 ↓ 0.0 0 1

Update on temp_posting tp (cost=41,612.43..369,673.77 rows=3,291,655,363 width=57) (actual time=469.978..469.978 rows=0 loops=1)

2. 16.886 465.793 ↑ 412,436.5 7,981 1

Hash Join (cost=41,612.43..369,673.77 rows=3,291,655,363 width=57) (actual time=326.321..465.793 rows=7,981 loops=1)

  • Hash Cond: (p.id = pf.posting_id)
3. 18.134 396.325 ↑ 109.8 123,829 1

Merge Left Join (cost=33,087.42..244,352.68 rows=13,601,881 width=44) (actual time=273.579..396.325 rows=123,829 loops=1)

  • Merge Cond: (p.id = pl.posting_id)
4. 34.314 363.934 ↓ 1.0 101,131 1

Merge Left Join (cost=33,087.14..36,914.32 rows=100,309 width=38) (actual time=273.555..363.934 rows=101,131 loops=1)

  • Merge Cond: (p.id = ld.posting_id)
5. 14.740 99.243 ↓ 1.0 100,070 1

Merge Left Join (cost=11,847.43..13,676.15 rows=100,067 width=20) (actual time=68.758..99.243 rows=100,070 loops=1)

  • Merge Cond: (p.id = aaa.posting_id)
6. 67.565 84.361 ↓ 1.0 100,070 1

Sort (cost=11,831.54..12,081.71 rows=100,067 width=14) (actual time=68.643..84.361 rows=100,070 loops=1)

  • Sort Key: p.id
  • Sort Method: external merge Disk: 2544kB
7. 16.796 16.796 ↓ 1.0 100,070 1

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

8. 0.068 0.142 ↓ 1.0 180 1

Sort (cost=15.90..16.34 rows=177 width=14) (actual time=0.113..0.142 rows=180 loops=1)

  • Sort Key: aaa.posting_id
  • Sort Method: quicksort Memory: 33kB
9. 0.074 0.074 ↓ 1.0 180 1

Seq Scan on auto_accept_algo aaa (cost=0.00..9.29 rows=177 width=14) (actual time=0.014..0.074 rows=180 loops=1)

  • Filter: (status = 9101)
  • Rows Removed by Filter: 6
10. 9.954 230.377 ↓ 1.0 98,490 1

Materialize (cost=21,239.70..21,727.02 rows=97,463 width=26) (actual time=204.794..230.377 rows=98,490 loops=1)

11. 85.079 220.423 ↓ 1.0 98,490 1

Sort (cost=21,239.70..21,483.36 rows=97,463 width=26) (actual time=204.792..220.423 rows=98,490 loops=1)

  • Sort Key: ld.posting_id
  • Sort Method: external merge Disk: 2592kB
12. 24.152 135.344 ↓ 1.0 98,490 1

Hash Right Join (cost=9,632.61..10,829.14 rows=97,463 width=26) (actual time=108.943..135.344 rows=98,490 loops=1)

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

  • Filter: (type = 8202)
  • Rows Removed by Filter: 7811
14. 20.704 108.883 ↓ 1.0 97,568 1

Hash (cost=7,747.33..7,747.33 rows=97,463 width=28) (actual time=108.883..108.883 rows=97,568 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 3221kB
15. 29.815 88.179 ↓ 1.0 97,568 1

Hash Right Join (cost=6,645.80..7,747.33 rows=97,463 width=28) (actual time=55.855..88.179 rows=97,568 loops=1)

  • Hash Cond: (lr_nucor.load_detail_id = ld.id)
16. 2.556 2.556 ↑ 1.0 7,072 1

Seq Scan on load_reference lr_nucor (cost=0.00..365.04 rows=7,072 width=14) (actual time=0.013..2.556 rows=7,072 loops=1)

  • Filter: (type = 8201)
  • Rows Removed by Filter: 7811
17. 20.672 55.808 ↑ 1.0 97,444 1

Hash (cost=4,855.51..4,855.51 rows=97,463 width=22) (actual time=55.808..55.808 rows=97,444 loops=1)

  • Buckets: 65536 Batches: 2 Memory Usage: 3193kB
18. 35.136 35.136 ↑ 1.0 97,444 1

Seq Scan on load_detail ld (cost=0.00..4,855.51 rows=97,463 width=22) (actual time=0.008..35.136 rows=97,444 loops=1)

  • Filter: (status = 6301)
  • Rows Removed by Filter: 3960
19. 3.792 14.257 ↓ 1.0 27,670 1

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

20. 10.465 10.465 ↓ 1.0 27,123 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.018..10.465 rows=27,123 loops=1)

21. 0.793 52.582 ↑ 14.0 3,465 1

Hash (cost=7,920.01..7,920.01 rows=48,400 width=28) (actual time=52.582..52.582 rows=3,465 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 729kB
22. 8.253 51.789 ↑ 14.0 3,465 1

Hash Join (cost=5,587.51..7,920.01 rows=48,400 width=28) (actual time=43.146..51.789 rows=3,465 loops=1)

  • Hash Cond: (tp.id = pf.posting_id)
23. 1.022 1.022 ↑ 14.0 3,465 1

Seq Scan on temp_posting tp (cost=0.00..704.00 rows=48,400 width=14) (actual time=0.151..1.022 rows=3,465 loops=1)

24. 19.603 42.514 ↓ 1.0 100,070 1

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

  • Buckets: 131072 Batches: 2 Memory Usage: 3366kB
25. 22.911 22.911 ↓ 1.0 100,070 1

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