explain.depesz.com

PostgreSQL's explain analyze made readable

Result: xtkn

Settings
# exclusive inclusive rows x rows loops node
1. 6.380 596.094 ↓ 0.0 0 1

Update on temp_posting tp (cost=39,760.99..293,629.05 rows=286,795,717 width=57) (actual time=596.094..596.094 rows=0 loops=1)

2. 12.573 589.714 ↑ 35,948.3 7,978 1

Merge Join (cost=39,760.99..293,629.05 rows=286,795,717 width=57) (actual time=416.866..589.714 rows=7,978 loops=1)

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

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

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

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

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

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

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

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

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

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

8. 0.091 0.194 ↑ 1.0 177 1

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

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

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

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

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

11. 101.770 271.306 ↓ 1.0 98,487 1

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

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

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

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

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

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

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

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

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

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

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

  • Buckets: 65536 Batches: 2 Memory Usage: 3193kB
18. 46.180 46.180 ↑ 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.071..46.180 rows=97,441 loops=1)

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

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

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

21. 2.469 65.107 ↓ 1.9 7,978 1

Sort (cost=6,673.57..6,684.11 rows=4,217 width=28) (actual time=63.897..65.107 rows=7,978 loops=1)

  • Sort Key: tp.id
  • Sort Method: quicksort Memory: 367kB
22. 12.546 62.638 ↑ 1.2 3,462 1

Hash Join (cost=5,587.51..6,419.66 rows=4,217 width=28) (actual time=49.163..62.638 rows=3,462 loops=1)

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

Seq Scan on temp_posting tp (cost=0.00..243.17 rows=4,217 width=14) (actual time=0.042..1.195 rows=3,462 loops=1)

24. 26.002 48.897 ↑ 1.0 100,067 1

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

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