explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Wp4Z

Settings
# exclusive inclusive rows x rows loops node
1. 242.566 63,664.444 ↑ 22.4 27,287 1

GroupAggregate (cost=444,472,509.92..687,333,497.19 rows=612,254 width=205) (actual time=63,399.514..63,664.444 rows=27,287 loops=1)

  • Group Key: vp.vbeln, vp.waerk
2. 928.065 63,421.878 ↑ 5,879.9 412,977 1

Sort (cost=444,472,509.92..450,543,192.75 rows=2,428,273,133 width=24) (actual time=63,399.465..63,421.878 rows=412,977 loops=1)

  • Sort Key: vp.vbeln, vp.waerk
  • Sort Method: quicksort Memory: 44,552kB
3. 3,274.027 62,493.813 ↑ 5,879.9 412,977 1

Hash Left Join (cost=9,877,014.32..36,769,975.63 rows=2,428,273,133 width=24) (actual time=55,638.983..62,493.813 rows=412,977 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: 1,879,990
4. 2,066.811 54,599.330 ↑ 1,059.0 2,292,967 1

Hash Left Join (cost=7,831,998.24..15,602,307.64 rows=2,428,273,133 width=43) (actual time=51,005.418..54,599.330 rows=2,292,967 loops=1)

  • Hash Cond: ((vk.objnr)::text = (jclosed.objnr)::text)
5. 1,272.685 46,421.095 ↑ 108.6 2,292,967 1

Merge Join (cost=5,586,325.87..8,748,456.31 rows=249,120,748 width=33) (actual time=44,865.183..46,421.095 rows=2,292,967 loops=1)

  • Merge Cond: ((cb.objnr)::text = (vp.objnr)::text)
6. 27,864.565 37,887.804 ↑ 1.3 6,627,104 1

Sort (cost=3,133,984.60..3,154,881.19 rows=8,358,635 width=23) (actual time=36,914.700..37,887.804 rows=6,627,104 loops=1)

  • Sort Key: cb.objnr
  • Sort Method: external merge Disk: 237,048kB
7. 0.000 10,023.239 ↑ 1.3 6,627,104 1

Gather (cost=1,000.00..2,172,957.40 rows=8,358,635 width=23) (actual time=2.383..10,023.239 rows=6,627,104 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
8. 10,600.199 10,600.199 ↑ 1.4 828,388 8 / 8

Parallel Seq Scan on coepb cb (cost=0.00..2,171,957.40 rows=1,194,091 width=23) (actual time=3.186..10,600.199 rows=828,388 loops=8)

  • Filter: (((mandt)::text = '400'::text) AND (hvr_is_deleted = 0) AND ((kokrs)::text = '1000'::text) AND ((versn)::text = '000'::text) AND ((wrttp)::text = '32'::text) AND ((abkat)::text = ANY ('{63,75,60,71,72,64}'::text[])))
  • Rows Removed by Filter: 10,019,222
9. 3,793.512 7,260.606 ↓ 1.5 2,691,558 1

Sort (cost=2,141,816.49..2,146,442.63 rows=1,850,454 width=32) (actual time=7,114.559..7,260.606 rows=2,691,558 loops=1)

  • Sort Key: vp.objnr
  • Sort Method: quicksort Memory: 83,348kB
10. 634.536 3,467.094 ↑ 3.6 512,400 1

Hash Join (cost=742,059.42..1,949,189.34 rows=1,850,454 width=32) (actual time=1,655.794..3,467.094 rows=512,400 loops=1)

  • Hash Cond: ((vp.vbeln)::text = (vk.vbeln)::text)
11. 0.000 1,179.764 ↑ 1.0 6,117,683 1

Gather (cost=1,000.00..1,166,670.74 rows=6,121,114 width=23) (actual time=2.054..1,179.764 rows=6,117,683 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
12. 1,478.514 1,478.514 ↑ 1.1 764,710 8 / 8

Parallel Seq Scan on vbap vp (cost=0.00..1,165,670.74 rows=874,445 width=23) (actual time=0.952..1,478.514 rows=764,710 loops=8)

  • Filter: (((mandt)::text = '400'::text) AND (hvr_is_deleted = 0))
  • Rows Removed by Filter: 264
13. 35.308 1,652.794 ↑ 2.0 116,764 1

Hash (cost=738,108.65..738,108.65 rows=236,061 width=19) (actual time=1,652.794..1,652.794 rows=116,764 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 9,116kB
14. 61.617 1,617.486 ↑ 2.0 116,764 1

Nested Loop Left Join (cost=1,014.68..738,108.65 rows=236,061 width=19) (actual time=1.218..1,617.486 rows=116,764 loops=1)

  • Join Filter: (((jscreated.stat)::text = (jcreated.stat)::text) AND ((jscreated.chgnr)::text = (jcreated.chgnr)::text))
  • Filter: ((jscreated.udate IS NULL) OR ((jscreated.udate)::text < to_char(now(), 'YYYYMMdd'::text)))
15. 0.000 37.937 ↑ 2.0 116,764 1

Gather (cost=1,014.12..105,733.40 rows=236,061 width=29) (actual time=1.179..37.937 rows=116,764 loops=1)

  • Workers Planned: 5
  • Workers Launched: 5
16. 11.866 438.054 ↑ 2.4 19,461 6 / 6

Nested Loop Left Join (cost=14.12..104,733.40 rows=47,212 width=29) (actual time=0.205..438.054 rows=19,461 loops=6)

17. 5.374 173.199 ↓ 7.1 19,461 6 / 6

Hash Join (cost=13.55..102,085.21 rows=2,735 width=19) (actual time=0.171..173.199 rows=19,461 loops=6)

  • Hash Cond: ((vk.vkorg)::text = (tv.vkorg)::text)
18. 167.735 167.735 ↑ 1.1 28,632 6 / 6

Parallel Seq Scan on vbak vk (cost=0.00..101,984.65 rows=32,687 width=24) (actual time=0.015..167.735 rows=28,632 loops=6)

  • Filter: (((mandt)::text = '400'::text) AND (hvr_is_deleted = 0) AND ((auart)::text = ANY ('{ZFCA,ZFXA,YSBA,ZSCA,YSCA,ZKBA,ZSXA,ZKEA,ZSBA,YVCA}'::text[])))
  • Rows Removed by Filter: 101,546
19. 0.006 0.090 ↑ 1.0 25 6 / 6

Hash (cost=13.23..13.23 rows=25 width=5) (actual time=0.090..0.090 rows=25 loops=6)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
20. 0.084 0.084 ↑ 1.0 25 6 / 6

Seq Scan on tvko tv (cost=0.00..13.23 rows=25 width=5) (actual time=0.014..0.084 rows=25 loops=6)

  • Filter: (((mandt)::text = '400'::text) AND (hvr_is_deleted = 0) AND ((bukrs)::text = 'PY72'::text))
  • Rows Removed by Filter: 274
21. 252.989 252.989 ↓ 0.0 0 116,764 / 6

Index Scan using jest_pkey on jest jcreated (cost=0.57..7.34 rows=17 width=27) (actual time=0.013..0.013 rows=0 loops=116,764)

  • Index Cond: (((mandt)::text = '400'::text) AND ((objnr)::text = (vk.objnr)::text) AND ((stat)::text = 'I0001'::text))
  • Filter: (((inact)::text <> 'X'::text) AND (hvr_is_deleted = 0))
22. 1,517.932 1,517.932 ↓ 0.0 0 116,764

Index Scan using jcds_pkey on jcds jscreated (cost=0.57..2.66 rows=1 width=33) (actual time=0.013..0.013 rows=0 loops=116,764)

  • Index Cond: (((mandt)::text = '400'::text) AND ((objnr)::text = (vk.objnr)::text) AND ((stat)::text = 'I0001'::text))
  • Filter: (hvr_is_deleted = 0)
23. 3,205.753 6,111.424 ↓ 1.5 13,476,285 1

Hash (cost=2,071,087.92..2,071,087.92 rows=9,028,996 width=27) (actual time=6,111.424..6,111.424 rows=13,476,285 loops=1)

  • Buckets: 8,388,608 Batches: 2 Memory Usage: 454,904kB
24. 80.155 2,905.671 ↓ 1.5 13,476,285 1

Gather (cost=1,000.00..2,071,087.92 rows=9,028,996 width=27) (actual time=7.596..2,905.671 rows=13,476,285 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
25. 2,825.516 2,825.516 ↓ 1.3 1,684,536 8 / 8

Parallel Seq Scan on jest jclosed (cost=0.00..2,070,087.92 rows=1,289,857 width=27) (actual time=0.883..2,825.516 rows=1,684,536 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,229,315
26. 2,592.078 4,620.456 ↓ 22.0 7,426,397 1

Hash (cost=2,039,117.97..2,039,117.97 rows=337,035 width=33) (actual time=4,620.456..4,620.456 rows=7,426,397 loops=1)

  • Buckets: 8,388,608 (originally 524288) Batches: 2 (originally 1) Memory Usage: 446,465kB
27. 0.000 2,028.378 ↓ 22.0 7,426,397 1

Gather (cost=1,000.00..2,039,117.97 rows=337,035 width=33) (actual time=0.694..2,028.378 rows=7,426,397 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
28. 2,463.078 2,463.078 ↓ 19.3 928,300 8 / 8

Parallel Seq Scan on jcds jsclosed (cost=0.00..2,038,117.97 rows=48,148 width=33) (actual time=1.352..2,463.078 rows=928,300 loops=8)

  • Filter: (((mandt)::text = '400'::text) AND (hvr_is_deleted = 0) AND ((stat)::text = 'I0046'::text))
  • Rows Removed by Filter: 14,223,417
Planning time : 48.905 ms
Execution time : 63,763.112 ms