explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 79EF : Optimization for: plan #iKZO

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 975.131 32,472.069 ↑ 107.1 32,314 1

Hash Left Join (cost=8,925,480.57..9,309,531.28 rows=3,462,242 width=164) (actual time=28,571.605..32,472.069 rows=32,314 loops=1)

  • Hash Cond: (((jclosed.stat)::text = (jsclosed.stat)::text) AND ((vk.objnr)::text = (jsclosed.objnr)::text) AND ((jclosed.chgnr)::text = (jsclosed.chgnr)::text))
  • Filter: ((jsclosed.udate IS NULL) OR ((jsclosed.udate)::text > ("left"(to_char((CURRENT_DATE - '1 year'::interval), 'YYYYMMdd'::text), 4) || '1231'::text)))
  • Rows Removed by Filter: 108,941
2. 1,465.120 26,437.432 ↑ 24.5 141,255 1

Hash Left Join (cost=6,868,575.22..7,225,360.77 rows=3,462,242 width=142) (actual time=23,509.783..26,437.432 rows=141,255 loops=1)

  • Hash Cond: ((vk.objnr)::text = (jclosed.objnr)::text)
3. 1,015.663 18,655.023 ↑ 2.5 141,255 1

Hash Left Join (cost=4,608,366.70..4,888,317.86 rows=352,911 width=132) (actual time=17,164.868..18,655.023 rows=141,255 loops=1)

  • Hash Cond: (((jcreated.stat)::text = (jscreated.stat)::text) AND ((vk.objnr)::text = (jscreated.objnr)::text) AND ((jcreated.chgnr)::text = (jscreated.chgnr)::text))
  • Filter: ((jscreated.udate IS NULL) OR ((jscreated.udate)::text < to_char(now(), 'YYYYMMdd'::text)))
4. 438.287 11,794.736 ↑ 2.5 141,255 1

Hash Left Join (cost=2,397,104.56..2,610,328.54 rows=352,911 width=142) (actual time=11,291.385..11,794.736 rows=141,255 loops=1)

  • Hash Cond: ((vk.objnr)::text = (jcreated.objnr)::text)
5. 0.000 96.776 ↓ 7.0 141,255 1

Gather (cost=1,404.47..103,758.15 rows=20,311 width=132) (actual time=3.818..96.776 rows=141,255 loops=1)

  • Workers Planned: 5
  • Workers Launched: 5
6. 8.348 216.316 ↓ 5.8 23,542 6 / 6

Hash Join (cost=404.48..102,758.15 rows=4,062 width=132) (actual time=3.516..216.316 rows=23,542 loops=6)

  • Hash Cond: ((vk.zz_prctr)::text = (prct.profit_center)::text)
7. 206.202 206.202 ↑ 1.2 28,891 6 / 6

Parallel Seq Scan on vbak vk (cost=0.00..102,185.89 rows=33,910 width=132) (actual time=1.628..206.202 rows=28,891 loops=6)

  • Filter: (((mandt)::text = '400'::text) AND (hvr_is_deleted = 0) AND ((auart)::text = ANY ('{ZFCA,ZFXA,YSBA,ZSCA,YSCA,ZSXA,ZKEA,ZSBA,YVCA}'::text[])))
  • Rows Removed by Filter: 102,202
8. 0.189 1.766 ↑ 1.0 1,168 6 / 6

Hash (cost=389.88..389.88 rows=1,168 width=7) (actual time=1.766..1.766 rows=1,168 loops=6)

  • Buckets: 2,048 Batches: 1 Memory Usage: 61kB
9. 1.577 1.577 ↑ 1.0 1,168 6 / 6

Seq Scan on cntrll_drm_profit_center_sot prct (cost=0.00..389.88 rows=1,168 width=7) (actual time=0.066..1.577 rows=1,168 loops=6)

  • Filter: (tier1_code = 'PXATCO'::text)
  • Rows Removed by Filter: 8,582
10. 329.054 11,259.673 ↑ 13.6 1,181,795 1

Hash (cost=2,084,493.21..2,084,493.21 rows=16,094,710 width=27) (actual time=11,259.673..11,259.673 rows=1,181,795 loops=1)

  • Buckets: 8,388,608 Batches: 4 Memory Usage: 83,478kB
11. 0.000 10,930.619 ↑ 13.6 1,181,795 1

Gather (cost=1,000.00..2,084,493.21 rows=16,094,710 width=27) (actual time=9.319..10,930.619 rows=1,181,795 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
12. 11,019.178 11,019.178 ↑ 15.6 147,724 8 / 8

Parallel Seq Scan on jest jcreated (cost=0.00..2,083,493.21 rows=2,299,244 width=27) (actual time=13.112..11,019.178 rows=147,724 loops=8)

  • Filter: (((inact)::text <> 'X'::text) AND ((mandt)::text = '400'::text) AND (hvr_is_deleted = 0) AND ((stat)::text = 'I0001'::text))
  • Rows Removed by Filter: 19,870,148
13. 3,491.855 5,844.624 ↓ 1.4 8,943,441 1

Hash (cost=2,050,972.94..2,050,972.94 rows=6,332,412 width=33) (actual time=5,844.623..5,844.624 rows=8,943,441 loops=1)

  • Buckets: 8,388,608 (originally 8388608) Batches: 2 (originally 1) Memory Usage: 446,465kB
14. 0.000 2,352.769 ↓ 1.4 8,943,441 1

Gather (cost=1,000.00..2,050,972.94 rows=6,332,412 width=33) (actual time=1.207..2,352.769 rows=8,943,441 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
15. 2,651.695 2,651.695 ↓ 1.2 1,117,930 8 / 8

Parallel Seq Scan on jcds jscreated (cost=0.00..2,049,972.94 rows=904,630 width=33) (actual time=0.949..2,651.695 rows=1,117,930 loops=8)

  • Filter: (((mandt)::text = '400'::text) AND (hvr_is_deleted = 0) AND ((stat)::text = 'I0001'::text))
  • Rows Removed by Filter: 14,121,309
16. 3,213.811 6,317.289 ↓ 1.5 13,557,705 1

Hash (cost=2,084,493.21..2,084,493.21 rows=9,087,465 width=27) (actual time=6,317.289..6,317.289 rows=13,557,705 loops=1)

  • Buckets: 8,388,608 Batches: 2 Memory Usage: 457,169kB
17. 318.450 3,103.478 ↓ 1.5 13,557,705 1

Gather (cost=1,000.00..2,084,493.21 rows=9,087,465 width=27) (actual time=1.271..3,103.478 rows=13,557,705 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
18. 2,785.028 2,785.028 ↓ 1.3 1,694,713 8 / 8

Parallel Seq Scan on jest jclosed (cost=0.00..2,083,493.21 rows=1,298,209 width=27) (actual time=0.110..2,785.028 rows=1,694,713 loops=8)

  • Filter: (((inact)::text <> 'X'::text) AND ((mandt)::text = '400'::text) AND (hvr_is_deleted = 0) AND ((stat)::text = 'I0046'::text))
  • Rows Removed by Filter: 18,323,160
19. 2,916.389 5,059.506 ↓ 22.1 7,487,077 1

Hash (cost=2,050,972.94..2,050,972.94 rows=338,995 width=33) (actual time=5,059.506..5,059.506 rows=7,487,077 loops=1)

  • Buckets: 8,388,608 (originally 524288) Batches: 2 (originally 1) Memory Usage: 446,465kB
20. 0.000 2,143.117 ↓ 22.1 7,487,077 1

Gather (cost=1,000.00..2,050,972.94 rows=338,995 width=33) (actual time=0.532..2,143.117 rows=7,487,077 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
21. 2,591.785 2,591.785 ↓ 19.3 935,885 8 / 8

Parallel Seq Scan on jcds jsclosed (cost=0.00..2,049,972.94 rows=48,428 width=33) (actual time=1.255..2,591.785 rows=935,885 loops=8)

  • Filter: (((mandt)::text = '400'::text) AND (hvr_is_deleted = 0) AND ((stat)::text = 'I0046'::text))
  • Rows Removed by Filter: 14,303,355
Planning time : 1.892 ms
Execution time : 32,561.825 ms