explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iKZO

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 968.503 35,520.052 ↑ 107.1 32,314 1

Hash Left Join (cost=2,212,668.33..2,683,622.36 rows=3,462,242 width=164) (actual time=13,912.372..35,520.052 rows=32,314 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)))
2. 0.000 20,693.682 ↑ 107.1 32,314 1

Gather (cost=1,406.18..253,615.06 rows=3,462,242 width=142) (actual time=26.917..20,693.682 rows=32,314 loops=1)

  • Workers Planned: 5
  • Workers Launched: 5
3. 8.928 32,142.544 ↑ 128.6 5,386 6 / 6

Nested Loop Left Join (cost=406.18..252,615.06 rows=692,448 width=142) (actual time=138.159..32,142.544 rows=5,386 loops=6)

4. 84.228 32,042.060 ↑ 7.4 5,386 6 / 6

Nested Loop Left Join (cost=405.61..214,367.15 rows=39,852 width=132) (actual time=138.130..32,042.060 rows=5,386 loops=6)

  • Join Filter: (((jsclosed.stat)::text = (jclosed.stat)::text) AND ((jsclosed.chgnr)::text = (jclosed.chgnr)::text))
  • Rows Removed by Join Filter: 2,304
  • 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: 18,157
5. 23.362 13,924.277 ↑ 1.7 23,542 6 / 6

Nested Loop Left Join (cost=405.05..107,463.65 rows=39,852 width=142) (actual time=14.169..13,924.277 rows=23,542 loops=6)

6. 19.366 1,235.050 ↓ 5.8 23,542 6 / 6

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

  • Hash Cond: ((vk.zz_prctr)::text = (prct.profit_center)::text)
7. 1,206.061 1,206.061 ↑ 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=0.012..1,206.061 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.180 9.623 ↑ 1.0 1,168 6 / 6

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 61kB
9. 9.443 9.443 ↑ 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.262..9.443 rows=1,168 loops=6)

  • Filter: (tier1_code = 'PXATCO'::text)
  • Rows Removed by Filter: 8,582
10. 12,665.865 12,665.865 ↑ 10.0 1 141,255 / 6

Index Scan using jest_pkey on jest jclosed (cost=0.57..6.45 rows=10 width=27) (actual time=0.538..0.538 rows=1 loops=141,255)

  • Index Cond: (((mandt)::text = '400'::text) AND ((objnr)::text = (vk.objnr)::text) AND ((stat)::text = 'I0046'::text))
  • Filter: (((inact)::text <> 'X'::text) AND (hvr_is_deleted = 0))
  • Rows Removed by Filter: 0
11. 18,033.555 18,033.555 ↑ 1.0 1 141,255 / 6

Index Scan using jcds_pkey on jcds jsclosed (cost=0.57..2.65 rows=1 width=33) (actual time=0.744..0.766 rows=1 loops=141,255)

  • Index Cond: (((mandt)::text = '400'::text) AND ((objnr)::text = (vk.objnr)::text) AND ((stat)::text = 'I0046'::text))
  • Filter: (hvr_is_deleted = 0)
12. 91.556 91.556 ↓ 0.0 0 32,314 / 6

Index Scan using jest_pkey on jest jcreated (cost=0.57..7.20 rows=17 width=27) (actual time=0.017..0.017 rows=0 loops=32,314)

  • 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))
13. 4,127.786 13,857.867 ↓ 1.4 8,943,441 1

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

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

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

  • Workers Planned: 7
  • Workers Launched: 7
15. 10,733.237 10,733.237 ↓ 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=1.102..10,733.237 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
Planning time : 54.108 ms
Execution time : 35,544.717 ms