explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T7ah : Query Plan

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Gather Motion 120:1 (slice7; segments: 120) (cost=0.00..295,886.92 rows=236,164,016 width=423) (actual rows= loops=)

  • Rows out: 14417373 rows at destination with 16523 ms to first row, 22149 ms to end.
  • (slice0) Executor memory: 1420K bytes.
  • (slice1) Executor memory: 2181K bytes avg x 120 workers, 2181K bytes max (seg0).
  • (slice2) Executor memory: 2181K bytes avg x 120 workers, 2181K bytes max (seg0).
  • (slice3) Executor memory: 297390K bytes avg x 120 workers, 297394K bytes max (seg0). Work_mem: 208467K bytes max.
  • (slice4) Executor memory: 182189K bytes avg x 120 workers, 182403K bytes max (seg77). Work_mem: 123766K bytes max.
  • (slice5) Executor memory: 59538K bytes avg x 120 workers, 59894K bytes max (seg9). Work_mem: 21864K bytes max.
  • (slice6) Executor memory: 157447K bytes avg x 120 workers, 160196K bytes max (seg34). Work_mem: 73894K bytes max.
  • (slice7) Executor memory: 33334K bytes avg x 120 workers, 33575K bytes max (seg40).
  • Memory used: 2752512K bytes
2. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.00..72,299.93 rows=1,968,034 width=423) (actual rows= loops=)

  • Group By: cnsmpn_cntct_dim.entity_id, cnsmpn_rsdnc_dim.rsdnc_entity_id, cnsmpn_cntct_dim.frst_nm, cnsmpn_cntct_dim.mdl_nm, cnsmpn_cntct_dim.last_nm, cnsmpn_cntct_dim.ctry_cd, cnsmpn_site_dim.addr_ln_1, cnsmpn_site_dim.addr_ln_2, cnsmpn_site_dim.city_nm, cnsmpn_site_dim.st_cd, cnsmpn_site_dim.pstl_cd, cnsmpn_site_dim.zip_plus_4_cd, cnsmpn_cntct_dim.best_ph_nbr, cnsmpn_cntct_dim.prim_email_addr, "outer".development_flag, "outer".reactivation_flag, "outer".gaming_purchase_flag, "outer".pc_purchase_flag, "outer".ena_purchase_flag, "outer".mpp_purchase_flag, "outer".outlet_purchase_flag, "outer".offline_only_purchase_flag, "outer".high_gaming_propensity, "outer".med_gaming_propensity, "outer".low_gaming_propensity, "outer".high_dell_propensity, "outer".med_dell_propensity, "outer".low_dell_propensity, "outer".high_value_household, "outer".med_value_household, "outer".low_value_household, "outer".young_upcoming_flag, "outer".affluent_digital_family_flag, "outer".heartlanders_flag, "outer".empty_nesters_flag, "outer".soho_flag, "outer".savvy_suburbia_flag, "outer".best_iid_for_rid_flag, "outer".dell_advantage_flag, "outer".dpa_flag, "outer".dormant_flag, "outer".inquirer_flag, (md5((((((((((((((((((((((((((COALESCE(btrim(cnsmpn_cntct_dim.entity_id::text), '-1'::text) || '~'::text) || COALESCE(btrim(cnsmpn_rsdnc_dim.rsdnc_entity_id::text), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_cntct_dim.frst_nm), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_cntct_dim.mdl_nm), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_cntct_dim.last_nm), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_cntct_dim.ctry_cd::text), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_site_dim.addr_ln_1::text), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_site_dim.addr_ln_2::text), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_site_dim.city_nm), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_site_dim.st_cd::text), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_site_dim.pstl_cd::text), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_site_dim.zip_plus_4_cd::text), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_cntct_dim.best_ph_nbr::text), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_cntct_dim.prim_email_addr::text), 'Not Applicable'::text))), "outer".dw_ins_dtsz, "outer".dw_upd_dtsz
  • Rows out: Avg 120144.8 rows x 120 workers. Max 120969 rows (seg103) with 16529 ms to first row, 16604 ms to end.
  • Executor memory: 32926K bytes avg, 33180K bytes max (seg40).
  • (seg103) Hash chain length 3.8 avg, 13 max, using 31953 of 32768 buckets; total 10 expansions.
3. 0.000 0.000 ↓ 0.0

Redistribute Motion 120:120 (slice6; segments: 120) (cost=0.00..57,009.84 rows=1,968,034 width=423) (actual rows= loops=)

  • Hash Key: cnsmpn_cntct_dim.entity_id, cnsmpn_rsdnc_dim.rsdnc_entity_id, cnsmpn_cntct_dim.frst_nm, cnsmpn_cntct_dim.mdl_nm, cnsmpn_cntct_dim.last_nm, cnsmpn_cntct_dim.ctry_cd, cnsmpn_site_dim.addr_ln_1, cnsmpn_site_dim.addr_ln_2, cnsmpn_site_dim.city_nm, cnsmpn_site_dim.st_cd, cnsmpn_site_dim.pstl_cd, cnsmpn_site_dim.zip_plus_4_cd, cnsmpn_cntct_dim.best_ph_nbr, cnsmpn_cntct_dim.prim_email_addr, "outer".development_flag, "outer".reactivation_flag, "outer".gaming_purchase_flag, "outer".pc_purchase_flag, "outer".ena_purchase_flag, "outer".mpp_purchase_flag, "outer".outlet_purchase_flag, "outer".offline_only_purchase_flag, "outer".high_gaming_propensity, "outer".med_gaming_propensity, "outer".low_gaming_propensity, "outer".high_dell_propensity, "outer".med_dell_propensity, "outer".low_dell_propensity, "outer".high_value_household, "outer".med_value_household, "outer".low_value_household, "outer".young_upcoming_flag, "outer".affluent_digital_family_flag, "outer".heartlanders_flag, "outer".empty_nesters_flag, "outer".soho_flag, "outer".savvy_suburbia_flag, "outer".best_iid_for_rid_flag, "outer".dell_advantage_flag, "outer".dpa_flag, "outer".dormant_flag, "outer".inquirer_flag, (md5((((((((((((((((((((((((((COALESCE(btrim(cnsmpn_cntct_dim.entity_id::text), '-1'::text) || '~'::text) || COALESCE(btrim(cnsmpn_rsdnc_dim.rsdnc_entity_id::text), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_cntct_dim.frst_nm), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_cntct_dim.mdl_nm), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_cntct_dim.last_nm), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_cntct_dim.ctry_cd::text), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_site_dim.addr_ln_1::text), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_site_dim.addr_ln_2::text), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_site_dim.city_nm), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_site_dim.st_cd::text), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_site_dim.pstl_cd::text), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_site_dim.zip_plus_4_cd::text), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_cntct_dim.best_ph_nbr::text), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_cntct_dim.prim_email_addr::text), 'Not Applicable'::text))), "outer".dw_ins_dtsz, "outer".dw_upd_dtsz
  • Rows out: Avg 120144.8 rows x 120 workers at destination. Max 120969 rows (seg103) with 15988 ms to first row, 16207 ms to end.
4. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.00..54,404.19 rows=1,968,034 width=423) (actual rows= loops=)

  • Group By: cnsmpn_cntct_dim.entity_id, cnsmpn_rsdnc_dim.rsdnc_entity_id, cnsmpn_cntct_dim.frst_nm, cnsmpn_cntct_dim.mdl_nm, cnsmpn_cntct_dim.last_nm, cnsmpn_cntct_dim.ctry_cd, cnsmpn_site_dim.addr_ln_1, cnsmpn_site_dim.addr_ln_2, cnsmpn_site_dim.city_nm, cnsmpn_site_dim.st_cd, cnsmpn_site_dim.pstl_cd, cnsmpn_site_dim.zip_plus_4_cd, cnsmpn_cntct_dim.best_ph_nbr, cnsmpn_cntct_dim.prim_email_addr, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, NULL::text, md5((((((((((((((((((((((((((COALESCE(btrim(cnsmpn_cntct_dim.entity_id::text), '-1'::text) || '~'::text) || COALESCE(btrim(cnsmpn_rsdnc_dim.rsdnc_entity_id::text), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_cntct_dim.frst_nm), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_cntct_dim.mdl_nm), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_cntct_dim.last_nm), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_cntct_dim.ctry_cd::text), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_site_dim.addr_ln_1::text), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_site_dim.addr_ln_2::text), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_site_dim.city_nm), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_site_dim.st_cd::text), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_site_dim.pstl_cd::text), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_site_dim.zip_plus_4_cd::text), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_cntct_dim.best_ph_nbr::text), 'Not Applicable'::text)) || '~'::text) || COALESCE(btrim(cnsmpn_cntct_dim.prim_email_addr::text), 'Not Applicable'::text)), '2019-04-12 10:12:07.520547+00'::timestamp with time zone, '2019-04-12 10:12:07.520547+00'::timestamp with time zone
  • Rows out: Avg 120144.8 rows x 120 workers. Max 131793 rows (seg34) with 15984 ms to first row, 16111 ms to end.
  • Executor memory: 32930K bytes avg, 35678K bytes max (seg34).
  • (seg34) Hash chain length 4.1 avg, 16 max, using 32208 of 32768 buckets; total 10 expansions.
5. 0.000 0.000 ↓ 0.0

Result (cost=0.00..39,114.10 rows=1,968,034 width=423) (actual rows= loops=)

  • Rows out: Avg 126579.8 rows x 120 workers. Max 146710 rows (seg46) with 12316 ms to first row, 15576 ms to end.
6. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.00..38,281.62 rows=1,968,034 width=175) (actual rows= loops=)

  • Hash Cond: cnsmpn_cntct_rsdnc_xwalk.rsdnc_entity_id::text = cnsmpn_rsdnc_dim.rsdnc_entity_id::text
  • Rows out: Avg 126579.8 rows x 120 workers. Max 146710 rows (seg46) with 12316 ms to first row, 14469 ms to end.
  • Executor memory: 73704K bytes avg, 73894K bytes max (seg109).
  • Work_mem used: 73704K bytes avg, 73894K bytes max (seg109). Workfile: (0 spilling)
  • (seg46) Hash chain length 1.8 avg, 9 max, using 758907 of 1048576 buckets.
7. 0.000 0.000 ↓ 0.0

Redistribute Motion 120:120 (slice5; segments: 120) (cost=0.00..34,682.67 rows=1,968,034 width=175) (actual rows= loops=)

  • Hash Key: cnsmpn_cntct_rsdnc_xwalk.rsdnc_entity_id
  • Rows out: Avg 126579.8 rows x 120 workers at destination. Max 146710 rows (seg46) with 11500 ms to first row, 13351 ms to end.
8. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.00..33,604.68 rows=1,968,034 width=175) (actual rows= loops=)

  • Hash Cond: cnsmpn_cntct_rsdnc_xwalk.entity_id::text = cnsmpn_cntct_dim.entity_id::text
  • Rows out: Avg 126579.8 rows x 120 workers. Max 129208 rows (seg9) with 12464 ms to first row, 15649 ms to end.
  • Executor memory: 21394K bytes avg, 21864K bytes max (seg119).
  • Work_mem used: 21394K bytes avg, 21864K bytes max (seg119). Workfile: (0 spilling)
  • (seg9) Hash chain length 1.2 avg, 2693 max, using 106798 of 1048576 buckets.
9. 0.000 0.000 ↓ 0.0

Redistribute Motion 120:120 (slice1; segments: 120) (cost=0.00..2,338.07 rows=4,115,479 width=76) (actual rows= loops=)

  • Hash Key: cnsmpn_cntct_rsdnc_xwalk.entity_id::text
  • Rows out: Avg 4115369.9 rows x 120 workers at destination. Max 4122228 rows (seg10) with 218 ms to first row, 1974 ms to end.
10. 0.000 0.000 ↓ 0.0

Table Scan on cnsmpn_cntct_rsdnc_xwalk (cost=0.00..777.32 rows=4,115,479 width=76) (actual rows= loops=)

  • Rows out: Avg 4115369.9 rows x 120 workers. Max 4119680 rows (seg59) with 0.078 ms to first row, 932 ms to end.
11. 0.000 0.000 ↓ 0.0

Hash (cost=19,538.58..19,538.58 rows=1,968,034 width=142) (actual rows= loops=)

  • Rows in: Avg 126579.8 rows x 120 workers. Max 129208 rows (seg9) with 12285 ms to end, start offset by 142 ms.
12. 0.000 0.000 ↓ 0.0

Redistribute Motion 120:120 (slice4; segments: 120) (cost=0.00..19,538.58 rows=1,968,034 width=142) (actual rows= loops=)

  • Hash Key: cnsmpn_cntct_dim.entity_id::text
  • Rows out: Avg 126579.8 rows x 120 workers at destination. Max 129208 rows (seg9) with 4795 ms to first row, 12221 ms to end.
13. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.00..18,663.86 rows=1,968,034 width=142) (actual rows= loops=)

  • Hash Cond: cnsmpn_cntct_to_site_xwalk.site_entity_id::text = cnsmpn_site_dim.entity_id::text
  • Rows out: Avg 126579.8 rows x 120 workers. Max 163377 rows (seg77) with 4141 ms to first row, 12087 ms to end.
  • Executor memory: 123492K bytes avg, 123766K bytes max (seg36).
  • Work_mem used: 123492K bytes avg, 123766K bytes max (seg36). Workfile: (0 spilling)
  • (seg77) Hash chain length 1.6 avg, 9 max, using 658108 of 1048576 buckets.
14. 0.000 0.000 ↓ 0.0

Redistribute Motion 120:120 (slice3; segments: 120) (cost=0.00..12,159.38 rows=1,968,034 width=133) (actual rows= loops=)

  • Hash Key: cnsmpn_cntct_to_site_xwalk.site_entity_id
  • Rows out: Avg 126579.8 rows x 120 workers at destination. Max 163377 rows (seg77) with 3058 ms to first row, 10598 ms to end.
15. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.00..11,340.11 rows=1,968,034 width=133) (actual rows= loops=)

  • Hash Cond: cnsmpn_cntct_dim.entity_id::text = cnsmpn_cntct_to_site_xwalk.cntct_entity_id::text
  • Rows out: Avg 126579.8 rows x 120 workers. Max 129208 rows (seg9) with 2953 ms to first row, 10810 ms to end.
  • Executor memory: 207600K bytes avg, 208467K bytes max (seg59).
  • Work_mem used: 207600K bytes avg, 208467K bytes max (seg59). Workfile: (0 spilling)
  • (seg9) Hash chain length 2.4 avg, 2693 max, using 824229 of 1048576 buckets.
16. 0.000 0.000 ↓ 0.0

Table Scan on cnsmpn_cntct_dim (cost=0.00..3,046.38 rows=1,721,405 width=93) (actual rows= loops=)

  • Filter: btrim(lower(perspective_id::text)) = 'consumer'::text AND btrim(upper(ctry_cd::text)) = 'US'::text
  • Rows out: Avg 3440426.3 rows x 120 workers. Max 3445934 rows (seg10) with 169 ms to first row, 6842 ms to end.
17. 0.000 0.000 ↓ 0.0

Hash (cost=1,428.79..1,428.79 rows=1,968,034 width=80) (actual rows= loops=)

  • Rows in: Avg 1968111.2 rows x 120 workers. Max 1976428 rows (seg59) with 1588 ms to end, start offset by 137 ms.
18. 0.000 0.000 ↓ 0.0

Redistribute Motion 120:120 (slice2; segments: 120) (cost=0.00..1,428.79 rows=1,968,034 width=80) (actual rows= loops=)

  • Hash Key: cnsmpn_cntct_to_site_xwalk.cntct_entity_id::text
  • Rows out: Avg 1968111.2 rows x 120 workers at destination. Max 1976428 rows (seg59) with 19 ms to first row, 833 ms to end.
19. 0.000 0.000 ↓ 0.0

Table Scan on cnsmpn_cntct_to_site_xwalk (cost=0.00..643.15 rows=1,968,034 width=80) (actual rows= loops=)

  • Rows out: Avg 1968111.2 rows x 120 workers. Max 1971239 rows (seg111) with 0.078 ms to first row, 490 ms to end.
20. 0.000 0.000 ↓ 0.0

Hash (cost=740.42..740.42 rows=1,034,165 width=91) (actual rows= loops=)

  • Rows in: Avg 1034164.9 rows x 120 workers. Max 1036436 rows (seg36) with 1128 ms to end, start offset by 141 ms.
21. 0.000 0.000 ↓ 0.0

Table Scan on cnsmpn_site_dim (cost=0.00..740.42 rows=1,034,165 width=91) (actual rows= loops=)

  • Rows out: Avg 1034164.9 rows x 120 workers. Max 1036436 rows (seg36) with 0.285 ms to first row, 468 ms to end.
22. 0.000 0.000 ↓ 0.0

Hash (cost=614.81..614.81 rows=1,347,568 width=33) (actual rows= loops=)

  • Rows in: Avg 1347714.2 rows x 120 workers. Max 1351188 rows (seg109) with 778 ms to end, start offset by 141 ms.
23. 0.000 0.000 ↓ 0.0

Table Scan on cnsmpn_rsdnc_dim (cost=0.00..614.81 rows=1,347,568 width=33) (actual rows= loops=)

  • Rows out: Avg 1347714.2 rows x 120 workers. Max 1351188 rows (seg109) with 0.130 ms to first row, 301 ms to end.