explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c6lV

Settings

Optimization(s) for this plan:

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

Gather (cost=734,825.12..125,739,068.31 rows=24,836,788 width=4,641) (actual rows= loops=)

  • Workers Planned: 7
2. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=733,825.12..36,290,152.14 rows=3,548,113 width=4,641) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=733,824.69..20,131,249.70 rows=3,548,113 width=2,899) (actual rows= loops=)

  • Hash Cond: ((root.api_srce_vrsn_id = ar_relationship_limit.api_srce_vrsn_id) AND ((root.ar_key)::text = (ar_relationship_limit.ar_key)::text))
4. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=684,905.86..19,878,240.79 rows=3,548,113 width=2,881) (actual rows= loops=)

  • Hash Cond: ((root.api_srce_vrsn_id = ar_relationship_pkg.api_srce_vrsn_id) AND ((root.ar_key)::text = (ar_relationship_pkg.ar_key)::text))
5. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=684,901.40..19,851,625.47 rows=3,548,113 width=2,863) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=684,900.84..17,493,324.59 rows=3,548,113 width=2,745) (actual rows= loops=)

  • Hash Cond: ((grp_irt_system_cde.grp_irt_system_cde)::text = (interest_rate_type.grp_irt_system_cde)::text)
  • Join Filter: (((COALESCE(root.lc_cmtmt_type_cde, 'TYPE_CD'::character varying))::text = (COALESCE(interest_rate_type.lc_cmtmt_type_cde, root.lc_cmtmt_type_cde, 'TYPE_CD'::character varying))::text) AND ((COALESCE(root.intr
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=684,898.94..17,236,459.31 rows=3,548,113 width=2,727) (actual rows= loops=)

  • Hash Cond: ((root.srce_sys_cde)::text = (grp_irt_system_cde.ar_srce_cde)::text)
8. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=684,896.34..17,187,670.16 rows=3,548,113 width=2,710) (actual rows= loops=)

  • Hash Cond: ((root.ar_key)::text = (stg_reg_ar_extn.ar_key)::text)
9. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=684,884.54..17,174,352.83 rows=3,548,113 width=2,476) (actual rows= loops=)

  • Hash Cond: ((root.ar_key)::text = (stg_reg_ar_gan_rltshp.ar_key)::text)
  • Join Filter: ((root.msmt_dte_id >= stg_reg_ar_gan_rltshp.start_dte_id) AND (root.msmt_dte_id <= stg_reg_ar_gan_rltshp.end_dte_id))
10. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=684,872.33..17,161,035.17 rows=3,548,113 width=2,461) (actual rows= loops=)

  • Hash Cond: ((root.prod_level_3)::text = (coll_sec_issuer.prod_level_3)::text)
11. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=684,686.64..17,147,544.05 rows=3,548,113 width=2,536) (actual rows= loops=)

  • Hash Cond: ((issuer.resid_ctry_cde)::text = (country_iso3166_short_issuer.resid_ctry_cde)::text)
12. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=684,674.89..17,129,045.68 rows=3,548,113 width=2,540) (actual rows= loops=)

  • Hash Cond: ((issuer.cus_anzsic_indstr_cde)::text = (anzsic_sector_issuer.cus_anzsic_indstr_cde)::text)
  • Join Filter: (((COALESCE(tmp_org_type_issuer.category, 'DEF_ORG'::character varying))::text = (COALESCE(anzsic_sector_issuer.cus_org_type, tmp_org_type_issuer.category, 'DEF_ORG'::charac
13. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=684,672.49..17,057,434.57 rows=3,548,113 width=2,499) (actual rows= loops=)

  • Join Filter: ((COALESCE(grp_deposit_interest_rate_dup_issuer.tmp_dep_interest_rate_cde, 'GROUP_OTHERS'::character varying))::text = (COALESCE(prod_hierarchy_afs_dup_issuer.tmp_dep_
14. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=684,672.22..14,768,898.55 rows=3,548,113 width=2,508) (actual rows= loops=)

  • Hash Cond: (((root.prod_nture_cde)::text = (grp_deposit_interest_rate_dup_issuer.prod_nture_cde)::text) AND ((root.dep_intrst_rate_type_cde)::text = (grp_deposit_interest_rat
15. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=684,670.92..14,742,286.18 rows=3,548,113 width=2,498) (actual rows= loops=)

  • Hash Cond: ((issuer.cus_org_type)::text = (tmp_org_type_issuer.cust_org_type)::text)
16. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=684,669.31..14,724,362.35 rows=3,548,113 width=2,489) (actual rows= loops=)

  • Hash Cond: ((tmp_cus_type_issuer.grp_cs_cus_type_cde1)::text = (medfin_sector_issuer.cus_type_cde1)::text)
17. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=684,667.61..14,678,704.79 rows=3,548,113 width=2,406) (actual rows= loops=)

  • Hash Cond: ((issuer.cus_type_cde)::text = (tmp_cus_type_issuer.cust_type_cde)::text)
  • Join Filter: ((COALESCE(root.prod_level_2, 'PROD_LEVEL2'::character varying))::text = (COALESCE(tmp_cus_type_issuer.prod_level_2, root.prod_level_2, 'PROD_L
18. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=684,662.37..14,527,847.08 rows=3,548,113 width=2,376) (actual rows= loops=)

  • Hash Cond: ((root.fncl_instt_cde)::text = (host_country.fncl_instt_cde)::text)
19. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=684,661.01..14,479,059.17 rows=3,548,113 width=2,373) (actual rows= loops=)

  • Hash Cond: ((root.pkg_type_cde)::text = (tmp_facility_prod.facility_product_name)::text)
20. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=684,659.92..14,465,231.81 rows=3,548,113 width=2,365) (actual rows= loops=)

  • Hash Cond: ("substring"((root.prod_level_3)::text, 1, 3) = (asx_listed.prod_level_3)::text)
  • Join Filter: ((root.srce_sys_cde)::text = ANY ('{NETS_EQ,NETS_SB}'::text[]))
21. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=684,599.18..14,403,079.10 rows=3,548,113 width=2,363) (actual rows= loops=)

  • Hash Cond: ((root.srce_sys_cde)::text = (grp_meas_type_gwfs.ar_srce_cde)::text)
  • Join Filter: (((COALESCE(root.prod_level_1, 'PROD_LEVEL_1'::character varying))::text = (COALESCE(grp_meas_type_gwfs.prod_level_1, r
22. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=684,596.70..14,295,976.71 rows=3,548,113 width=2,350) (actual rows= loops=)

  • Hash Cond: ((scrty_prov.api_srce_vrsn_id = issuer.api_srce_vrsn_id) AND ((scrty_prov.cus_key)::text = (issuer.cus_key)::text))
23. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=98,704.27..11,476,690.61 rows=3,548,113 width=2,321) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=98,703.71..9,160,826.21 rows=3,548,113 width=2,301) (actual rows= loops=)

  • Hash Cond: ((root.api_srce_vrsn_id = ar_relationship.api_srce_vrsn_id) AND ((root.ar_key)::text = (ar_relationship
25. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=31.47..8,857,040.29 rows=3,548,113 width=2,296) (actual rows= loops=)

  • Hash Cond: ((root.cst_cntr_cde)::text = (grp_cost_centres.cst_cntr_cde)::text)
  • Join Filter: ((COALESCE(root.short_sell, 'SHORT_SELL'::character varying))::text = (COALESCE(grp_cost_centre
26. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=29.41..8,839,297.65 rows=3,548,113 width=2,432) (actual rows= loops=)

  • Hash Cond: ((customer.cus_anzsic_indstr_cde)::text = (anzsic_sector.cus_anzsic_indstr_cde)::text)
  • Join Filter: (((COALESCE(tmp_org_type.category, 'DEF_ORG'::character varying))::text = (COALESCE(anzsi
27. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=27.02..8,767,686.54 rows=3,548,113 width=2,384) (actual rows= loops=)

  • Join Filter: ((COALESCE(grp_deposit_interest_rate_dup.tmp_dep_interest_rate_cde, 'GROUP_OTHERS':
  • -> Index Scan using i_t_4912 on t_4912 prod_hierarchy_afs_dup (cost=0.28..0.63 rows=1 width=48
28. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=26.74..6,479,150.52 rows=3,548,113 width=2,389) (actual rows= loops=)

  • Hash Cond: (((root.prod_nture_cde)::text = (grp_deposit_interest_rate_dup.prod_nture_cde):
  • Index Cond: (((root.fncl_instt_cde)::text = (fncl_instt_cde)::text) AND ((root.srce_sys_cd
  • Filter: (((COALESCE(root.prod_nture_cde_2, 'C'::character varying))::text = (COALESCE(prod
29. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=25.44..6,452,538.15 rows=3,548,113 width=2,374) (actual rows= loops=)

  • Hash Cond: ((COALESCE(customer.cus_org_type, '<NULL>'::character varying))::text = (
30. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=23.84..6,434,441.17 rows=3,548,113 width=2,365) (actual rows= loops=)

  • Hash Cond: ((tmp_cus_type.grp_cs_cus_type_cde1)::text = (medfin_sector.cus_typ
31. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=22.14..6,388,783.61 rows=3,548,113 width=2,282) (actual rows= loops=)

  • Hash Cond: ((customer.cus_type_cde)::text = (tmp_cus_type.cust_type_cde)
  • Join Filter: ((COALESCE(root.prod_level_2, 'DEF_PRD'::character varying)
32. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=16.90..6,237,925.90 rows=3,548,113 width=2,256) (actual rows= loops=)

  • Hash Cond: ((customer.resid_ctry_cde)::text = (country_iso3166_sho
  • -> Nested Loop Left Join (cost=5.15..6219427.54 rows=3548113 wid
  • -> Hash Left Join (cost=4.59..4029991.42 rows=3548113 widt
  • Hash Cond: ((root.scrtysn_pool_id)::text = (securitisa
  • -> Hash Left Join (cost=3.25..4016120.75 rows=354811
  • Hash Cond: ((prod_hierarchy_afs.tag_grp_product_
  • -> Nested Loop Left Join (cost=1.58..3967332.5
  • Join Filter: ((COALESCE(grp_deposit_intere
  • -> Hash Left Join (cost=1.30..1678796.49
  • Hash Cond: (((root.prod_nture_cde)::
  • -> Parallel Seq Scan on aggr_5802 r
  • -> Hash (cost=1.12..1.12 rows=12 w
  • -> Seq Scan on t_4911 grp_dep
  • -> Index Scan using i_t_4912 on t_4912 pr
  • Index Cond: (((root.fncl_instt_cde):
  • Filter: (((COALESCE(root.prod_nture_
  • -> Index Scan using i_aggr_7761_1 on aggr_7761 customer (c
  • Index Cond: ((api_srce_vrsn_id = root.api_srce_vrsn_id
33. 0.000 0.000 ↓ 0.0

Hash (cost=1.30..1.30 rows=30 width=27) (actual rows= loops=)

  • -> Seq Scan on t_4953 tmp_prod_afs_type
34. 0.000 0.000 ↓ 0.0

Hash (cost=1.15..1.15 rows=15 width=22) (actual rows= loops=)

  • -> Seq Scan on t_4908 securitisation (cost=0.0
35. 0.000 0.000 ↓ 0.0

Hash (cost=6.89..6.89 rows=389 width=9) (actual rows= loops=)

  • -> Seq Scan on t_4952 country_iso3166_short (cost=0.00..6.
36. 0.000 0.000 ↓ 0.0

Hash (cost=3.44..3.44 rows=144 width=45) (actual rows= loops=)

  • -> Seq Scan on t_4934 tmp_cus_type (cost=0.00..3.44 rows=144 wid
37. 0.000 0.000 ↓ 0.0

Hash (cost=1.31..1.31 rows=31 width=99) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Seq Scan on t_4949 medfin_sector (cost=0.00..1.31 rows=31 width=99) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Hash (cost=1.27..1.27 rows=27 width=14) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Seq Scan on t_4902 tmp_org_type (cost=0.00..1.27 rows=27 width=14) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Hash (cost=1.12..1.12 rows=12 width=23) (actual rows= loops=)

  • -> Seq Scan on t_4911 grp_deposit_interest_rate_dup (cost=0.00..1.12 rows=12 width
42. 0.000 0.000 ↓ 0.0

Hash (cost=1.62..1.62 rows=62 width=84) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Seq Scan on t_4943 anzsic_sector (cost=0.00..1.62 rows=62 width=84) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Hash (cost=1.47..1.47 rows=47 width=27) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Seq Scan on t_4918 grp_cost_centres (cost=0.00..1.47 rows=47 width=27) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Hash (cost=87,151.90..87,151.90 rows=768,023 width=27) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on aggr_5804 ar_relationship (cost=10,166.61..87,151.90 rows=768,023 width=27) (actual rows= loops=)

  • Recheck Cond: ((ar_rltshp_type_cde)::text = 'LTRM'::text)
48. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on i_aggr_5804_2 (cost=0.00..9,974.60 rows=768,023 width=0) (actual rows= loops=)

  • Index Cond: ((ar_rltshp_type_cde)::text = 'LTRM'::text)
49. 0.000 0.000 ↓ 0.0

Index Scan using i_aggr_5801_1 on aggr_5801 scrty_prov (cost=0.56..0.64 rows=1 width=41) (actual rows= loops=)

  • Index Cond: ((api_srce_vrsn_id = root.api_srce_vrsn_id) AND ((ar_key)::text = (root.ar_key)::text) AND ((cus_rltsh
50. 0.000 0.000 ↓ 0.0

Hash (cost=401,929.37..401,929.37 rows=6,885,137 width=69) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Seq Scan on aggr_7761 issuer (cost=0.00..401,929.37 rows=6,885,137 width=69) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Hash (cost=1.66..1.66 rows=66 width=41) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Seq Scan on t_4904 grp_meas_type_gwfs (cost=0.00..1.66 rows=66 width=41) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Hash (cost=32.55..32.55 rows=2,255 width=6) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Seq Scan on t_4929 asx_listed (cost=0.00..32.55 rows=2,255 width=6) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Hash (cost=1.04..1.04 rows=4 width=24) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Seq Scan on t_4937 tmp_facility_prod (cost=0.00..1.04 rows=4 width=24) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Hash (cost=1.16..1.16 rows=16 width=6) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Seq Scan on t_4938 host_country (cost=0.00..1.16 rows=16 width=6) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Hash (cost=3.44..3.44 rows=144 width=45) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Seq Scan on t_4934 tmp_cus_type_issuer (cost=0.00..3.44 rows=144 width=45) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Hash (cost=1.31..1.31 rows=31 width=99) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Seq Scan on t_4949 medfin_sector_issuer (cost=0.00..1.31 rows=31 width=99) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Hash (cost=1.27..1.27 rows=27 width=14) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Seq Scan on t_4902 tmp_org_type_issuer (cost=0.00..1.27 rows=27 width=14) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Hash (cost=1.12..1.12 rows=12 width=23) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Seq Scan on t_4911 grp_deposit_interest_rate_dup_issuer (cost=0.00..1.12 rows=12 width=23) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Index Scan using i_t_4912 on t_4912 prod_hierarchy_afs_dup_issuer (cost=0.28..0.63 rows=1 width=48) (actual rows= loops=)

  • Index Cond: (((root.fncl_instt_cde)::text = (fncl_instt_cde)::text) AND ((root.srce_sys_cde)::text = (srce_sys_cde)::text))
  • Filter: (((COALESCE(root.prod_nture_cde_2, 'C'::character varying))::text = (COALESCE(prod_nture_cde_2, root.prod_nture_cde_2, 'C'::character varying))::text) AND ((COALESCE(
69. 0.000 0.000 ↓ 0.0

Hash (cost=1.62..1.62 rows=62 width=84) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Seq Scan on t_4943 anzsic_sector_issuer (cost=0.00..1.62 rows=62 width=84) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Hash (cost=6.89..6.89 rows=389 width=9) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Seq Scan on t_4952 country_iso3166_short_issuer (cost=0.00..6.89 rows=389 width=9) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Hash (cost=127.53..127.53 rows=4,653 width=48) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Seq Scan on t_4950 coll_sec_issuer (cost=0.00..127.53 rows=4,653 width=48) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Hash (cost=12.20..12.20 rows=1 width=441) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=10.89..12.20 rows=1 width=441) (actual rows= loops=)

  • Hash Cond: ((kapiti_product.gan_number)::text = (stg_reg_ar_gan_rltshp.gan_nbr)::text)
77. 0.000 0.000 ↓ 0.0

Seq Scan on t_4927 kapiti_product (cost=0.00..1.22 rows=22 width=26) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

Hash (cost=10.88..10.88 rows=1 width=844) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

Seq Scan on aggr_5803 stg_reg_ar_gan_rltshp (cost=0.00..10.88 rows=1 width=844) (actual rows= loops=)

  • Filter: ((actv_flg)::text = 'Y'::text)
80. 0.000 0.000 ↓ 0.0

Hash (cost=10.80..10.80 rows=80 width=652) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Seq Scan on aggr_5655 stg_reg_ar_extn (cost=0.00..10.80 rows=80 width=652) (actual rows= loops=)

82. 0.000 0.000 ↓ 0.0

Hash (cost=1.71..1.71 rows=71 width=17) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Seq Scan on t_4947 grp_irt_system_cde (cost=0.00..1.71 rows=71 width=17) (actual rows= loops=)

84. 0.000 0.000 ↓ 0.0

Hash (cost=1.40..1.40 rows=40 width=42) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

Seq Scan on t_4945 interest_rate_type (cost=0.00..1.40 rows=40 width=42) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

Index Scan using i_aggr_5808_1 on aggr_5808 stg_reg_clatrl (cost=0.56..0.63 rows=3 width=139) (actual rows= loops=)

  • Index Cond: ((api_srce_vrsn_id = root.api_srce_vrsn_id) AND ((ar_key)::text = (root.ar_key)::text) AND (amt_rank = 1))
87. 0.000 0.000 ↓ 0.0

Hash (cost=4.45..4.45 rows=1 width=22) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

Index Scan using i_aggr_5804_2 on aggr_5804 ar_relationship_pkg (cost=0.43..4.45 rows=1 width=22) (actual rows= loops=)

  • Index Cond: ((ar_rltshp_type_cde)::text = 'PKG'::text)
89. 0.000 0.000 ↓ 0.0

Hash (cost=48,146.21..48,146.21 rows=51,508 width=22) (actual rows= loops=)

90. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on aggr_5804 ar_relationship_limit (cost=683.62..48,146.21 rows=51,508 width=22) (actual rows= loops=)

  • Recheck Cond: ((ar_rltshp_type_cde)::text = 'LIMIT'::text)
91. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on i_aggr_5804_2 (cost=0.00..670.74 rows=51,508 width=0) (actual rows= loops=)

  • Index Cond: ((ar_rltshp_type_cde)::text = 'LIMIT'::text)
92. 0.000 0.000 ↓ 0.0

Index Scan using i_aggr_5804_1 on aggr_5804 ar_relationship_lcar (cost=0.43..0.46 rows=1 width=22) (actual rows= loops=)

  • Index Cond: ((api_srce_vrsn_id = root.api_srce_vrsn_id) AND ((ar_key)::text = (root.ar_key)::text))
  • Filter: ((ar_rltshp_type_cde)::text = 'LCAR'::text)