explain.depesz.com

PostgreSQL's explain analyze made readable

Result: raFI

Settings
# exclusive inclusive rows x rows loops node
1. 890.354 1,723,587.870 ↓ 16.7 669,780 1

GroupAggregate (cost=1,251,091,069,601.53..1,251,091,280,642.53 rows=40,000 width=235) (actual time=1,722,654.810..1,723,587.870 rows=669,780 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. 458.421 1,722,697.516 ↑ 7.2 893,040 1

Sort (cost=1,251,091,069,601.53..1,251,091,085,758.53 rows=6,462,800 width=267) (actual time=1,722,654.789..1,722,697.516 rows=893,040 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: 97,748kB
3.          

CTE cte_period

4. 0.033 0.067 ↑ 6,944.4 144 1

Nested Loop (cost=0.00..20,052.54 rows=1,000,000 width=32) (actual time=0.027..0.067 rows=144 loops=1)

5. 0.009 0.018 ↑ 62.5 16 1

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

6. 0.009 0.009 ↑ 62.5 16 1

ProjectSet (cost=0.00..5.02 rows=1,000 width=4) (actual time=0.008..0.009 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.006 0.016 ↑ 111.1 9 16

Materialize (cost=0.00..20.03 rows=1,000 width=4) (actual time=0.001..0.001 rows=9 loops=16)

9. 0.010 0.010 ↑ 111.1 9 1

ProjectSet (cost=0.00..5.03 rows=1,000 width=4) (actual time=0.009..0.010 rows=9 loops=1)

10. 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)

11. 325,472.818 1,722,239.095 ↑ 7.2 893,040 1

GroupAggregate (cost=663,615,817,745.76..1,251,089,839,769.84 rows=6,462,800 width=267) (actual time=1,282,503.884..1,722,239.095 rows=893,040 loops=1)

  • Group Key: k.vbeln, cte_period.fiscal_year_and_period
12. 1,213,990.224 1,396,766.277 ↑ 3,644.4 632,155,200 1

Sort (cost=663,615,817,745.76..669,375,361,912.43 rows=2,303,817,666,667 width=63) (actual time=1,282,503.829..1,396,766.277 rows=632,155,200 loops=1)

  • Sort Key: k.vbeln, cte_period.fiscal_year_and_period
  • Sort Method: external merge Disk: 23,709,128kB
13. 83,606.521 182,776.053 ↑ 3,644.4 632,155,200 1

Merge Join (cost=1,785,912.19..38,359,077,471.10 rows=2,303,817,666,667 width=63) (actual time=52,371.548..182,776.053 rows=632,155,200 loops=1)

  • Merge Cond: ((cb.objnr)::text = (cb2.objnr)::text)
14. 1,386.830 29,986.856 ↑ 1.0 6,690,873 1

Merge Left Join (cost=905,788.09..1,042,729.59 rows=6,911,453 width=45) (actual time=27,350.273..29,986.856 rows=6,690,873 loops=1)

  • Merge Cond: ((cb.objnr)::text = (p.objnr)::text)
15. 26,090.796 27,108.631 ↓ 1.0 6,690,873 1

Sort (cost=880,124.10..896,846.47 rows=6,688,951 width=34) (actual time=25,894.302..27,108.631 rows=6,690,873 loops=1)

  • Sort Key: cb.objnr
  • Sort Method: external merge Disk: 298,168kB
16. 1,017.835 1,017.835 ↓ 1.0 6,690,873 1

Seq Scan on coepb_limited cb (cost=0.00..121,819.51 rows=6,688,951 width=34) (actual time=0.024..1,017.835 rows=6,690,873 loops=1)

17. 1,421.861 1,491.395 ↓ 2.8 561,412 1

Sort (cost=25,573.93..26,076.35 rows=200,968 width=29) (actual time=1,455.777..1,491.395 rows=561,412 loops=1)

  • Sort Key: p.objnr
  • Sort Method: quicksort Memory: 21,815kB
18. 38.406 69.534 ↑ 1.0 200,968 1

Hash Left Join (cost=1,622.06..7,872.06 rows=200,968 width=29) (actual time=12.710..69.534 rows=200,968 loops=1)

  • Hash Cond: ((p.vbeln)::text = (k.vbeln)::text)
19. 18.561 18.561 ↑ 1.0 200,968 1

Seq Scan on vbap_limited p (cost=0.00..3,486.68 rows=200,968 width=29) (actual time=0.027..18.561 rows=200,968 loops=1)

20. 4.874 12.567 ↑ 1.0 32,314 1

Hash (cost=1,218.14..1,218.14 rows=32,314 width=16) (actual time=12.567..12.567 rows=32,314 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,771kB
21. 7.693 7.693 ↑ 1.0 32,314 1

Seq Scan on sdasco_vbak_limited k (cost=0.00..1,218.14 rows=32,314 width=16) (actual time=0.007..7.693 rows=32,314 loops=1)

22. 28,646.421 69,182.676 ↑ 57.2 632,155,190 1

Materialize (cost=880,124.10..3,891,224,129.22 rows=36,188,666,667 width=46) (actual time=25,021.272..69,182.676 rows=632,155,190 loops=1)

23. 10,008.461 40,536.255 ↑ 1,212.5 29,847,248 1

Nested Loop (cost=880,124.10..3,800,752,462.55 rows=36,188,666,667 width=46) (actual time=25,021.264..40,536.255 rows=29,847,248 loops=1)

  • Join Filter: (("left"(cte_period.fiscal_year_and_period, 4))::integer >= ((min((cb2.gjahr)::text)))::integer)
  • Rows Removed by Join Filter: 16,632,064
24. 1,440.267 27,300.064 ↓ 3.0 322,773 1

GroupAggregate (cost=880,124.10..931,376.89 rows=108,566 width=46) (actual time=25,021.230..27,300.064 rows=322,773 loops=1)

  • Group Key: cb2.objnr
25. 25,105.497 25,859.797 ↓ 1.0 6,690,873 1

Sort (cost=880,124.10..896,846.47 rows=6,688,951 width=19) (actual time=25,021.198..25,859.797 rows=6,690,873 loops=1)

  • Sort Key: cb2.objnr
  • Sort Method: external merge Disk: 192,760kB
26. 754.300 754.300 ↓ 1.0 6,690,873 1

Seq Scan on coepb_limited cb2 (cost=0.00..121,819.51 rows=6,688,951 width=19) (actual time=0.017..754.300 rows=6,690,873 loops=1)

27. 3,227.730 3,227.730 ↑ 6,944.4 144 322,773

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=322,773)

Planning time : 1.078 ms
Execution time : 1,726,316.619 ms