explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HcYM : Optimization for: Optimization for: plan #YuwT; plan #ScqI

Settings

Optimization path:

Optimization(s) for this plan:

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

XN Hash Join DS_DIST_BOTH (cost=9,213,914,121,738.12..9,213,953,532,336.81 rows=15 width=264) (actual rows= loops=)

  • Outer Dist Key: units.ship_date
  • Inner Dist Key: contacts_agg.cal_day_date
  • Hash Cond: (("outer".ship_date = "inner".cal_day_date) AND ("outer".marketplace_id = ("inner".marketplace_skey)::numeric))
2. 0.000 0.000 ↓ 0.0

XN Subquery Scan units (cost=1,398,456,045,680.32..1,398,456,045,729.90 rows=2,833 width=63) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

XN HashAggregate (cost=1,398,456,045,680.32..1,398,456,045,701.57 rows=2,833 width=23) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

XN Hash Join DS_DIST_BOTH (cost=487,874.56..1,398,456,044,778.05 rows=120,302 width=23) (actual rows= loops=)

  • Outer Dist Key: fsun.ordering_customer_id
  • Inner Dist Key: dim_ab_customers.amazon_account_skey
  • Hash Cond: (("outer".ordering_customer_id = "inner".amazon_account_skey) AND ("outer".marketplace_id = ("inner".marketplace_id)::numeric))
  • Join Filter: ((trunc(("outer".ship_day)::timestamp without time zone) <= trunc("inner".effective_end_date)) AND (trunc(("outer".ship_day)::timestamp without time zone) >= trunc("inner".effective_start_date)))
5. 0.000 0.000 ↓ 0.0

XN Seq Scan on d_ab_unified_cust_ship_items fsun (cost=0.00..14,319,625.60 rows=190,928,342 width=36) (actual rows= loops=)

  • Filter: (('2019-06-01'::date < trunc((ship_day)::timestamp without time zone)) AND ('2019-06-01'::date <= trunc((ship_day)::timestamp without time zone)))
6. 0.000 0.000 ↓ 0.0

XN Hash (cost=425,522.97..425,522.97 rows=12,470,318 width=33) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

XN Seq Scan on dim_ab_customers (cost=0.00..425,522.97 rows=12,470,318 width=33) (actual rows= loops=)

  • Filter: (((is_active_now)::text = 'Y'::text) AND ((is_business_account_active)::text = 'Y'::text) AND (is_internal_business_account = 0))
8. 0.000 0.000 ↓ 0.0

XN Hash (cost=7,815,458,076,056.80..7,815,458,076,056.80 rows=201 width=232) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

XN Subquery Scan contacts_agg (cost=7,815,458,076,051.27..7,815,458,076,056.80 rows=201 width=232) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

XN HashAggregate (cost=7,815,458,076,051.27..7,815,458,076,054.79 rows=201 width=176) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

XN Subquery Scan contacts_raw (cost=7,815,137,046,199.78..7,815,457,389,601.11 rows=30,508,896 width=176) (actual rows= loops=)

  • Filter: (rn = 1)
12. 0.000 0.000 ↓ 0.0

XN Window (cost=7,815,137,046,199.78..7,815,381,117,362.70 rows=6,101,779,073 width=166) (actual rows= loops=)

  • Partition: dim_ab_customers.amazon_account_skey, dim_ab_customers.marketplace_id
  • Order: dim_ab_customers.business_registration_date
13. 0.000 0.000 ↓ 0.0

XN Sort (cost=7,815,137,046,199.78..7,815,152,300,647.46 rows=6,101,779,073 width=166) (actual rows= loops=)

  • Sort Key: dim_ab_customers.amazon_account_skey, dim_ab_customers.marketplace_id, dim_ab_customers.business_registration_date
14. 0.000 0.000 ↓ 0.0

XN Network (cost=1,979,234,829,848.07..6,814,145,306,266.48 rows=6,101,779,073 width=166) (actual rows= loops=)

  • Distribute
15. 0.000 0.000 ↓ 0.0

XN Hash Join DS_BCAST_INNER (cost=1,979,234,829,848.07..6,814,145,306,266.48 rows=6,101,779,073 width=166) (actual rows= loops=)

  • Hash Cond: (("outer".marketplace_skey = "inner".marketplace_id) AND ("outer".cal_day_date = trunc("inner".comm_creation_day_lcl)))
16. 0.000 0.000 ↓ 0.0

XN Subquery Scan ct (cost=39,180,718.29..40,132,579.74 rows=47,593,073 width=104) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

XN HashAggregate (cost=39,180,718.29..39,656,649.02 rows=47,593,073 width=36) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

XN Seq Scan on fact_contact_skill_na (cost=0.00..33,231,584.25 rows=475,930,723 width=36) (actual rows= loops=)

  • Filter: (('2019-06-01'::date < trunc(creation_date_datetime_lcl)) AND ((direction)::text = 'Inbound'::text))
19. 0.000 0.000 ↓ 0.0

XN Hash (cost=1,979,195,623,488.32..1,979,195,623,488.32 rows=5,128,292 width=70) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

XN Hash Join DS_DIST_BOTH (cost=487,874.56..1,979,195,623,488.32 rows=5,128,292 width=70) (actual rows= loops=)

  • Outer Dist Key: (hmd.customer_id)::numeric
  • Inner Dist Key: dim_ab_customers.amazon_account_skey
  • Hash Cond: (("outer"."?column9?" = "inner".amazon_account_skey) AND ("outer".marketplace_skey = "inner".marketplace_id))
  • Join Filter: (("outer".comm_creation_day_lcl_skey <= (to_char("inner".effective_end_date, 'YYYYMMDD'::text))::integer) AND (trunc("inner".business_registration_date) <= trunc("outer".comm_creation_day_lcl)) AND ("outer".comm_creation_day_lcl_skey >= (to_char("inner".effective_start_date, 'YYYYMMDD'::text))::integer))
21. 0.000 0.000 ↓ 0.0

XN Seq Scan on fact_hmd_na hmd (cost=0.00..21,748,263.68 rows=176,732,023 width=57) (actual rows= loops=)

  • Filter: (('2019-06-01'::date < trunc(comm_creation_day_lcl)) AND ('2019-06-01'::date <= trunc(comm_creation_day_lcl)) AND (customer_id > 1) AND ((owner_agent_login)::text <> 'drone'::text) AND ((owner_agent_login)::text <> 'cs-arf'::text))
22. 0.000 0.000 ↓ 0.0

XN Hash (cost=425,522.97..425,522.97 rows=12,470,318 width=41) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

XN Seq Scan on dim_ab_customers (cost=0.00..425,522.97 rows=12,470,318 width=41) (actual rows= loops=)

  • Filter: (((is_active_now)::text = 'Y'::text) AND ((is_business_account_active)::text = 'Y'::text) AND (is_internal_business_account = 0))