explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 12qR

Settings
# exclusive inclusive rows x rows loops node
1. 1,070.656 1,874,643.254 ↓ 16.9 676,896 1

GroupAggregate (cost=1,417,990,265,436.07..1,417,990,477,127.07 rows=40,000 width=234) (actual time=1,873,520.964..1,874,643.254 rows=676,896 loops=1)

  • Group Key: k.vbeln, (CASE WHEN (("right"(cte_period.fiscal_year_and_period, 2))::integer >= 12) THEN ("left"(cte_period.fiscal_year_and_period, 4) || '12'::text) ELSE cte_period.fiscal_year_and_period END)
2. 477.250 1,873,572.598 ↑ 7.2 902,528 1

Sort (cost=1,417,990,265,436.07..1,417,990,281,643.07 rows=6,482,800 width=266) (actual time=1,873,520.939..1,873,572.598 rows=902,528 loops=1)

  • Sort Key: k.vbeln, (CASE WHEN (("right"(cte_period.fiscal_year_and_period, 2))::integer >= 12) THEN ("left"(cte_period.fiscal_year_and_period, 4) || '12'::text) ELSE cte_period.fiscal_year_and_period END)
  • Sort Method: quicksort Memory: 98,548kB
3.          

CTE cte_period

4. 0.030 0.075 ↑ 6,944.4 144 1

Nested Loop (cost=0.00..32,562.52 rows=1,000,000 width=32) (actual time=0.024..0.075 rows=144 loops=1)

5. 0.008 0.013 ↑ 62.5 16 1

Result (cost=0.00..25.02 rows=1,000 width=32) (actual time=0.009..0.013 rows=16 loops=1)

6. 0.005 0.005 ↑ 62.5 16 1

ProjectSet (cost=0.00..5.02 rows=1,000 width=4) (actual time=0.004..0.005 rows=16 loops=1)

7. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)

8. 0.032 0.032 ↑ 111.1 9 16

ProjectSet (cost=0.00..5.03 rows=1,000 width=4) (actual time=0.001..0.002 rows=9 loops=16)

9. 0.000 0.000 ↑ 1.0 1 16

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=16)

10. 346,508.105 1,873,095.348 ↑ 7.2 902,528 1

GroupAggregate (cost=831,570,835,779.54..1,417,989,499,403.54 rows=6,482,800 width=266) (actual time=1,406,553.923..1,873,095.348 rows=902,528 loops=1)

  • Group Key: k.vbeln, cte_period.fiscal_year_and_period
11. 1,322,095.004 1,526,587.243 ↑ 3,637.3 632,243,328 1

Sort (cost=831,570,835,779.54..837,320,033,279.54 rows=2,299,679,000,000 width=62) (actual time=1,406,553.868..1,526,587.243 rows=632,243,328 loops=1)

  • Sort Key: k.vbeln, cte_period.fiscal_year_and_period
  • Sort Method: external merge Disk: 23,712,824kB
12. 92,979.353 204,492.239 ↑ 3,637.3 632,243,328 1

Merge Join (cost=1,788,074.79..38,247,614,499.04 rows=2,299,679,000,000 width=62) (actual time=56,949.396..204,492.239 rows=632,243,328 loops=1)

  • Merge Cond: ((cb.objnr)::text = (cb2.objnr)::text)
13. 1,610.013 32,429.941 ↑ 1.0 6,695,856 1

Merge Left Join (cost=906,913.68..1,043,723.33 rows=6,899,037 width=44) (actual time=29,887.919..32,429.941 rows=6,695,856 loops=1)

  • Merge Cond: ((cb.objnr)::text = (p.objnr)::text)
14. 28,039.465 29,225.192 ↑ 1.0 6,695,856 1

Sort (cost=881,161.11..897,902.51 rows=6,696,563 width=34) (actual time=28,336.742..29,225.192 rows=6,695,856 loops=1)

  • Sort Key: cb.objnr
  • Sort Method: quicksort Memory: 719,722kB
15. 1,185.727 1,185.727 ↑ 1.0 6,695,856 1

Seq Scan on coepb_limited cb (cost=0.00..121,938.63 rows=6,696,563 width=34) (actual time=0.697..1,185.727 rows=6,695,856 loops=1)

16. 1,524.598 1,594.736 ↓ 2.8 566,346 1

Sort (cost=25,670.90..26,175.12 rows=201,688 width=28) (actual time=1,550.971..1,594.736 rows=566,346 loops=1)

  • Sort Key: p.objnr
  • Sort Method: quicksort Memory: 21,872kB
17. 41.872 70.138 ↑ 1.0 201,688 1

Hash Left Join (cost=1,627.31..7,900.40 rows=201,688 width=28) (actual time=13.281..70.138 rows=201,688 loops=1)

  • Hash Cond: ((p.vbeln)::text = (k.vbeln)::text)
18. 15.093 15.093 ↑ 1.0 201,688 1

Seq Scan on vbap_limited p (cost=0.00..3,499.88 rows=201,688 width=29) (actual time=0.009..15.093 rows=201,688 loops=1)

19. 5.171 13.173 ↑ 1.0 32,414 1

Hash (cost=1,222.14..1,222.14 rows=32,414 width=15) (actual time=13.173..13.173 rows=32,414 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,776kB
20. 8.002 8.002 ↑ 1.0 32,414 1

Seq Scan on sdasco_vbak_limited k (cost=0.00..1,222.14 rows=32,414 width=15) (actual time=0.005..8.002 rows=32,414 loops=1)

21. 34,731.265 79,082.945 ↑ 56.5 632,243,318 1

Materialize (cost=881,161.11..3,840,664,361.76 rows=35,718,333,333 width=46) (actual time=27,061.472..79,082.945 rows=632,243,318 loops=1)

22. 11,621.965 44,351.680 ↑ 1,196.3 29,856,720 1

Nested Loop (cost=881,161.11..3,751,368,528.43 rows=35,718,333,333 width=46) (actual time=27,061.463..44,351.680 rows=29,856,720 loops=1)

  • Join Filter: (("left"(cte_period.fiscal_year_and_period, 4))::integer >= ((min((cb2.gjahr)::text)))::integer)
  • Rows Removed by Join Filter: 16,707,840
23. 1,620.341 29,496.065 ↓ 3.0 323,365 1

GroupAggregate (cost=881,161.11..932,456.88 rows=107,155 width=46) (actual time=27,061.431..29,496.065 rows=323,365 loops=1)

  • Group Key: cb2.objnr
24. 27,137.243 27,875.724 ↑ 1.0 6,695,856 1

Sort (cost=881,161.11..897,902.51 rows=6,696,563 width=19) (actual time=27,061.392..27,875.724 rows=6,695,856 loops=1)

  • Sort Key: cb2.objnr
  • Sort Method: quicksort Memory: 600,589kB
25. 738.481 738.481 ↑ 1.0 6,695,856 1

Seq Scan on coepb_limited cb2 (cost=0.00..121,938.63 rows=6,696,563 width=19) (actual time=0.010..738.481 rows=6,695,856 loops=1)

26. 3,233.650 3,233.650 ↑ 6,944.4 144 323,365

CTE Scan on cte_period (cost=0.00..20,000.00 rows=1,000,000 width=32) (actual time=0.000..0.010 rows=144 loops=323,365)

Planning time : 1.134 ms
Execution time : 1,878,948.405 ms