explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SQth

Settings
# exclusive inclusive rows x rows loops node
1. 6.910 897.130 ↓ 0.0 0 1

Update on temp_posting tp (cost=40,403.57..644,260.08 rows=3,398,634,163 width=57) (actual time=897.130..897.130 rows=0 loops=1)

2. 21.683 890.220 ↑ 426,000.8 7,978 1

Hash Join (cost=40,403.57..644,260.08 rows=3,398,634,163 width=57) (actual time=620.392..890.220 rows=7,978 loops=1)

  • Hash Cond: (p.id = pf.posting_id)
3. 25.952 693.910 ↑ 226.8 123,826 1

Merge Left Join (cost=33,101.81..463,701.58 rows=28,087,886 width=44) (actual time=445.430..693.910 rows=123,826 loops=1)

  • Merge Cond: (p.id = pl.posting_id)
4. 22.210 573.865 ↑ 2.0 101,128 1

Merge Left Join (cost=33,101.52..38,707.99 rows=207,137 width=38) (actual time=444.187..573.865 rows=101,128 loops=1)

  • Merge Cond: (p.id = aaa.posting_id)
5. 51.825 551.435 ↓ 1.0 101,128 1

Merge Left Join (cost=33,071.24..35,319.87 rows=100,309 width=32) (actual time=444.003..551.435 rows=101,128 loops=1)

  • Merge Cond: (p.id = ld.posting_id)
6. 100.699 174.219 ↑ 1.0 100,067 1

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

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

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

8. 14.123 325.391 ↓ 1.0 98,487 1

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

9. 129.334 311.268 ↓ 1.0 98,487 1

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

  • Sort Key: ld.posting_id
  • Sort Method: external merge Disk: 2592kB
10. 32.960 181.934 ↓ 1.0 98,487 1

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

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

  • Filter: (type = 8202)
  • Rows Removed by Filter: 7811
12. 28.727 145.462 ↓ 1.0 97,565 1

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

  • Buckets: 65536 Batches: 2 Memory Usage: 3221kB
13. 39.400 116.735 ↓ 1.0 97,565 1

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

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

  • Filter: (type = 8201)
  • Rows Removed by Filter: 7811
15. 29.073 72.863 ↑ 1.0 97,441 1

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

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

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

  • Filter: (status = 6301)
  • Rows Removed by Filter: 3960
17. 0.106 0.220 ↑ 2.3 177 1

Sort (cost=30.28..31.31 rows=413 width=14) (actual time=0.179..0.220 rows=177 loops=1)

  • Sort Key: aaa.posting_id
  • Sort Method: quicksort Memory: 33kB
18. 0.114 0.114 ↑ 2.3 177 1

Seq Scan on auto_accept_algo aaa (cost=0.00..12.34 rows=413 width=14) (actual time=0.041..0.114 rows=177 loops=1)

  • Filter: (status = 9101)
  • Rows Removed by Filter: 6
19. 5.412 94.093 ↓ 1.0 27,667 1

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

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

21. 0.970 174.627 ↑ 7.0 3,462 1

Hash (cost=6,999.26..6,999.26 rows=24,200 width=28) (actual time=174.627..174.627 rows=3,462 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 473kB
22. 12.082 173.657 ↑ 7.0 3,462 1

Hash Join (cost=5,587.51..6,999.26 rows=24,200 width=28) (actual time=160.963..173.657 rows=3,462 loops=1)

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

Seq Scan on temp_posting tp (cost=0.00..352.00 rows=24,200 width=14) (actual time=0.129..0.868 rows=3,462 loops=1)

24. 28.727 160.707 ↑ 1.0 100,067 1

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

  • Buckets: 131072 Batches: 2 Memory Usage: 3366kB
25. 131.980 131.980 ↑ 1.0 100,067 1

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