explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0NHY

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

XN Subquery Scan outer_q (cost=999,999,999,999,999,967,336,168,804,116,691,273,849,533,185,806,555,472,917,961,779,471,295,845,921,727,862,608,739,868,455,469,056.00..999,999,999,999,999,967,336,168,804,116,691,273,849,533,185,806,555,472,917,961,779,471,295,845,921,727,862,608,739,868,455,469,056.00 rows=95 width=4,674) (actual rows= loops=)

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

XN Window (cost=999,999,999,999,999,967,336,168,804,116,691,273,849,533,185,806,555,472,917,961,779,471,295,845,921,727,862,608,739,868,455,469,056.00..999,999,999,999,999,967,336,168,804,116,691,273,849,533,185,806,555,472,917,961,779,471,295,845,921,727,862,608,739,868,455,469,056.00 rows=18,894 width=919) (actual rows= loops=)

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

XN Sort (cost=999,999,999,999,999,967,336,168,804,116,691,273,849,533,185,806,555,472,917,961,779,471,295,845,921,727,862,608,739,868,455,469,056.00..999,999,999,999,999,967,336,168,804,116,691,273,849,533,185,806,555,472,917,961,779,471,295,845,921,727,862,608,739,868,455,469,056.00 rows=18,894 width=919) (actual rows= loops=)

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

XN Network (cost=1,362,212,601,035.26..999,999,999,999,999,967,336,168,804,116,691,273,849,533,185,806,555,472,917,961,779,471,295,845,921,727,862,608,739,868,455,469,056.00 rows=18,894 width=919) (actual rows= loops=)

  • Distribute
5. 0.000 0.000 ↓ 0.0

XN Hash Join DS_DIST_OUTER (cost=1,362,212,601,035.26..999,999,999,999,999,967,336,168,804,116,691,273,849,533,185,806,555,472,917,961,779,471,295,845,921,727,862,608,739,868,455,469,056.00 rows=18,894 width=919) (actual rows= loops=)

  • Outer Dist Key: "outer".comm_id
  • Hash Cond: ("outer".comm_id = "inner".comm_id)
6. 0.000 0.000 ↓ 0.0

XN Hash Left Join DS_DIST_OUTER (cost=1,362,081,124,818.80..999,999,999,999,999,967,336,168,804,116,691,273,849,533,185,806,555,472,917,961,779,471,295,845,921,727,862,608,739,868,455,469,056.00 rows=6,298 width=751) (actual rows= loops=)

  • Outer Dist Key: ("inner".business_account_skey)::text
  • Hash Cond: ("outer"."?column46?" = ("inner".business_account_id)::text)
7. 0.000 0.000 ↓ 0.0

XN Hash Right Join DS_DIST_OUTER (cost=1,362,081,120,798.13..1,363,577,869,658.30 rows=6,298 width=747) (actual rows= loops=)

  • Outer Dist Key: dcrg.agent_sic4_skey
  • Hash Cond: (("outer".agent_sic4_skey = "inner".agent_sic4_skey) AND ("outer".agent_sic3_skey = "inner".agent_sic3_skey) AND ("outer".agent_sic2_skey = "inner".agent_sic2_skey) AND ("outer".agent_sic1_skey = "inner".agent_sic1_skey) AND ("outer".marketplace_skey = "inner".marketplace_skey))
8. 0.000 0.000 ↓ 0.0

XN Seq Scan on dim_contact_reason_group dcrg (cost=0.00..629.76 rows=62,976 width=142) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

XN Hash (cost=1,362,081,120,755.87..1,362,081,120,755.87 rows=3,381 width=641) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

XN Hash Right Join DS_DIST_INNER (cost=1,361,753,288,420.75..1,362,081,120,755.87 rows=3,381 width=641) (actual rows= loops=)

  • Inner Dist Key: hmd.agent_sic4_skey
  • Hash Cond: ("outer".agent_sic_skey = "inner".agent_sic4_skey)
11. 0.000 0.000 ↓ 0.0

XN Seq Scan on dim_agent_sic_all_nodes d4 (cost=0.00..875.61 rows=87,561 width=35) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

XN Hash (cost=1,361,753,288,412.30..1,361,753,288,412.30 rows=3,381 width=610) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

XN Hash Right Join DS_DIST_INNER (cost=1,361,442,361,077.19..1,361,753,288,412.30 rows=3,381 width=610) (actual rows= loops=)

  • Inner Dist Key: hmd.agent_sic3_skey
  • Hash Cond: ("outer".agent_sic_skey = "inner".agent_sic3_skey)
14. 0.000 0.000 ↓ 0.0

XN Seq Scan on dim_agent_sic_all_nodes d3 (cost=0.00..875.61 rows=87,561 width=35) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

XN Hash (cost=1,361,442,361,068.74..1,361,442,361,068.74 rows=3,381 width=579) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

XN Hash Right Join DS_DIST_INNER (cost=1,361,148,338,733.62..1,361,442,361,068.74 rows=3,381 width=579) (actual rows= loops=)

  • Inner Dist Key: hmd.agent_sic2_skey
  • Hash Cond: ("outer".agent_sic_skey = "inner".agent_sic2_skey)
17. 0.000 0.000 ↓ 0.0

XN Seq Scan on dim_agent_sic_all_nodes d2 (cost=0.00..875.61 rows=87,561 width=35) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

XN Hash (cost=1,361,148,338,725.17..1,361,148,338,725.17 rows=3,381 width=548) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

XN Hash Right Join DS_DIST_INNER (cost=1,360,869,108,265.06..1,361,148,338,725.17 rows=3,381 width=548) (actual rows= loops=)

  • Inner Dist Key: hmd.agent_sic1_skey
  • Hash Cond: ("outer".agent_sic_skey = "inner".agent_sic1_skey)
20. 0.000 0.000 ↓ 0.0

XN Seq Scan on dim_agent_sic_all_nodes d1 (cost=0.00..875.61 rows=87,561 width=35) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

XN Hash (cost=1,360,869,108,256.61..1,360,869,108,256.61 rows=3,381 width=517) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

XN Hash Join DS_DIST_BOTH (cost=488,494.54..1,360,869,108,256.61 rows=3,381 width=517) (actual rows= loops=)

  • Outer Dist Key: ("outer".customer_id)::numeric
  • Inner Dist Key: dim_ab_customers.amazon_account_skey
  • Hash Cond: (("outer"."?column32?" = "inner".amazon_account_skey) AND ("outer".marketplace_skey = "inner".marketplace_id))
  • Join Filter: (("outer".comm_creation_day_utc <= "inner".effective_end_date) AND ("outer".comm_creation_day_utc >= "inner".effective_start_date) AND ("outer".comm_creation_day_utc >= trunc("inner".business_registration_date)))
23. 0.000 0.000 ↓ 0.0

XN Hash Join DS_BCAST_INNER (cost=619.98..39,682,781,952.57 rows=16,094,251 width=412) (actual rows= loops=)

  • Hash Cond: (("outer".routing_skill_skey = "inner".routing_skill_skey) AND ("outer".marketplace_skey = "inner".marketplace_skey))
24. 0.000 0.000 ↓ 0.0

XN Subquery Scan hmd (cost=0.00..2,242,306.40 rows=40,371,206 width=324) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

XN Multi Scan (cost=0.00..1,838,594.34 rows=40,371,206 width=133) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

XN Seq Scan on fact_hmd_na (cost=0.00..400,365.76 rows=11,929,412 width=133) (actual rows= loops=)

  • Filter: ((comm_creation_day_utc >= '2019-07-01 00:00:00'::timestamp without time zone) AND (comm_creation_day_utc <= '2019-07-31 00:00:00'::timestamp without time zone) AND (customer_id > 1) AND ((owner_agent_login)::text <> 'drone'::text) AND ((owner_agent_login)::text <> 'cs-arf'::text))
27. 0.000 0.000 ↓ 0.0

XN Seq Scan on fact_hmd_fe (cost=0.00..31,762.77 rows=864,845 width=132) (actual rows= loops=)

  • Filter: ((comm_creation_day_utc >= '2019-07-01 00:00:00'::timestamp without time zone) AND (comm_creation_day_utc <= '2019-07-31 00:00:00'::timestamp without time zone) AND (customer_id > 1) AND ((owner_agent_login)::text <> 'drone'::text) AND ((owner_agent_login)::text <> 'cs-arf'::text))
28. 0.000 0.000 ↓ 0.0

XN Seq Scan on fact_hmd_eu (cost=0.00..381,223.69 rows=11,135,293 width=133) (actual rows= loops=)

  • Filter: ((comm_creation_day_utc >= '2019-07-01 00:00:00'::timestamp without time zone) AND (comm_creation_day_utc <= '2019-07-31 00:00:00'::timestamp without time zone) AND (customer_id > 1) AND ((owner_agent_login)::text <> 'drone'::text) AND ((owner_agent_login)::text <> 'cs-arf'::text))
29. 0.000 0.000 ↓ 0.0

XN Seq Scan on fact_hmd_in (cost=0.00..612,837.34 rows=16,157,734 width=133) (actual rows= loops=)

  • Filter: ((comm_creation_day_utc >= '2019-07-01 00:00:00'::timestamp without time zone) AND (comm_creation_day_utc <= '2019-07-31 00:00:00'::timestamp without time zone) AND (customer_id > 1) AND ((owner_agent_login)::text <> 'drone'::text) AND ((owner_agent_login)::text <> 'cs-arf'::text))
30. 0.000 0.000 ↓ 0.0

XN Seq Scan on fact_hmd_sa (cost=0.00..8,692.71 rows=283,922 width=132) (actual rows= loops=)

  • Filter: ((comm_creation_day_utc >= '2019-07-01 00:00:00'::timestamp without time zone) AND (comm_creation_day_utc <= '2019-07-31 00:00:00'::timestamp without time zone) AND (customer_id > 1) AND ((owner_agent_login)::text <> 'drone'::text) AND ((owner_agent_login)::text <> 'cs-arf'::text))
31. 0.000 0.000 ↓ 0.0

XN Hash (cost=413.32..413.32 rows=41,332 width=88) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

XN Seq Scan on dim_cs_routing_skills dcrs (cost=0.00..413.32 rows=41,332 width=88) (actual rows= loops=)

  • Filter: (cs_managed_name IS NOT NULL)
33. 0.000 0.000 ↓ 0.0

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

34. 0.000 0.000 ↓ 0.0

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

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

XN Hash (cost=3,216.54..3,216.54 rows=321,654 width=21) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

XN Seq Scan on pbi_spend_dates pbi (cost=0.00..3,216.54 rows=321,654 width=21) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

XN Hash (cost=131,476,214.96..131,476,214.96 rows=600 width=176) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

XN Subquery Scan ct (cost=66,434,630.71..131,476,214.96 rows=600 width=176) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

XN Append (cost=66,434,630.71..131,476,208.96 rows=600 width=172) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

XN Subquery Scan "*SELECT* 1" (cost=66,434,630.71..66,434,637.21 rows=200 width=172) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

XN HashAggregate (cost=66,434,630.71..66,434,635.21 rows=200 width=172) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

XN Subquery Scan volt_dt_1 (cost=58,581,773.78..62,865,150.29 rows=142,779,217 width=172) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

XN HashAggregate (cost=58,581,773.78..61,437,358.12 rows=142,779,217 width=52) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

XN Seq Scan on fact_contact_skill_na (cost=0.00..22,886,969.60 rows=1,427,792,167 width=52) (actual rows= loops=)

  • Filter: ((direction)::text = 'Inbound'::text)
45. 0.000 0.000 ↓ 0.0

XN Subquery Scan "*SELECT* 2" (cost=5,262,539.93..5,262,546.43 rows=200 width=172) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

XN HashAggregate (cost=5,262,539.93..5,262,544.43 rows=200 width=172) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

XN Subquery Scan volt_dt_2 (cost=4,654,429.90..4,986,126.28 rows=11,056,546 width=172) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

XN HashAggregate (cost=4,654,429.90..4,875,560.82 rows=11,056,546 width=52) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

XN Seq Scan on fact_contact_skill_fe (cost=0.00..1,890,293.40 rows=110,565,460 width=52) (actual rows= loops=)

  • Filter: ((direction)::text = 'Inbound'::text)
50. 0.000 0.000 ↓ 0.0

XN Subquery Scan "*SELECT* 3" (cost=59,779,018.82..59,779,025.32 rows=200 width=172) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

XN HashAggregate (cost=59,779,018.82..59,779,023.32 rows=200 width=172) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

XN Subquery Scan volt_dt_3 (cost=52,759,612.58..56,588,379.62 rows=127,625,568 width=172) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

XN HashAggregate (cost=52,759,612.58..55,312,123.94 rows=127,625,568 width=52) (actual rows= loops=)