explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Vx1R

Settings
# exclusive inclusive rows x rows loops node
1. 100.730 493,449.722 ↑ 12.9 804 1

Gather Motion 12:1 (slice6; segments: 12) (cost=0.00..119,484.77 rows=10,346 width=721) (actual time=493,444.376..493,449.722 rows=804 loops=1)

  • (slice0) Executor memory: 6377K bytes.
  • (slice1) Executor memory: 6793K bytes avg x 12 workers, 6833K bytes max (seg0). Work_mem: 8K bytes max.
  • (slice2) Executor memory: 1742K bytes avg x 12 workers, 1742K bytes max (seg0). Work_mem: 8K bytes max.
  • (slice3) Executor memory: 3142K bytes avg x 12 workers, 3142K bytes max (seg0). Work_mem: 4K bytes max.
  • (slice4) Executor memory: 130K bytes avg x 12 workers, 130K bytes max (seg0). Work_mem: 65K bytes max.
  • (slice5) Executor memory: 2378K bytes avg x 12 workers, 2378K bytes max (seg0). Work_mem: 1K bytes max.
  • (slice6) Executor memory: 3479K bytes avg x 12 workers, 3557K bytes max (seg10). Work_mem: 8K bytes max.Memory used: 557056kBOptimizer: Pivotal Optimizer (GPORCA) version 3.88.0
2. 6.324 493,348.992 ↑ 11.4 76 1

Hash Join (cost=0.00..119,465.51 rows=863 width=721) (actual time=493,347.673..493,348.992 rows=76 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."YEAR_MONTH")::text = (cdp_main_1."YEAR_MONTH")::text))
  • Extra Text: (seg4) Hash chain length 1.0 avg, 1 max, using 76 of 262144 buckets.Hash chain length 2.5 avg, 7 max, using 30 of 32 buckets; total 0 expansions.
3. 7.665 457,770.390 ↓ 1.1 76 1

HashAggregate (cost=0.00..70,341.10 rows=69 width=697) (actual time=457,769.907..457,770.390 rows=76 loops=1)

  • Group Key: cdp_dict."MACROREGION_NAME", cdp_dict."REGION_NAME", cdp_main."YEAR_MONTH
  • Extra Text: (seg4) Hash chain length 2.5 avg, 7 max, using 30 of 32 buckets; total 0 expansions.
4. 0.000 457,762.725 ↓ 1.2 81 1

Redistribute Motion 12:12 (slice1; segments: 12) (cost=0.00..70,341.04 rows=69 width=697) (actual time=304,322.490..457,762.725 rows=81 loops=1)

  • Hash Key: cdp_dict."MACROREGION_NAME", cdp_dict."REGION_NAME", cdp_main."YEAR_MONTH
5. 0.035 472,748.177 ↓ 1.1 79 1

Result (cost=0.00..70,340.89 rows=69 width=697) (actual time=472,747.484..472,748.177 rows=79 loops=1)

6. 271,461.484 472,748.142 ↓ 1.1 79 1

HashAggregate (cost=0.00..70,340.89 rows=69 width=697) (actual time=472,747.475..472,748.142 rows=79 loops=1)

  • Group Key: cdp_dict."MACROREGION_NAME", cdp_dict."REGION_NAME", cdp_main."YEAR_MONTH
  • Extra Text: (seg0) Hash chain length 2.8 avg, 6 max, using 28 of 32 buckets; total 0 expansions.
7. 161,484.884 201,286.658 ↑ 1.0 33,301,436 1

Result (cost=0.00..42,799.88 rows=34,875,242 width=279) (actual time=111.133..201,286.658 rows=33,301,436 loops=1)

8. 13,651.056 39,801.774 ↑ 1.0 33,301,436 1

Hash Left Join (cost=0.00..33,069.68 rows=34,875,242 width=142) (actual time=111.076..39,801.774 rows=33,301,436 loops=1)

  • Hash Cond: (cdp_main."BRANCH_ID" = cdp_dict."BRANCH_ID")
  • Extra Text: (seg3) Hash chain length 1.0 avg, 1 max, using 113 of 131072 buckets.Hash chain length 2.5 avg, 7 max, using 30 of 32 buckets; total 0 expansions.
9. 26,149.811 26,149.811 ↓ 1.2 33,301,436 1

Seq Scan on cdp_main (cost=0.00..2,865.62 rows=27,324,563 width=114) (actual time=108.929..26,149.811 rows=33,301,436 loops=1)

10. 0.062 0.907 ↑ 1.0 113 1

Hash (cost=431.01..431.01 rows=113 width=38) (actual time=0.907..0.907 rows=113 loops=1)

11. 0.845 0.845 ↑ 1.0 113 1

Seq Scan on cdp_dict (cost=0.00..431.01 rows=113 width=38) (actual time=0.819..0.845 rows=113 loops=1)

12. 0.143 35,572.278 ↓ 1.1 76 1

Hash (cost=49,122.05..49,122.05 rows=69 width=65) (actual time=35,572.278..35,572.278 rows=76 loops=1)

13. 0.768 35,572.135 ↓ 1.1 76 1

HashAggregate (cost=0.00..49,122.05 rows=69 width=65) (actual time=35,572.001..35,572.135 rows=76 loops=1)

  • Group Key: cdp_dict_1."MACROREGION_NAME", cdp_dict_1."REGION_NAME", cdp_main_1."YEAR_MONTH
  • Extra Text: (seg4) Hash chain length 2.5 avg, 7 max, using 30 of 32 buckets; total 0 expansions.
14. 0.000 35,571.367 ↓ 1.1 76 1

Redistribute Motion 12:12 (slice5; segments: 12) (cost=0.00..49,122.02 rows=69 width=65) (actual time=35,568.105..35,571.367 rows=76 loops=1)

  • Hash Key: cdp_dict_1."MACROREGION_NAME", cdp_dict_1."REGION_NAME", cdp_main_1."YEAR_MONTH
15. 0.024 35,615.728 ↓ 1.2 82 1

Result (cost=0.00..49,122.00 rows=69 width=65) (actual time=35,615.560..35,615.728 rows=82 loops=1)

16. 0.444 35,615.704 ↓ 1.2 82 1

HashAggregate (cost=0.00..49,122.00 rows=69 width=65) (actual time=35,615.547..35,615.704 rows=82 loops=1)

  • Group Key: cdp_dict_1."MACROREGION_NAME", cdp_dict_1."REGION_NAME", cdp_main_1."YEAR_MONTH
  • Extra Text: (seg11) Hash chain length 2.6 avg, 6 max, using 31 of 32 buckets; total 0 expansions.
17. 51.017 35,615.260 ↑ 6.1 82 1

Hash Join (cost=0.00..49,121.80 rows=504 width=65) (actual time=35,614.399..35,615.260 rows=82 loops=1)

  • Hash Cond: (cdp_main_1."REPORT_MONTH" = cdp_main_2."REPORT_MONTH")
  • Extra Text: (seg11) Hash chain length 1.0 avg, 1 max, using 13 of 262144 buckets.Hash chain length 2.8 avg, 8 max, using 29 of 32 buckets; total 0 expansions.
18. 0.153 0.204 ↑ 6.1 82 1

HashAggregate (cost=0.00..32,569.25 rows=504 width=45) (actual time=0.156..0.204 rows=82 loops=1)

  • Group Key: cdp_dict_1."MACROREGION_NAME", cdp_dict_1."REGION_NAME", cdp_main_1."REPORT_MONTH", cdp_main_1."YEAR_MONTH
  • Extra Text: (seg11) Hash chain length 2.8 avg, 8 max, using 29 of 32 buckets; total 0 expansions.
19. 0.000 0.051 ↑ 5.9 86 1

Redistribute Motion 12:12 (slice2; segments: 12) (cost=0.00..32,568.99 rows=504 width=45) (actual time=0.015..0.051 rows=86 loops=1)

  • Hash Key: cdp_dict_1."MACROREGION_NAME", cdp_dict_1."REGION_NAME", cdp_main_1."REPORT_MONTH", cdp_main_1."YEAR_MONTH
20. 8,602.683 25,761.357 ↑ 6.4 79 1

HashAggregate (cost=0.00..32,568.92 rows=504 width=45) (actual time=25,761.319..25,761.357 rows=79 loops=1)

  • Group Key: cdp_dict_1."MACROREGION_NAME", cdp_dict_1."REGION_NAME", cdp_main_1."REPORT_MONTH", cdp_main_1."YEAR_MONTH
  • Extra Text: (seg0) Hash chain length 2.6 avg, 5 max, using 30 of 32 buckets; total 0 expansions.
21. 11,487.991 17,158.674 ↑ 1.0 33,301,436 1

Hash Left Join (cost=0.00..14,685.50 rows=34,875,242 width=45) (actual time=20.619..17,158.674 rows=33,301,436 loops=1)

  • Hash Cond: (cdp_main_1."BRANCH_ID" = cdp_dict_1."BRANCH_ID")
  • Extra Text: (seg3) Hash chain length 1.0 avg, 1 max, using 113 of 131072 buckets.Hash chain length 1.2 avg, 2 max, using 11 of 32 buckets; total 0 expansions.
22. 5,665.473 5,665.473 ↓ 1.2 33,301,436 1

Seq Scan on cdp_main cdp_main_1 (cost=0.00..2,865.62 rows=27,324,563 width=17) (actual time=16.844..5,665.473 rows=33,301,436 loops=1)

23. 0.058 5.210 ↑ 1.0 113 1

Hash (cost=431.01..431.01 rows=113 width=38) (actual time=5.210..5.210 rows=113 loops=1)

24. 5.152 5.152 ↑ 1.0 113 1

Seq Scan on cdp_dict cdp_dict_1 (cost=0.00..431.01 rows=113 width=38) (actual time=5.136..5.152 rows=113 loops=1)

25. 0.024 35,564.039 ↑ 1.0 13 1

Hash (cost=16,552.32..16,552.32 rows=13 width=28) (actual time=35,564.039..35,564.039 rows=13 loops=1)

26. 9.230 35,564.015 ↑ 1.0 13 1

Broadcast Motion 12:12 (slice4; segments: 12) (cost=0.00..16,552.32 rows=13 width=28) (actual time=35,561.038..35,564.015 rows=13 loops=1)

27. 0.010 35,554.785 ↓ 2.5 5 1

Result (cost=0.00..16,552.32 rows=2 width=28) (actual time=35,554.763..35,554.785 rows=5 loops=1)

28. 0.051 35,554.775 ↓ 2.5 5 1

GroupAggregate (cost=0.00..16,552.32 rows=2 width=28) (actual time=35,554.754..35,554.775 rows=5 loops=1)

  • Group Key: cdp_main_2."REPORT_MONTH
29. 0.932 35,554.724 ↓ 30.0 60 1

Sort (cost=0.00..16,552.32 rows=2 width=28) (actual time=35,554.719..35,554.724 rows=60 loops=1)

  • Sort Key: cdp_main_2."REPORT_MONTH
  • Sort Method: quicksort Memory: 412kB
30. 2,909.717 35,553.792 ↓ 30.0 60 1

Redistribute Motion 12:12 (slice3; segments: 12) (cost=0.00..16,552.32 rows=2 width=28) (actual time=24,489.643..35,553.792 rows=60 loops=1)

  • Hash Key: cdp_main_2."REPORT_MONTH
31. 0.009 32,644.075 ↓ 6.5 13 1

Result (cost=0.00..16,552.32 rows=2 width=28) (actual time=32,644.048..32,644.075 rows=13 loops=1)

32. 16,105.904 32,644.066 ↓ 6.5 13 1

HashAggregate (cost=0.00..16,552.32 rows=2 width=28) (actual time=32,644.040..32,644.066 rows=13 loops=1)

  • Group Key: cdp_main_2."REPORT_MONTH
  • Extra Text: (seg0) Hash chain length 1.2 avg, 2 max, using 11 of 32 buckets; total 0 expansions.
33. 10,091.483 16,538.162 ↑ 1.0 33,301,436 1

Hash Left Join (cost=0.00..11,984.01 rows=34,875,242 width=19) (actual time=24.029..16,538.162 rows=33,301,436 loops=1)

  • Hash Cond: (cdp_main_2."BRANCH_ID" = cdp_dict_2."BRANCH_ID")
  • Extra Text: (seg3) Hash chain length 1.0 avg, 1 max, using 113 of 262144 buckets.
34. 6,441.664 6,441.664 ↓ 1.2 33,301,436 1

Seq Scan on cdp_main cdp_main_2 (cost=0.00..2,865.62 rows=27,324,563 width=24) (actual time=22.330..6,441.664 rows=33,301,436 loops=1)

35. 0.048 5.015 ↑ 1.0 113 1

Hash (cost=431.01..431.01 rows=113 width=5) (actual time=5.015..5.015 rows=113 loops=1)

36. 4.967 4.967 ↑ 1.0 113 1

Seq Scan on cdp_dict cdp_dict_2 (cost=0.00..431.01 rows=113 width=5) (actual time=4.957..4.967 rows=113 loops=1)

Planning time : 285.855 ms
Execution time : 493,719.544 ms