explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6L63 : Optimization for: Optimization for: plan #VnNM; plan #RQA3

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 2,429.914 112,977.374 ↑ 4,454.3 352,257 1

Hash Left Join (cost=10,576,440.71..27,393,376.74 rows=1,569,069,141 width=10) (actual time=103,600.187..112,977.374 rows=352,257 loops=1)

  • Hash Cond: (((jclosed.stat)::text = (jsclosed.stat)::text) AND ((k.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,260,378
2. 1,767.384 105,594.116 ↑ 973.0 1,612,635 1

Hash Left Join (cost=8,531,424.62..12,991,940.55 rows=1,569,069,141 width=29) (actual time=98,636.180..105,594.116 rows=1,612,635 loops=1)

  • Hash Cond: ((k.objnr)::text = (jclosed.objnr)::text)
3. 2,154.365 97,137.601 ↑ 99.8 1,612,635 1

Merge Join (cost=6,285,752.26..8,375,578.75 rows=160,973,522 width=19) (actual time=91,920.359..97,137.601 rows=1,612,635 loops=1)

  • Merge Cond: ((cb.objnr)::text = (p.objnr)::text)
4. 81,251.171 84,019.833 ↑ 1.0 14,657,384 1

Sort (cost=3,942,801.35..3,979,593.17 rows=14,716,727 width=14) (actual time=74,780.658..84,019.833 rows=14,657,384 loops=1)

  • Sort Key: cb.objnr
  • Sort Method: external merge Disk: 351,712kB
5. 0.000 2,768.662 ↑ 1.0 14,657,384 1

Gather (cost=1,000.00..2,043,391.83 rows=14,716,727 width=14) (actual time=0.608..2,768.662 rows=14,657,384 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
6. 2,935.794 2,935.794 ↑ 1.1 1,832,173 8 / 8

Parallel Seq Scan on cosp cb (cost=0.00..2,042,391.83 rows=2,102,390 width=14) (actual time=0.008..2,935.794 rows=1,832,173 loops=8)

  • Filter: (((mandt)::text = '400'::text) AND (hvr_is_deleted = 0) AND ((wrttp)::text = '01'::text))
  • Rows Removed by Filter: 1,780,563
7. 3,759.787 10,963.403 ↑ 1.1 1,752,796 1

Sort (cost=2,141,490.61..2,146,117.17 rows=1,850,626 width=29) (actual time=10,849.674..10,963.403 rows=1,752,796 loops=1)

  • Sort Key: p.objnr
  • Sort Method: quicksort Memory: 83,287kB
8. 850.802 7,203.616 ↑ 3.6 512,400 1

Hash Join (cost=741,713.06..1,948,844.31 rows=1,850,626 width=29) (actual time=1,645.998..7,203.616 rows=512,400 loops=1)

  • Hash Cond: ((p.vbeln)::text = (k.vbeln)::text)
9. 0.000 4,709.535 ↑ 1.0 6,117,683 1

Gather (cost=1,000.00..1,166,670.74 rows=6,121,114 width=20) (actual time=1.795..4,709.535 rows=6,117,683 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
10. 4,849.882 4,849.882 ↑ 1.1 764,710 8 / 8

Parallel Seq Scan on vbap p (cost=0.00..1,165,670.74 rows=874,445 width=20) (actual time=0.860..4,849.882 rows=764,710 loops=8)

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

Hash (cost=737,762.08..737,762.08 rows=236,078 width=19) (actual time=1,643.279..1,643.279 rows=116,764 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 9,116kB
12. 54.938 1,608.154 ↑ 2.0 116,764 1

Nested Loop Left Join (cost=1,014.68..737,762.08 rows=236,078 width=19) (actual time=3.800..1,608.154 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)))
13. 0.000 35.284 ↑ 2.0 116,764 1

Gather (cost=1,014.12..105,342.91 rows=236,078 width=29) (actual time=2.210..35.284 rows=116,764 loops=1)

  • Workers Planned: 5
  • Workers Launched: 5
14. 9.170 335.697 ↑ 2.4 19,461 6 / 6

Nested Loop Left Join (cost=14.12..104,342.91 rows=47,216 width=29) (actual time=0.361..335.697 rows=19,461 loops=6)

15. 11.452 73.538 ↓ 7.1 19,461 6 / 6

Hash Join (cost=13.55..101,694.80 rows=2,735 width=19) (actual time=0.173..73.538 rows=19,461 loops=6)

  • Hash Cond: ((k.vkorg)::text = (tv.vkorg)::text)
16. 61.993 61.993 ↑ 1.1 28,634 6 / 6

Parallel Seq Scan on vbak k (cost=0.00..101,594.23 rows=32,691 width=24) (actual time=0.006..61.993 rows=28,634 loops=6)

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

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

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

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

  • Filter: (((mandt)::text = '400'::text) AND (hvr_is_deleted = 0) AND ((bukrs)::text = 'PY72'::text))
  • Rows Removed by Filter: 274
19. 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 = (k.objnr)::text) AND ((stat)::text = 'I0001'::text))
  • Filter: (((inact)::text <> 'X'::text) AND (hvr_is_deleted = 0))
20. 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 = (k.objnr)::text) AND ((stat)::text = 'I0001'::text))
  • Filter: (hvr_is_deleted = 0)
21. 3,337.053 6,689.131 ↓ 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,689.131..6,689.131 rows=13,476,285 loops=1)

  • Buckets: 8,388,608 Batches: 2 Memory Usage: 454,904kB
22. 0.000 3,352.078 ↓ 1.5 13,476,285 1

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

  • Workers Planned: 7
  • Workers Launched: 7
23. 3,472.081 3,472.081 ↓ 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=6.589..3,472.081 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
24. 2,692.022 4,953.344 ↓ 22.0 7,426,397 1

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

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

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

  • Workers Planned: 7
  • Workers Launched: 7
26. 2,824.064 2,824.064 ↓ 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=4.120..2,824.064 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 : 4.637 ms
Execution time : 113,098.521 ms