explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RQA3 : Optimization for: plan #VnNM

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,102.939 170,586.234 ↑ 126,307.5 352,530 1

Merge Left Join (cost=24,735,804.51..964,856,122.43 rows=44,527,170,439 width=10) (actual time=160,857.473..170,586.234 rows=352,530 loops=1)

  • Merge Cond: ((k.objnr)::text = (jscreated.objnr)::text)
  • 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)))
2. 0.000 107,297.475 ↑ 126,307.5 352,530 1

Merge Left Join (cost=21,985,704.12..523,947,070.36 rows=44,527,170,439 width=29) (actual time=99,452.470..107,297.475 rows=352,530 loops=1)

  • Merge Cond: ((k.objnr)::text = (jcreated.objnr)::text)
3. 3,822.718 96,132.199 ↑ 7,316.5 352,530 1

Gather Merge (cost=17,777,117.13..67,938,644.01 rows=2,579,271,679 width=19) (actual time=88,305.204..96,132.199 rows=352,530 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
4. 1,466.891 92,309.481 ↑ 8,361.7 44,066 8 / 8

Merge Left Join (cost=17,776,117.01..22,800,389.50 rows=368,467,383 width=19) (actual time=84,889.822..92,309.481 rows=44,066 loops=8)

  • Merge Cond: ((k.objnr)::text = (jsclosed.objnr)::text)
  • Join Filter: (((jsclosed.stat)::text = (jclosed.stat)::text) AND ((jsclosed.chgnr)::text = (jclosed.chgnr)::text))
  • Rows Removed by Join Filter: 30,631
  • 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: 158,470
5. 2,217.593 64,374.639 ↑ 1,819.3 202,536 8 / 8

Merge Left Join (cost=15,319,502.23..19,238,329.55 rows=368,467,383 width=29) (actual time=59,035.129..64,374.639 rows=202,536 loops=8)

  • Merge Cond: ((k.objnr)::text = (jclosed.objnr)::text)
6. 423.377 30,240.377 ↑ 186.6 202,536 8 / 8

Sort (cost=10,584,958.52..10,679,462.79 rows=37,801,707 width=19) (actual time=30,229.564..30,240.377 rows=202,536 loops=8)

  • Sort Key: k.objnr
  • Sort Method: quicksort Memory: 21,977kB
7. 485.856 29,817.000 ↑ 186.6 202,536 8 / 8

Merge Left Join (cost=4,775,629.41..5,373,180.74 rows=37,801,707 width=19) (actual time=29,179.560..29,817.000 rows=202,536 loops=8)

  • Merge Cond: ((cb.objnr)::text = (cs.objnr)::text)
8. 585.683 19,612.733 ↑ 6.6 201,579 8 / 8

Merge Join (cost=2,576,272.13..2,603,467.72 rows=1,332,074 width=33) (actual time=19,057.492..19,612.733 rows=201,579 loops=8)

  • Merge Cond: ((cb.objnr)::text = (p.objnr)::text)
9. 9,124.327 12,059.765 ↑ 1.1 1,832,173 8 / 8

Sort (cost=2,263,180.62..2,268,436.59 rows=2,102,390 width=14) (actual time=11,908.718..12,059.765 rows=1,832,173 loops=8)

  • Sort Key: cb.objnr
  • Sort Method: quicksort Memory: 156,845kB
10. 2,935.438 2,935.438 ↑ 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.009..2,935.438 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
11. 3,956.616 6,967.285 ↓ 5.8 618,074 8 / 8

Sort (cost=311,310.70..311,578.70 rows=107,199 width=29) (actual time=6,918.487..6,967.285 rows=618,074 loops=8)

  • Sort Key: p.objnr
  • Sort Method: quicksort Memory: 83,287kB
12. 112.635 3,010.669 ↓ 4.8 512,400 8 / 8

Nested Loop (cost=14.10..302,354.26 rows=107,199 width=29) (actual time=0.275..3,010.669 rows=512,400 loops=8)

13. 71.586 445.990 ↓ 8.5 116,764 8 / 8

Hash Join (cost=13.55..117,659.84 rows=13,675 width=19) (actual time=0.245..445.990 rows=116,764 loops=8)

  • Hash Cond: ((k.vkorg)::text = (tv.vkorg)::text)
14. 374.309 374.309 ↓ 1.1 171,806 8 / 8

Seq Scan on vbak k (cost=0.00..117,211.15 rows=163,453 width=24) (actual time=0.035..374.309 rows=171,806 loops=8)

  • Filter: (((mandt)::text = '400'::text) AND ((auart)::text = ANY ('{ZFCA,ZFXA,YSBA,ZSCA,YSCA,ZKBA,ZSXA,ZKEA,ZSBA,YVCA}'::text[])))
  • Rows Removed by Filter: 609,259
15. 0.007 0.095 ↑ 1.0 25 8 / 8

Hash (cost=13.23..13.23 rows=25 width=5) (actual time=0.095..0.095 rows=25 loops=8)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.088 0.088 ↑ 1.0 25 8 / 8

Seq Scan on tvko tv (cost=0.00..13.23 rows=25 width=5) (actual time=0.016..0.088 rows=25 loops=8)

  • Filter: (((mandt)::text = '400'::text) AND (hvr_is_deleted = 0) AND ((bukrs)::text = 'PY72'::text))
  • Rows Removed by Filter: 274
17. 2,452.044 2,452.044 ↑ 37.8 4 934,112 / 8

Index Scan using vbap_pkey on vbap p (cost=0.56..12.00 rows=151 width=20) (actual time=0.013..0.021 rows=4 loops=934,112)

  • Index Cond: (((mandt)::text = '400'::text) AND ((vbeln)::text = (k.vbeln)::text))
  • Filter: (hvr_is_deleted = 0)
  • Rows Removed by Filter: 0
18. 1,409.629 9,718.411 ↓ 1.0 4,805,431 8 / 8

Sort (cost=2,199,357.28..2,211,303.76 rows=4,778,590 width=14) (actual time=9,511.116..9,718.411 rows=4,805,431 loops=8)

  • Sort Key: cs.objnr
  • Sort Method: quicksort Memory: 402,588kB
19. 8,308.782 8,308.782 ↓ 1.0 4,799,848 8 / 8

Index Scan using coss_pkey on coss cs (cost=0.69..1,669,216.84 rows=4,778,590 width=14) (actual time=0.911..8,308.782 rows=4,799,848 loops=8)

  • Index Cond: (((mandt)::text = '400'::text) AND ((wrttp)::text = '01'::text))
  • Filter: (hvr_is_deleted = 0)
  • Rows Removed by Filter: 1,580
20. 15,832.735 31,916.669 ↓ 1.5 13,476,274 8 / 8

Sort (cost=4,734,543.71..4,757,116.20 rows=9,028,996 width=27) (actual time=28,805.558..31,916.669 rows=13,476,274 loops=8)

  • Sort Key: jclosed.objnr
  • Sort Method: external merge Disk: 490,248kB
21. 16,083.934 16,083.934 ↓ 1.5 13,476,285 8 / 8

Index Scan using jest_pkey on jest jclosed (cost=0.57..3,691,417.75 rows=9,028,996 width=27) (actual time=306.139..16,083.934 rows=13,476,285 loops=8)

  • Index Cond: (((mandt)::text = '400'::text) AND ((stat)::text = 'I0046'::text))
  • Filter: (((inact)::text <> 'X'::text) AND (hvr_is_deleted = 0))
  • Rows Removed by Filter: 26,867
22. 13,996.642 26,467.951 ↓ 22.1 7,454,254 8 / 8

Sort (cost=2,456,614.78..2,457,457.36 rows=337,035 width=33) (actual time=25,854.686..26,467.951 rows=7,454,254 loops=8)

  • Sort Key: jsclosed.objnr
  • Sort Method: external sort Disk: 335,528kB
23. 12,471.309 12,471.309 ↓ 22.0 7,426,397 8 / 8

Index Scan using jcds_pkey on jcds jsclosed (cost=0.57..2,425,670.69 rows=337,035 width=33) (actual time=127.921..12,471.309 rows=7,426,397 loops=8)

  • Index Cond: (((mandt)::text = '400'::text) AND ((stat)::text = 'I0046'::text))
  • Filter: (hvr_is_deleted = 0)
24. 8,369.474 11,236.400 ↑ 13.7 1,166,760 1

Sort (cost=4,208,586.99..4,248,564.88 rows=15,991,155 width=27) (actual time=11,147.260..11,236.400 rows=1,166,760 loops=1)

  • Sort Key: jcreated.objnr
  • Sort Method: quicksort Memory: 140,497kB
25. 0.000 2,866.926 ↑ 13.7 1,169,211 1

Gather (cost=1,000.00..2,071,087.92 rows=15,991,155 width=27) (actual time=2.724..2,866.926 rows=1,169,211 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
26. 2,911.063 2,911.063 ↑ 15.6 146,151 8 / 8

Parallel Seq Scan on jest jcreated (cost=0.00..2,070,087.92 rows=2,284,451 width=27) (actual time=2.867..2,911.063 rows=146,151 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,767,699
27. 59,796.493 62,185.820 ↓ 1.4 8,862,121 1

Sort (cost=2,750,100.39..2,765,839.87 rows=6,295,792 width=33) (actual time=61,404.996..62,185.820 rows=8,862,121 loops=1)

  • Sort Key: jscreated.objnr
  • Sort Method: external sort Disk: 385,752kB
28. 0.000 2,389.327 ↓ 1.4 8,864,572 1

Gather (cost=1,000.00..2,039,117.97 rows=6,295,792 width=33) (actual time=0.803..2,389.327 rows=8,864,572 loops=1)

  • Workers Planned: 7
  • Workers Launched: 7
29. 2,516.954 2,516.954 ↓ 1.2 1,108,072 8 / 8

Parallel Seq Scan on jcds jscreated (cost=0.00..2,038,117.97 rows=899,399 width=33) (actual time=0.009..2,516.954 rows=1,108,072 loops=8)

  • Filter: (((mandt)::text = '400'::text) AND (hvr_is_deleted = 0) AND ((stat)::text = 'I0001'::text))
  • Rows Removed by Filter: 14,043,645
Planning time : 5.331 ms
Execution time : 171,141.720 ms