explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hL69

Settings
# exclusive inclusive rows x rows loops node
1. 34,447.347 254,706.499 ↑ 25.5 5,929,968 1

Hash Join (cost=0.00..1,995,946.72 rows=150,965,035 width=897) (actual time=128,313.859..254,706.499 rows=5,929,968 loops=1)

  • Hash Cond: (((v_cdp_m."MACROREGION_NAME")::text = (v_cdp_m_1."MACROREGION_NAME")::text) AND ((v_cdp_m."REGION_NAME")::text = (v_cdp_m_1."REGION_NAME")::text) AND ((v_cdp_m."YEAR_MONTH")::text = (v_cdp_m_1."YEAR_MONTH")::text))
  • Extra Text: Initial batch 0:
  • Wrote 1527378K bytes to inner workfile.
  • Wrote 4912400K bytes to outer workfile.
  • Initial batches 1..63:
  • Read 1527378K bytes from inner workfile: 24,245K avg x 63 nonempty batches, 24412K max.
  • Read 4912400K bytes from outer workfile: 77,975K avg x 63 nonempty batches, 78515K max.
  • Hash chain length 1.9 avg, 11 max, using 3172976 of 4194304 buckets.
  • (seg2) Hash chain length 3.0 avg, 14 max, using 619145 of 656384 buckets; total 37 expansions.
  • (seg2) Hash chain length 3.1 avg, 15 max, using 509509 of 540672 buckets; total 9 expansions.
  • (seg1) Hash chain length 3.1 avg, 15 max, using 557879 of 589824 buckets; total 11 expansions.
  • * (slice0) Executor memory: 61,590K bytes. Work_mem: 25,505K bytes max, 1621140K bytes wanted.
  • (slice1) Executor memory: 6,481K bytes avg x 4 workers, 6481K bytes max (seg0).
  • * (slice2) Executor memory: 139,756K bytes avg x 4 workers, 139778K bytes max (seg0). Work_mem: 31,831K bytes max, 3835733K bytes wanted.
  • (slice3) Executor memory: 702K bytes avg x 4 workers, 702K bytes max (seg0).
  • (slice4) Executor memory: 1,567K bytes avg x 4 workers, 1567K bytes max (seg0).
  • (slice5) Executor memory: 354K bytes avg x 4 workers, 354K bytes max (seg0).
  • * (slice6) Executor memory: 65,456K bytes avg x 4 workers, 65456K bytes max (seg0). Work_mem: 62,573K bytes max, 374017K bytes wanted.
  • * (slice7) Executor memory: 57,778K bytes avg x 4 workers, 57778K bytes max (seg0). Work_mem: 31,926K bytes max, 583745K bytes wanted.
  • Memory used: 128,000kB
  • Memory wanted: 26,851,726kB
  • Optimizer: Pivotal Optimizer (GPORCA) version 3.88.0
2. 0.000 111,109.401 ↑ 1.0 5,937,301 1

Gather Motion 4:1 (slice2; segments: 4) (cost=0.00..23,555.91 rows=5,937,301 width=873) (actual time=19,163.742..111,109.401 rows=5,937,301 loops=1)

3. 675.722 204,208.345 ↓ 1.0 1,485,212 1

Result (cost=0.00..6,217.90 rows=1,484,326 width=873) (actual time=128,371.808..204,208.345 rows=1,485,212 loops=1)

4. 134,417.592 203,532.623 ↓ 1.0 1,485,212 1

HashAggregate (cost=0.00..6,217.90 rows=1,484,326 width=873) (actual time=128,371.784..203,532.623 rows=1,485,212 loops=1)

  • Group Key: v_cdp_m."MACROREGION_NAME", v_cdp_m."REGION_NAME", v_cdp_m."YEAR_MONTH
  • Extra Text: (seg2) 1485212 groups total in 160 batches; 33 overflows; 2970424 spill groups.
5. 62,746.587 69,115.031 ↓ 1.0 1,485,212 1

Redistribute Motion 4:4 (slice1; segments: 4) (cost=0.00..4,521.19 rows=1,484,326 width=401) (actual time=56.279..69,115.031 rows=1,485,212 loops=1)

  • Hash Key: v_cdp_m."MACROREGION_NAME", v_cdp_m."REGION_NAME", v_cdp_m."YEAR_MONTH
6. 6,368.444 6,368.444 ↓ 1.0 1,484,898 1

Seq Scan on v_cdp_m (cost=0.00..1,551.07 rows=1,484,326 width=401) (actual time=26.954..6,368.444 rows=1,484,898 loops=1)

7. 8,157.015 109,149.751 ↓ 4.0 5,929,968 1

Hash (cost=14,519.91..14,519.91 rows=1,484,326 width=241) (actual time=109,149.751..109,149.751 rows=5,929,968 loops=1)

  • Buckets: 65,536 Batches: 64 Memory Usage: 25,505kB
8. 14,478.331 100,992.736 ↑ 1.0 5,937,301 1

Gather Motion 4:1 (slice7; segments: 4) (cost=0.00..14,519.91 rows=5,937,301 width=241) (actual time=73,464.549..100,992.736 rows=5,937,301 loops=1)

9. 303.900 86,514.405 ↓ 1.0 1,485,212 1

Result (cost=0.00..9,733.58 rows=1,484,326 width=241) (actual time=73,774.192..86,514.405 rows=1,485,212 loops=1)

10. 21,131.708 86,210.505 ↓ 1.0 1,485,212 1

HashAggregate (cost=0.00..9,733.58 rows=1,484,326 width=241) (actual time=73,774.169..86,210.505 rows=1,485,212 loops=1)

  • Group Key: v_cdp_m_1."MACROREGION_NAME", v_cdp_m_1."REGION_NAME", v_cdp_m_1."YEAR_MONTH
  • Extra Text: (seg2) 1485212 groups total in 32 batches; 1 overflows; 1485212 spill groups.
11. 26,220.817 65,078.797 ↓ 1.0 1,485,212 1

Redistribute Motion 4:4 (slice6; segments: 4) (cost=0.00..8,878.35 rows=1,484,326 width=241) (actual time=30,618.846..65,078.797 rows=1,485,212 loops=1)

  • Hash Key: v_cdp_m_1."MACROREGION_NAME", v_cdp_m_1."REGION_NAME", v_cdp_m_1."YEAR_MONTH
12. 1,683.816 38,857.980 ↓ 1.0 1,486,070 1

Hash Join (cost=0.00..7,758.68 rows=1,484,326 width=241) (actual time=30,749.227..38,857.980 rows=1,486,070 loops=1)

  • Hash Cond: (v_cdp_m_1."REPORT_MONTH" = v_cdp_m_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.
13. 9,723.159 33,605.081 ↓ 1.0 1,486,070 1

HashAggregate (cost=0.00..4,231.44 rows=1,484,326 width=221) (actual time=27,176.423..33,605.081 rows=1,486,070 loops=1)

  • Group Key: v_cdp_m_1."MACROREGION_NAME", v_cdp_m_1."REGION_NAME", v_cdp_m_1."REPORT_MONTH", v_cdp_m_1."YEAR_MONTH
  • Extra Text: (seg1) 1486070 groups total in 32 batches; 1 overflows; 1486070 spill groups.
14. 22,410.085 23,881.922 ↓ 1.0 1,486,070 1

Redistribute Motion 4:4 (slice3; segments: 4) (cost=0.00..3,187.97 rows=1,484,326 width=221) (actual time=0.104..23,881.922 rows=1,486,070 loops=1)

  • Hash Key: v_cdp_m_1."MACROREGION_NAME", v_cdp_m_1."REGION_NAME", v_cdp_m_1."REPORT_MONTH", v_cdp_m_1."YEAR_MONTH
15. 1,471.837 1,471.837 ↓ 1.0 1,484,898 1

Seq Scan on v_cdp_m v_cdp_m_1 (cost=0.00..1,551.07 rows=1,484,326 width=221) (actual time=0.734..1,471.837 rows=1,484,898 loops=1)

16. 2.514 3,569.083 ↓ 1.0 7,306 1

Hash (cost=1,812.19..1,812.19 rows=7,176 width=28) (actual time=3,569.083..3,569.083 rows=7,306 loops=1)

17. 14.510 3,566.569 ↓ 1.0 7,306 1

Broadcast Motion 4:4 (slice5; segments: 4) (cost=0.00..1,812.19 rows=7,176 width=28) (actual time=3,551.086..3,566.569 rows=7,306 loops=1)

18. 0.275 3,552.059 ↓ 1.0 1,867 1

Result (cost=0.00..1,809.42 rows=1,794 width=28) (actual time=3,551.320..3,552.059 rows=1,867 loops=1)

19. 4.649 3,551.784 ↓ 1.0 1,867 1

HashAggregate (cost=0.00..1,809.42 rows=1,794 width=28) (actual time=3,551.316..3,551.784 rows=1,867 loops=1)

  • Group Key: v_cdp_m_2."REPORT_MONTH
  • Extra Text: (seg1) Hash chain length 3.7 avg, 10 max, using 505 of 512 buckets; total 4 expansions.
20. 639.625 3,547.135 ↓ 4.2 7,468 1

Redistribute Motion 4:4 (slice4; segments: 4) (cost=0.00..1,809.17 rows=1,794 width=28) (actual time=2,863.560..3,547.135 rows=7,468 loops=1)

  • Hash Key: v_cdp_m_2."REPORT_MONTH
21. 1.061 2,907.510 ↓ 4.1 7,306 1

Result (cost=0.00..1,809.02 rows=1,794 width=28) (actual time=2,902.149..2,907.510 rows=7,306 loops=1)

22. 2,150.881 2,906.449 ↓ 4.1 7,306 1

HashAggregate (cost=0.00..1,809.02 rows=1,794 width=28) (actual time=2,902.139..2,906.449 rows=7,306 loops=1)

  • Group Key: v_cdp_m_2."REPORT_MONTH
  • Extra Text: (seg0) Hash chain length 3.7 avg, 13 max, using 1993 of 2048 buckets; total 6 expansions.
23. 755.568 755.568 ↓ 1.0 1,484,898 1

Seq Scan on v_cdp_m v_cdp_m_2 (cost=0.00..1,551.07 rows=1,484,326 width=23) (actual time=0.953..755.568 rows=1,484,898 loops=1)

Planning time : 239.058 ms
Execution time : 255,169.499 ms