explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Mq0T : CTE

Settings
# exclusive inclusive rows x rows loops node
1. 43,256.407 259,130.860 ↓ 11.3 5,929,968 1

Gather Motion 4:1 (slice6; segments: 4) (cost=0.00..5,526.20 rows=522,508 width=897) (actual time=134,357.119..259,130.860 rows=5,929,968 loops=1)

  • (seg2) Wrote 363920K bytes to inner workfile.
  • (seg2) Wrote 1170434K bytes to outer workfile.
  • (seg2) Overflow batches 1..15:
  • (seg2) Read 555708K bytes from inner workfile: 37048K avg x 15 nonempty batches, 90050K max.
  • (seg2) Wrote 191789K bytes to inner workfile: 27399K avg x 7 overflowing batches, 65788K max.
  • (seg2) Read 1170434K bytes from outer workfile: 78029K avg x 15 nonempty batches, 78750K max.
  • (seg2) Hash chain length 3.0 avg, 14 max, using 493341 of 524288 buckets.1485212 groups total in 288 batches; 33 overflows; 2970424 spill groups.
  • (seg2) Hash chain length 3.1 avg, 14 max, using 557268 of 590336 buckets; total 36 expansions.
  • (seg2) Hash chain length 3.1 avg, 15 max, using 509462 of 540672 buckets; total 9 expansions.
  • (seg1) Hash chain length 3.0 avg, 14 max, using 554736 of 589824 buckets; total 11 expansions.
  • (slice0) Executor memory: 6205K bytes.
  • (slice1) Executor memory: 4792K bytes avg x 4 workers, 4792K bytes max (seg0). Work_mem: 318K bytes max.
  • (slice2) Executor memory: 1198K bytes avg x 4 workers, 1198K bytes max (seg0). Work_mem: 318K bytes max.
  • (slice3) Executor memory: 2383K bytes avg x 4 workers, 2383K bytes max (seg0). Work_mem: 43K bytes max.
  • (slice4) Executor memory: 354K bytes avg x 4 workers, 354K bytes max (seg0).
  • * (slice5) Executor memory: 47139K bytes avg x 4 workers, 47139K bytes max (seg0). Work_mem: 44292K bytes max, 374017K bytes wanted.
  • * (slice6) Executor memory: 185254K bytes avg x 4 workers, 185268K bytes max (seg2). Work_mem: 45672K bytes max, 3841909K bytes wanted.
  • Memory used: 128000kB
  • Memory wanted: 38421083kB
  • Optimizer: Pivotal Optimizer (GPORCA) version 3.88.0
2. 14,908.410 215,874.453 ↓ 11.4 1,483,426 1

Hash Join (cost=0.00..3,958.44 rows=130,627 width=897) (actual time=134,404.387..215,874.453 rows=1,483,426 loops=1)

  • Hash Cond: (((cdp_dict."MACROREGION_NAME")::text = (cdp_dict_1."MACROREGION_NAME")::text) AND ((cdp_dict."REGION_NAME")::text = (cdp_dict_1."REGION_NAME")::text) AND ((cdp_main_old."YEAR_MONTH")::text = (cdp_main_old_1."YEAR_MONTH")::text))
  • Extra Text: (seg2) Initial batch 0:
3. 118,478.727 162,712.352 ↓ 26.0 1,485,212 1

HashAggregate (cost=0.00..1,118.21 rows=57,147 width=873) (actual time=96,149.890..162,712.352 rows=1,485,212 loops=1)

  • Group Key: cdp_dict."MACROREGION_NAME", cdp_dict."REGION_NAME", cdp_main_old."YEAR_MONTH
  • Extra Text: (seg2) 1485212 groups total in 288 batches; 33 overflows; 2970424 spill groups.
4. 12,914.405 44,233.625 ↓ 26.0 1,485,212 1

Result (cost=0.00..1,052.89 rows=57,147 width=471) (actual time=0.080..44,233.625 rows=1,485,212 loops=1)

5. 29,864.593 31,319.220 ↓ 26.0 1,485,212 1

Redistribute Motion 4:4 (slice1; segments: 4) (cost=0.00..1,025.98 rows=57,147 width=362) (actual time=0.024..31,319.220 rows=1,485,212 loops=1)

  • Hash Key: cdp_dict."MACROREGION_NAME", cdp_dict."REGION_NAME", cdp_main_old."YEAR_MONTH
6. 1,338.559 1,454.627 ↓ 26.3 1,501,130 1

Hash Left Join (cost=0.00..961.23 rows=57,147 width=362) (actual time=10.988..1,454.627 rows=1,501,130 loops=1)

  • Hash Cond: (cdp_main_old."BRANCH_ID" = cdp_dict."BRANCH_ID")
  • Extra Text: (seg1) Hash chain length 11.0 avg, 166 max, using 123 of 8192 buckets.1485212 groups total in 32 batches; 1 overflows; 1485212 spill groups.
7. 113.832 113.832 ↓ 1.0 25,209 1

Seq Scan on cdp_main_old (cost=0.00..435.47 rows=25,000 width=165) (actual time=8.205..113.832 rows=25,209 loops=1)

8. 0.958 2.236 ↑ 1.0 1,356 1

Hash (cost=431.79..431.79 rows=1,356 width=207) (actual time=2.236..2.236 rows=1,356 loops=1)

9. 1.278 1.278 ↑ 1.0 1,356 1

Seq Scan on cdp_dict (cost=0.00..431.79 rows=1,356 width=207) (actual time=0.023..1.278 rows=1,356 loops=1)

10. 2,082.293 38,253.691 ↓ 26.0 1,483,426 1

Hash (cost=2,040.17..2,040.17 rows=57,147 width=241) (actual time=38,253.691..38,253.691 rows=1,483,426 loops=1)

11. 10,995.223 36,171.398 ↓ 26.0 1,485,212 1

HashAggregate (cost=0.00..2,040.17 rows=57,147 width=241) (actual time=31,342.681..36,171.398 rows=1,485,212 loops=1)

  • Group Key: cdp_dict_1."MACROREGION_NAME", cdp_dict_1."REGION_NAME", cdp_main_old_1."YEAR_MONTH
  • Extra Text: (seg2) 1485212 groups total in 32 batches; 1 overflows; 1485212 spill groups.
12. 9,716.203 25,176.175 ↓ 26.0 1,485,212 1

Redistribute Motion 4:4 (slice5; segments: 4) (cost=0.00..2,007.24 rows=57,147 width=241) (actual time=12,595.776..25,176.175 rows=1,485,212 loops=1)

  • Hash Key: cdp_dict_1."MACROREGION_NAME", cdp_dict_1."REGION_NAME", cdp_main_old_1."YEAR_MONTH
13. 1,221.056 15,459.972 ↓ 26.0 1,486,070 1

Hash Join (cost=0.00..1,964.14 rows=57,147 width=241) (actual time=12,618.412..15,459.972 rows=1,486,070 loops=1)

  • Hash Cond: (cdp_main_old_1."REPORT_MONTH" = cdp_main_old_2."REPORT_MONTH")
  • Extra Text: (seg1) Hash chain length 1.1 avg, 3 max, using 6901 of 65536 buckets.1486070 groups total in 32 batches; 1 overflows; 1486070 spill groups.
14. 4,360.415 12,161.390 ↓ 26.0 1,486,070 1

HashAggregate (cost=0.00..1,004.02 rows=57,147 width=221) (actual time=10,543.483..12,161.390 rows=1,486,070 loops=1)

  • Group Key: cdp_dict_1."MACROREGION_NAME", cdp_dict_1."REGION_NAME", cdp_main_old_1."REPORT_MONTH", cdp_main_old_1."YEAR_MONTH
  • Extra Text: (seg1) 1486070 groups total in 32 batches; 1 overflows; 1486070 spill groups.
15. 6,994.687 7,800.975 ↓ 26.0 1,486,070 1

Redistribute Motion 4:4 (slice2; segments: 4) (cost=0.00..963.85 rows=57,147 width=221) (actual time=0.024..7,800.975 rows=1,486,070 loops=1)

  • Hash Key: cdp_dict_1."MACROREGION_NAME", cdp_dict_1."REGION_NAME", cdp_main_old_1."REPORT_MONTH", cdp_main_old_1."YEAR_MONTH
16. 779.667 806.288 ↓ 26.3 1,501,130 1

Hash Left Join (cost=0.00..924.32 rows=57,147 width=221) (actual time=3.260..806.288 rows=1,501,130 loops=1)

  • Hash Cond: (cdp_main_old_1."BRANCH_ID" = cdp_dict_1."BRANCH_ID")
  • Extra Text: (seg1) Hash chain length 11.0 avg, 166 max, using 123 of 8192 buckets.Hash chain length 3.7 avg, 10 max, using 505 of 512 buckets; total 4 expansions.
17. 24.386 24.386 ↓ 1.0 25,209 1

Seq Scan on cdp_main_old cdp_main_old_1 (cost=0.00..435.47 rows=25,000 width=24) (actual time=0.879..24.386 rows=25,209 loops=1)

18. 0.903 2.235 ↑ 1.0 1,356 1

Hash (cost=431.79..431.79 rows=1,356 width=207) (actual time=2.235..2.235 rows=1,356 loops=1)

19. 1.332 1.332 ↑ 1.0 1,356 1

Seq Scan on cdp_dict cdp_dict_1 (cost=0.00..431.79 rows=1,356 width=207) (actual time=0.041..1.332 rows=1,356 loops=1)

20. 2.586 2,077.526 ↓ 1.0 7,306 1

Hash (cost=888.95..888.95 rows=7,260 width=28) (actual time=2,077.526..2,077.526 rows=7,306 loops=1)

21. 9.875 2,074.940 ↓ 1.0 7,306 1

Broadcast Motion 4:4 (slice4; segments: 4) (cost=0.00..888.95 rows=7,260 width=28) (actual time=2,067.440..2,074.940 rows=7,306 loops=1)

22. 0.429 2,065.065 ↓ 1.0 1,867 1

Result (cost=0.00..886.15 rows=1,815 width=28) (actual time=2,064.252..2,065.065 rows=1,867 loops=1)

23. 4.790 2,064.636 ↓ 1.0 1,867 1

HashAggregate (cost=0.00..886.15 rows=1,815 width=28) (actual time=2,064.247..2,064.636 rows=1,867 loops=1)

  • Group Key: cdp_main_old_2."REPORT_MONTH
  • Extra Text: (seg1) Hash chain length 3.7 avg, 10 max, using 505 of 512 buckets; total 4 expansions.
24. 46.023 2,059.846 ↓ 3.9 6,995 1

Redistribute Motion 4:4 (slice3; segments: 4) (cost=0.00..885.90 rows=1,815 width=28) (actual time=1,995.659..2,059.846 rows=6,995 loops=1)

  • Hash Key: cdp_main_old_2."REPORT_MONTH
25. 1.154 2,013.823 ↓ 3.8 6,851 1

Result (cost=0.00..885.74 rows=1,815 width=28) (actual time=2,010.704..2,013.823 rows=6,851 loops=1)

26. 1,460.748 2,012.669 ↓ 3.8 6,851 1

HashAggregate (cost=0.00..885.74 rows=1,815 width=28) (actual time=2,010.687..2,012.669 rows=6,851 loops=1)

  • Group Key: cdp_main_old_2."REPORT_MONTH
  • Extra Text: (seg1) Hash chain length 3.5 avg, 13 max, using 1976 of 2048 buckets; total 6 expansions.
27. 539.008 551.921 ↓ 26.3 1,501,130 1

Hash Left Join (cost=0.00..878.24 rows=57,147 width=23) (actual time=3.205..551.921 rows=1,501,130 loops=1)

  • Hash Cond: (cdp_main_old_2."BRANCH_ID" = cdp_dict_2."BRANCH_ID")
  • Extra Text: (seg1) Hash chain length 11.0 avg, 166 max, using 123 of 65536 buckets.
28. 11.503 11.503 ↓ 1.0 25,209 1

Seq Scan on cdp_main_old cdp_main_old_2 (cost=0.00..435.47 rows=25,000 width=28) (actual time=1.242..11.503 rows=25,209 loops=1)

29. 0.485 1.410 ↑ 1.0 1,356 1

Hash (cost=431.79..431.79 rows=1,356 width=5) (actual time=1.410..1.410 rows=1,356 loops=1)

30. 0.925 0.925 ↑ 1.0 1,356 1

Seq Scan on cdp_dict cdp_dict_2 (cost=0.00..431.79 rows=1,356 width=5) (actual time=0.031..0.925 rows=1,356 loops=1)

Planning time : 202.664 ms
Execution time : 259,833.604 ms