explain.depesz.com

PostgreSQL's explain analyze made readable

Result: W6PU

Settings
# exclusive inclusive rows x rows loops node
1. 5.212 464.034 ↓ 0.0 0 1

Update on temp_posting tp (cost=31,675.42..288,337.82 rows=388,129,714 width=45) (actual time=464.034..464.034 rows=0 loops=1)

2. 10.185 458.822 ↑ 48,650.0 7,978 1

Merge Join (cost=31,675.42..288,337.82 rows=388,129,714 width=45) (actual time=334.843..458.822 rows=7,978 loops=1)

  • Merge Cond: (p.id = pf.posting_id)
3. 19.309 389.327 ↑ 110.8 122,777 1

Merge Left Join (cost=24,779.29..235,800.89 rows=13,601,881 width=32) (actual time=276.410..389.327 rows=122,777 loops=1)

  • Merge Cond: (p.id = pl.posting_id)
4. 31.519 353.619 ↑ 1.0 100,082 1

Merge Left Join (cost=24,779.01..28,362.53 rows=100,309 width=26) (actual time=276.354..353.619 rows=100,082 loops=1)

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

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

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

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

  • Sort Key: p.id
  • Sort Method: external merge Disk: 2544kB
7. 23.597 23.597 ↑ 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..23.597 rows=100,067 loops=1)

8. 0.094 0.205 ↑ 1.0 177 1

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

  • Sort Key: aaa.posting_id
  • Sort Method: quicksort Memory: 33kB
9. 0.111 0.111 ↑ 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.111 rows=177 loops=1)

  • Filter: (status = 9101)
  • Rows Removed by Filter: 6
10. 103.797 148.685 ↑ 1.0 97,441 1

Sort (cost=12,931.57..13,175.23 rows=97,463 width=14) (actual time=132.528..148.685 rows=97,441 loops=1)

  • Sort Key: ld.posting_id
  • Sort Method: external sort Disk: 2856kB
11. 44.888 44.888 ↑ 1.0 97,441 1

Seq Scan on load_detail ld (cost=0.00..4,855.51 rows=97,463 width=14) (actual time=1.714..44.888 rows=97,441 loops=1)

  • Filter: (status = 6301)
  • Rows Removed by Filter: 3960
12. 4.075 16.399 ↓ 1.0 27,142 1

Materialize (cost=0.29..3,226.89 rows=27,120 width=14) (actual time=0.045..16.399 rows=27,142 loops=1)

13. 12.324 12.324 ↑ 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.037..12.324 rows=27,120 loops=1)

14. 1.714 59.310 ↓ 1.4 7,978 1

Sort (cost=6,896.12..6,910.39 rows=5,707 width=28) (actual time=58.414..59.310 rows=7,978 loops=1)

  • Sort Key: tp.id
  • Sort Method: quicksort Memory: 367kB
15. 8.607 57.596 ↑ 1.6 3,462 1

Hash Join (cost=5,587.51..6,540.05 rows=5,707 width=28) (actual time=48.461..57.596 rows=3,462 loops=1)

  • Hash Cond: (tp.id = pf.posting_id)
16. 0.956 0.956 ↑ 1.6 3,462 1

Seq Scan on temp_posting tp (cost=0.00..329.07 rows=5,707 width=14) (actual time=0.095..0.956 rows=3,462 loops=1)

17. 21.965 48.033 ↑ 1.0 100,067 1

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

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

Planning time : 1.142 ms
Execution time : 466.355 ms