explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LHMP

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

Unique (cost=289.28..289.45 rows=1 width=1,922) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=289.28..289.29 rows=1 width=1,922) (actual rows= loops=)

  • Sort Key: acvnc.reg, acvnc.version, (to_char((to_date(to_char(acvnc.last_version, 'DD-MM-YYYY'::text), 'DD-MM-YYYY'::text))::timestamp with time zone, 'DD-MM-YYYY'::text)), (to_char((to_date(to_char(acvnc.version_modify_date, 'DD-MM-YYYY'::text), 'DD-MM-YYYY'::text))::timestamp with time zone, 'DD-MM-YYYY'::text)), acvnc.creator, acvnc.creator_ftid, acvnc.last_modify_by, acvnc.last_modify_by_ftid, acvnc.current_status, (CASE WHEN (acvnc.version = '1'::numeric) THEN CASE WHEN (upper((acvnc.current_status)::text) = 'ACTIVE'::text) THEN CASE WHEN (upper((acvnc.creator)::text) = 'UNKNOWN'::text) THEN '1990-01-01 00:00:00'::timestamp without time zone ELSE acvnc.last_version END ELSE CASE WHEN ((acvnc.creator)::text = 'UNKNOWN'::text) THEN '1990-01-01 00:00:00'::timestamp without time zone ELSE acvnc.last_version END END ELSE acvnc.last_version END), (CASE WHEN (acvnc.version = '1'::numeric) THEN CASE WHEN (upper((acvnc.current_status)::text) = 'ACTIVE'::text) THEN 'present'::text ELSE (acvnc.version_modify_date)::text END ELSE CASE WHEN (upper((acvnc.current_status)::text) = 'ACTIVE'::text) THEN 'present'::text ELSE (acvnc.version_modify_date)::text END END), cr1.csd_mn_workgroup, cr1.csd_functionalarea, regtl.region_name, subregtl.sub_region_name, cl1.country, cr1.city, ctl1.contact_type, (split_part((cr1.supervisor)::text, ','::text, 1)), (split_part((cr1.supervisor)::text, ','::text, 2)), (pkg_csm_report_versions_get_ico1_names_csd(cac.cac_id, (acvnc.ico1_customer_support_id)::numeric, (cac.cac_name)::text, acvnc.reg, acvnc.version)), (pkg_csm_report_versions_get_ico1_codes_csd(cac.cac_id, (acvnc.ico1_customer_support_id)::numeric, (cac.cac_name)::text, acvnc.reg, acvnc.version, (acvnc.csd_id)::numeric)), (pkg_csm_report_versions_get_spec_ico1_names_csd(cac.cac_id, (acvnc.ico1_customer_support_id)::numeric, (cac.cac_name)::text, (acvnc.csd_id)::numeric, (acvnc.resource_type_id)::numeric, acvnc.reg, acvnc.version)), (pkg_csm_report_versions_get_spec_ico1_codes_csd(cac.cac_id, (acvnc.ico1_customer_support_id)::numeric, (cac.cac_name)::text, (acvnc.csd_id)::numeric, (acvnc.resource_type_id)::numeric, acvnc.reg, acvnc.version)), (pkg_csm_report_get_partner_channels(acvnc.cac_id, (acvnc.ico1_customer_support_id)::numeric, (cac.cac_name)::text, (cs.nsc_ico1_identifier_code)::text)), (to_char(cs.contract_end_date, 'DD-MM-YYYY'::text)), cs.service_desk_committed, acvnc.fte_allocation_per_account, acvnc.incident_mgt, acvnc.performance_mgt, cs.csm_group_email, cac.cac_name, cac.cac_id, pcac.primary_cac_name, (CASE WHEN (COALESCE((pcac.primary_cac_label)::text, ''::text) = ''::text) THEN 'NO SPECIFIC CAC'::character varying ELSE pcac.primary_cac_label END), (pkg_csm_report_versions_get_prod_servicescsd(acvnc.cac_id, (acvnc.ico1_customer_support_id)::numeric, (cac.cac_name)::text, (acvnc.csd_id)::numeric, (acvnc.resource_type_id)::numeric, acvnc.reg, acvnc.version)), acvnc.sites_network, acvnc.sites_is, acvnc.sites_telephony, acvnc.sites_telepresence, acvnc.connections_network, acvnc.connections_is, acvnc.connections_telephony, acvnc.connections_telepresence, (CASE WHEN ((COALESCE((acvnc.sites_network)::text, ''::text) = ''::text) AND (COALESCE((acvnc.sites_is)::text, ''::text) = ''::text) AND (COALESCE((acvnc.sites_telephony)::text, ''::text) = ''::text) AND (COALESCE((acvnc.sites_telepresence)::text, ''::text) = ''::text)) THEN NULL::integer ELSE (((COALESCE(acvnc.sites_network, 0) + COALESCE(acvnc.sites_is, 0)) + COALESCE(acvnc.sites_telephony, 0)) + COALESCE(acvnc.sites_telepresence, 0)) END), (CASE WHEN ((COALESCE((acvnc.connections_network)::text, ''::text) = ''::text) AND (COALESCE((acvnc.connections_is)::text, ''::text) = ''::text) AND (COALESCE((acvnc.connections_telephony)::text, ''::text) = ''::text) AND (COALESCE((acvnc.connections_telepresence)::text, ''::text) = ''::text)) THEN NULL::integer ELSE (((COALESCE(acvnc.connections_network, 0) + COALESCE(acvnc.connections_is, 0)) + COALESCE(acvnc.connections_telephony, 0)) + COALESCE(acvnc.connections_telepresence, 0)) END), csl.csp_service, (to_char(acvnc.csm_start_date, 'DD-MM-YYYY'::text)), (pkg_csm_report_get_country((acvnc.supported_country_code)::text)), spreg.region_name, (CASE WHEN ((rutl.resource_util_type_descr)::text = '---- Select Type ----'::text) THEN 'Model Not Applied'::character varying ELSE rutl.resource_util_type_descr END), acvnc.region_contract_signed, acvnc.support_provided_by_resource, acvnc.comment_text, ((acvnc.ico1_customer_support_id)::text), acvnc.resource_type_id, ctl1.fte_type_id, (CASE WHEN (COALESCE((cust_segment_file_upload.customer_segment_level)::text, ''::text) = ''::text) THEN COALESCE(cust_segment_file_upload.customer_segment_level, 'Non-Strategic Customer'::character varying) ELSE cust_segment_file_upload.customer_segment_level END), (CASE WHEN (COALESCE((acvnc.last_version)::text, ''::text) = ''::text) THEN to_date('01-01-1990'::text, 'DD-MM-YYYY'::text) ELSE to_date(to_char(acvnc.last_version, 'DD-MM-YYYY'::text), 'DD-MM-YYYY'::text) END), (CASE WHEN (COALESCE((acvnc.version_modify_date)::text, ''::text) = ''::text) THEN to_date(to_char(LOCALTIMESTAMP, 'DD-MM-YYYY'::text), 'DD-MM-YYYY'::text) ELSE to_date(to_char(acvnc.version_modify_date, 'DD-MM-YYYY'::text), 'DD-MM-YYYY'::text) END), (pkg_csm_report_versions_get_languages_supportedcsd(acvnc.cac_id, (acvnc.ico1_customer_support_id)::numeric, (cac.cac_name)::text, (acvnc.csd_id)::numeric, (acvnc.resource_type_id)::numeric, acvnc.reg, acvnc.version)), ((((cl1.country)::text || '-'::text) || (cr1.city)::text)), (pkg_csm_report_get_cost_tiercsd((cl1.country)::text))
3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=119.34..289.27 rows=1 width=1,922) (actual rows= loops=)

  • Join Filter: (acvnc.cac_id = cac.cac_id)
4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=119.06..286.22 rows=1 width=1,342) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=118.92..285.91 rows=1 width=1,120) (actual rows= loops=)

  • Join Filter: (acvnc.ico1_customer_support_id = icd.ico1_customer_support_id)
6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=118.63..283.81 rows=1 width=1,134) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=118.34..281.73 rows=1 width=1,116) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=118.21..281.50 rows=1 width=902) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=118.07..281.28 rows=1 width=812) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=117.92..280.83 rows=1 width=803) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=117.78..280.54 rows=1 width=709) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Nested Loop (cost=117.64..280.26 rows=1 width=615) (actual rows= loops=)

  • Join Filter: (cr1.csd_id = acvnc.csd_id)
13. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=117.50..280.08 rows=1 width=559) (actual rows= loops=)

  • Join Filter: ((cs.nsc_ico1_identifier_code)::text = ((cust_segment_file_upload.ic01_code)::character varying)::text)
14. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=117.50..261.47 rows=1 width=532) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=117.36..261.25 rows=1 width=438) (actual rows= loops=)

  • Hash Cond: ((cs.nsc_ico1_identifier_code)::text = (spec.ico1_identifier_code)::text)
16. 0.000 0.000 ↓ 0.0

Seq Scan on cac_customer_data cs (cost=0.00..122.09 rows=5,809 width=42) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Hash (cost=117.35..117.35 rows=1 width=396) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash Join (cost=12.64..117.35 rows=1 width=396) (actual rows= loops=)

  • Hash Cond: ((spec.cac_id = acvnc.cac_id) AND (spec.csd_id = acvnc.csd_id) AND (spec.resource_type_id = acvnc.resource_type_id) AND (spec.ico1_customer_support_id = acvnc.ico1_customer_support_id))
19. 0.000 0.000 ↓ 0.0

Seq Scan on assign_csd_ver_specific_ico1 spec (cost=0.00..33.74 rows=1,774 width=32) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash (cost=8.88..8.88 rows=188 width=372) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Seq Scan on assign_csd_ver_nosp_cac acvnc (cost=0.00..8.88 rows=188 width=372) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Index Scan using pk_csp_service_lkp on csp_service_lkp csl (cost=0.14..0.22 rows=1 width=102) (actual rows= loops=)

  • Index Cond: (csp_service_id = acvnc.csp_service_id)
23. 0.000 0.000 ↓ 0.0

Seq Scan on cust_segment_file_upload (cost=0.00..18.55 rows=3 width=33) (actual rows= loops=)

  • Filter: (upper((flag)::text) = 'T'::text)
24. 0.000 0.000 ↓ 0.0

Index Scan using pk_csd_role_csd_id on csd_role cr1 (cost=0.14..0.17 rows=1 width=64) (actual rows= loops=)

  • Index Cond: (csd_id = spec.csd_id)
25. 0.000 0.000 ↓ 0.0

Index Scan using csm_sub_region_lkp_sub_region_id_key on csm_sub_region_lkp subregtl (cost=0.14..0.28 rows=1 width=102) (actual rows= loops=)

  • Index Cond: (sub_region_id = cr1.sub_region_id)
26. 0.000 0.000 ↓ 0.0

Index Scan using pk_csm_region_lkp on csm_region_lkp regtl (cost=0.14..0.28 rows=1 width=102) (actual rows= loops=)

  • Index Cond: (region_id = cr1.region_id)
27. 0.000 0.000 ↓ 0.0

Index Scan using countries_pk on countries_lookup cl1 (cost=0.15..0.44 rows=1 width=15) (actual rows= loops=)

  • Index Cond: (country_code = (cr1.country_code)::bpchar)
28. 0.000 0.000 ↓ 0.0

Index Scan using pk_csm_region_lkp on csm_region_lkp spreg (cost=0.14..0.22 rows=1 width=102) (actual rows= loops=)

  • Index Cond: (region_id = acvnc.supported_region_id)
29. 0.000 0.000 ↓ 0.0

Index Scan using pk_resource_util_type_lkp on resource_util_type_lkp rutl (cost=0.14..0.22 rows=1 width=222) (actual rows= loops=)

  • Index Cond: (resource_util_type_id = acvnc.resource_util_type_id)
30. 0.000 0.000 ↓ 0.0

Index Scan using pk_pcac_cust_id on primary_cac_customer pcac (cost=0.28..2.09 rows=1 width=31) (actual rows= loops=)

  • Index Cond: (primary_cac_id = acvnc.primary_cac_id)
31. 0.000 0.000 ↓ 0.0

Index Scan using pk_iccd_ic01 on ic01_customer_data icd (cost=0.29..2.08 rows=1 width=10) (actual rows= loops=)

  • Index Cond: ((ico1_identifier_code)::text = (spec.ico1_identifier_code)::text)
  • Filter: (spec.ico1_customer_support_id = ico1_customer_support_id)
32. 0.000 0.000 ↓ 0.0

Index Scan using pk_cont_type on contact_type_lookup ctl1 (cost=0.14..0.23 rows=1 width=254) (actual rows= loops=)

  • Index Cond: (contact_type_id = (acvnc.resource_type_id)::numeric)
  • Filter: ((application_type)::text = 'CSM'::text)
33. 0.000 0.000 ↓ 0.0

Index Scan using pk_ecac_cust_id on end_user_cac_customer cac (cost=0.29..0.56 rows=1 width=22) (actual rows= loops=)

  • Index Cond: (cac_id = spec.cac_id)
  • Filter: (upper((cac_name)::text) = 'NO SPECIFIC CAC'::text)