explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CmGU

Settings
# exclusive inclusive rows x rows loops node
1. 11.256 646.959 ↓ 0.0 0 1

Update on temp_posting tp (cost=39,701.84..292,758.42 rows=259,727,880 width=57) (actual time=646.959..646.959 rows=0 loops=1)

2. 12.573 635.703 ↑ 32,555.5 7,978 1

Merge Join (cost=39,701.84..292,758.42 rows=259,727,880 width=57) (actual time=461.587..635.703 rows=7,978 loops=1)

  • Merge Cond: (p.id = pf.posting_id)
3. 25.730 538.713 ↑ 109.8 123,826 1

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

  • Merge Cond: (p.id = pl.posting_id)
4. 46.459 493.711 ↓ 1.0 101,128 1

Merge Left Join (cost=33,087.14..36,914.32 rows=100,309 width=38) (actual time=378.204..493.711 rows=101,128 loops=1)

  • Merge Cond: (p.id = ld.posting_id)
5. 19.742 145.065 ↑ 1.0 100,067 1

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

  • Merge Cond: (p.id = aaa.posting_id)
6. 95.527 124.948 ↑ 1.0 100,067 1

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

  • Sort Key: p.id
  • Sort Method: external merge Disk: 2544kB
7. 29.421 29.421 ↑ 1.0 100,067 1

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

8. 0.125 0.375 ↑ 1.0 177 1

Sort (cost=15.90..16.34 rows=177 width=14) (actual time=0.357..0.375 rows=177 loops=1)

  • Sort Key: aaa.posting_id
  • Sort Method: quicksort Memory: 33kB
9. 0.250 0.250 ↑ 1.0 177 1

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

  • Filter: (status = 9101)
  • Rows Removed by Filter: 6
10. 12.757 302.187 ↓ 1.0 98,487 1

Materialize (cost=21,239.70..21,727.02 rows=97,463 width=26) (actual time=270.877..302.187 rows=98,487 loops=1)

11. 103.912 289.430 ↓ 1.0 98,487 1

Sort (cost=21,239.70..21,483.36 rows=97,463 width=26) (actual time=270.873..289.430 rows=98,487 loops=1)

  • Sort Key: ld.posting_id
  • Sort Method: external merge Disk: 2592kB
12. 30.744 185.518 ↓ 1.0 98,487 1

Hash Right Join (cost=9,632.61..10,829.14 rows=97,463 width=26) (actual time=151.428..185.518 rows=98,487 loops=1)

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

  • Filter: (type = 8202)
  • Rows Removed by Filter: 7811
14. 27.276 151.311 ↓ 1.0 97,565 1

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

  • Buckets: 65536 Batches: 2 Memory Usage: 3221kB
15. 39.088 124.035 ↓ 1.0 97,565 1

Hash Right Join (cost=6,645.80..7,747.33 rows=97,463 width=28) (actual time=81.573..124.035 rows=97,565 loops=1)

  • Hash Cond: (lr_nucor.load_detail_id = ld.id)
16. 3.488 3.488 ↑ 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.017..3.488 rows=7,072 loops=1)

  • Filter: (type = 8201)
  • Rows Removed by Filter: 7811
17. 29.711 81.459 ↑ 1.0 97,441 1

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

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

Seq Scan on load_detail ld (cost=0.00..4,855.51 rows=97,463 width=22) (actual time=1.988..51.748 rows=97,441 loops=1)

  • Filter: (status = 6301)
  • Rows Removed by Filter: 3960
19. 5.082 19.272 ↓ 1.0 27,667 1

Materialize (cost=0.29..3,226.89 rows=27,120 width=14) (actual time=0.018..19.272 rows=27,667 loops=1)

20. 14.190 14.190 ↑ 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.013..14.190 rows=27,120 loops=1)

21. 2.511 84.417 ↓ 2.1 7,978 1

Sort (cost=6,614.42..6,623.97 rows=3,819 width=28) (actual time=83.345..84.417 rows=7,978 loops=1)

  • Sort Key: tp.id
  • Sort Method: quicksort Memory: 367kB
22. 17.879 81.906 ↑ 1.1 3,462 1

Hash Join (cost=5,587.51..6,387.21 rows=3,819 width=28) (actual time=62.341..81.906 rows=3,462 loops=1)

  • Hash Cond: (tp.id = pf.posting_id)
23. 2.044 2.044 ↑ 1.1 3,462 1

Seq Scan on temp_posting tp (cost=0.00..220.19 rows=3,819 width=14) (actual time=0.035..2.044 rows=3,462 loops=1)

24. 29.334 61.983 ↑ 1.0 100,067 1

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

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