explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bNgz

Settings
# exclusive inclusive rows x rows loops node
1. 158.852 6,993.247 ↓ 28,176.0 140,880 1

Sort (cost=27,425.70..27,425.71 rows=5 width=2,426) (actual time=6,947.520..6,993.247 rows=140,880 loops=1)

  • Sort Key: (CASE WHEN ("UADCLD"."UPD_S" IS NULL) THEN "UADCLD"."DC_CREA_S" ELSE "UADCLD"."UPD_S" END) DESC NULLS LAST
  • Sort Method: external merge Disk: 14296kB
2. 326.991 6,834.395 ↓ 28,176.0 140,880 1

HashAggregate (cost=27,425.59..27,425.64 rows=5 width=2,426) (actual time=6,712.415..6,834.395 rows=140,880 loops=1)

  • Group Key: (concat('LIM', "UADCLD"."CL_DECISION_ID_N")), (CASE WHEN (rtrim(("UADLIM"."PRIM_CRD_LIM_DC_C")::text) = ANY ('{AA,AC,AU,AW,BC,CA,CB,CL,CP,DC,DL,MA,ND,NO,OK,PA,PW,RE,RP,RW,SC,SW}'::text[])) THEN "UADLIM"."PRIM_CRD_LIM_DC_C" WHEN (rtrim(("UADCLD"."CRD_LIM_DC_C")::text) = ANY ('{AA,AC,AU,AW,BC,CA,CB,CL,CP,DC,DL,MA,ND,NO,OK,PA,PW,RE,RP,RW,SC,SW}'::text[])) THEN "UADCLD"."CRD_LIM_DC_C" ELSE NULL::character varying END), "UADCDR"."CRD_DC_REQT_TYP_C", (CASE WHEN ("UADCLD"."UPD_S" IS NULL) THEN "UADCLD"."DC_CREA_S" ELSE "UADCLD"."UPD_S" END), "UADR1B"."REQT_REC_ID_N", "UADR1B"."REQT_DELIVERY_D", "UADCDR"."REQT_M", "UADCDR"."REQT_CURRENCY_C", "UADCDR"."INCREASE_M", "UADCDR"."INCREASE_END_D", "UADR1B"."REQT_INSRD_REFER_N", "UADCDR"."THD_ID_N", "UADEIB"."BR_ID_TYPE_C", "UADEIB"."BR_ID_N", "UADEIB"."BR_ADDRS_CTRY_C", "UADEIB"."BR_A", "UADEIB"."BR_ADDRS_ST_N_A", "UADEIB"."BR_ADDRS_ST_A", "UADEIB"."BR_ADDRS_ADD_LNE_A", "UADEIB"."BR_ADDRS_POST_C_A", "UADEIB"."BR_ADDRS_STE_A", "UADEIB"."BR_ADDRS_TOWN_A", "UADEIB"."BR_ADDRS_PO_N", "UADEIB"."BR_ADDRS_TEL_N", "UADEIB"."BR_MBL_TEL_N", "UADEIB"."BR_HOMEPAGE_A", "UADEIB"."BR_SOC_MEDIA", "UADEIB"."BR_PERS_INFO", "UADCLD"."CL_DECISION_ID_N", (CASE WHEN ("UADLIM"."PRIM_DC_REFER_D" IS NULL) THEN "UADCLD"."DC_CREA_S" ELSE ("UADLIM"."PRIM_DC_REFER_D")::timestamp without time zone END), "UADLIM"."PERM_M", "UADLIM"."PERM_REFER_D", "UADLIM"."PERM_EXPY_D", "UADLIM"."TEMP_INC_M", "UADLIM"."TEMP_INC_REFER_D", "UADLIM"."TEMP_INC_EXPY_D", "UADLIM"."PERM_CCY_C", "UADCLD"."THD_ID_N", "UADEIB"."BR_A", "UADLIM"."BUYER_DC_ID_N", "DGDCTR"."CONTR_N", "DGDCTR"."EXTENSION_N", "DGDCTR"."EGBU_C
3. 60.689 6,507.404 ↓ 28,176.0 140,880 1

Append (cost=3.28..27,425.05 rows=5 width=2,426) (actual time=0.188..6,507.404 rows=140,880 loops=1)

4. 321.669 6,446.650 ↓ 35,220.0 140,880 1

Nested Loop Left Join (cost=3.28..15,717.93 rows=4 width=1,714) (actual time=0.187..6,446.650 rows=140,880 loops=1)

5. 183.686 6,124.981 ↓ 140,880.0 140,880 1

Nested Loop Left Join (cost=2.71..15,710.16 rows=1 width=1,609) (actual time=0.181..6,124.981 rows=140,880 loops=1)

6. 176.497 5,941.295 ↓ 140,880.0 140,880 1

Nested Loop Left Join (cost=2.14..15,702.53 rows=1 width=139) (actual time=0.178..5,941.295 rows=140,880 loops=1)

7. 348.980 5,764.798 ↓ 140,880.0 140,880 1

Nested Loop (cost=1.57..15,694.91 rows=1 width=98) (actual time=0.175..5,764.798 rows=140,880 loops=1)

8. 657.592 1,746.140 ↓ 1,223,226.0 1,223,226 1

Nested Loop (cost=1.00..15,687.15 rows=1 width=66) (actual time=0.039..1,746.140 rows=1,223,226 loops=1)

9. 0.021 0.021 ↑ 1.0 1 1

Index Scan using "DGDCTRS02" on "DGDCTR" (cost=0.43..8.45 rows=1 width=26) (actual time=0.020..0.021 rows=1 loops=1)

  • Index Cond: ((rtrim(("EGBU_C")::text) = 'CBAC'::text) AND (rtrim(("CONTR_N")::text) = '0000001008'::text))
10. 1,088.527 1,088.527 ↓ 45,304.7 1,223,226 1

Index Scan using "UADCLDS01" on "UADCLD" (cost=0.57..15,678.44 rows=27 width=40) (actual time=0.016..1,088.527 rows=1,223,226 loops=1)

  • Index Cond: ("CONTR_ID_N" = "DGDCTR"."CONTR_ID_N")
  • Filter: (rtrim(("DECISION_STATE_C")::text) = '01'::text)
  • Rows Removed by Filter: 2
11. 3,669.678 3,669.678 ↓ 0.0 0 1,223,226

Index Scan using "UADLIMU01" on "UADLIM" (cost=0.57..7.75 rows=1 width=52) (actual time=0.003..0.003 rows=0 loops=1,223,226)

  • Index Cond: (("CONTR_ID_N" = "UADCLD"."CONTR_ID_N") AND ("THD_ID_N" = "UADCLD"."THD_ID_N") AND ("LIM_ID_N" = "UADCLD"."LIM_ID_N"))
  • Filter: ((rtrim(("LIM_STATE_C")::text) = 'CUR'::text) AND (CASE WHEN (rtrim(("PRIM_CRD_LIM_DC_C")::text) = ANY ('{AA,AC,AU,AW,BC,CA,CB,CL,CP,DC,DL,MA,ND,NO,OK,PA,PW,RE,RP,RW,SC,SW}'::text[])) THEN "PRIM_CRD_LIM_DC_C" WHEN (rtrim(("UADCLD"."CRD_LIM_DC_C")::text) = ANY ('{AA,AC,AU,AW,BC,CA,CB,CL,CP,DC,DL,MA,ND,NO,OK,PA,PW,RE,RP,RW,SC,SW}'::text[])) THEN "UADCLD"."CRD_LIM_DC_C" ELSE NULL::character varying END IS NOT NULL))
  • Rows Removed by Filter: 1
12. 0.000 0.000 ↓ 0.0 0 140,880

Index Scan using "UADR1BS02" on "UADR1B" (cost=0.57..7.61 rows=1 width=41) (actual time=0.000..0.000 rows=0 loops=140,880)

  • Index Cond: (("THD_ID_N" = "UADCLD"."THD_ID_N") AND ("UADCLD"."REQT_ID_N" = "REQT_ID_N") AND ("UADCLD"."CONTR_ID_N" = "CONTR_ID_N"))
13. 0.000 0.000 ↓ 0.0 0 140,880

Index Scan using "UADEIBU01" on "UADEIB" (cost=0.57..7.63 rows=1 width=1,490) (actual time=0.000..0.000 rows=0 loops=140,880)

  • Index Cond: (("UADCLD"."CONTR_ID_N" = "CONTR_ID_N") AND ("UADCLD"."THD_ID_N" = "THD_ID_N") AND ("UADCLD"."REQT_ID_N" = "REQT_ID_N"))
14. 0.000 0.000 ↓ 0.0 0 140,880

Index Scan using "UADCDRU01" on "UADCDR" (cost=0.57..7.51 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=140,880)

  • Index Cond: (("UADR1B"."CONTR_ID_N" = "CONTR_ID_N") AND ("THD_ID_N" = "UADR1B"."THD_ID_N") AND ("UADR1B"."REQT_ID_N" = "REQT_ID_N"))
15. 0.001 0.065 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.71..11,707.07 rows=1 width=1,761) (actual time=0.065..0.065 rows=0 loops=1)

16. 0.000 0.064 ↓ 0.0 0 1

Nested Loop Left Join (cost=2.14..11,698.50 rows=1 width=1,581) (actual time=0.064..0.064 rows=0 loops=1)

17. 0.001 0.064 ↓ 0.0 0 1

Nested Loop (cost=1.57..11,690.02 rows=1 width=103) (actual time=0.064..0.064 rows=0 loops=1)

18. 0.002 0.063 ↓ 0.0 0 1

Nested Loop (cost=1.00..11,681.49 rows=1 width=87) (actual time=0.063..0.063 rows=0 loops=1)

19. 0.021 0.021 ↑ 1.0 1 1

Index Scan using "DGDCTRS02" on "DGDCTR" "DGDCTR_1" (cost=0.43..8.45 rows=1 width=26) (actual time=0.020..0.021 rows=1 loops=1)

  • Index Cond: ((rtrim(("EGBU_C")::text) = 'CBAC'::text) AND (rtrim(("CONTR_N")::text) = '0000001008'::text))
20. 0.040 0.040 ↓ 0.0 0 1

Index Scan using "UADR1BS11" on "UADR1B" "UADR1B_1" (cost=0.57..11,673.04 rows=1 width=61) (actual time=0.040..0.040 rows=0 loops=1)

  • Index Cond: ("CONTR_ID_N" = "DGDCTR_1"."CONTR_ID_N")
  • Filter: ((rtrim(("SING_BR_REQT_TYP_C")::text) = 'CDR'::text) AND (rtrim(("CUR_REQT_STATE_C")::text) = ANY ('{02,03,04,05}'::text[])))
  • Rows Removed by Filter: 48
21. 0.000 0.000 ↓ 0.0 0

Index Scan using "UADCDRU01" on "UADCDR" "UADCDR_1" (cost=0.57..8.53 rows=1 width=32) (never executed)

  • Index Cond: (("CONTR_ID_N" = "UADR1B_1"."CONTR_ID_N") AND ("THD_ID_N" = "UADR1B_1"."THD_ID_N") AND ("REQT_ID_N" = "UADR1B_1"."REQT_ID_N"))
  • Filter: (rtrim(("CRD_DC_REQT_TYP_C")::text) = ANY ('{CLR,COR,LRP}'::text[]))
22. 0.000 0.000 ↓ 0.0 0

Index Scan using "UADEIBU01" on "UADEIB" "UADEIB_1" (cost=0.57..8.48 rows=1 width=1,490) (never executed)

  • Index Cond: (("UADR1B_1"."CONTR_ID_N" = "CONTR_ID_N") AND ("UADR1B_1"."THD_ID_N" = "THD_ID_N") AND ("UADR1B_1"."REQT_ID_N" = "REQT_ID_N"))
23. 0.000 0.000 ↓ 0.0 0

Index Scan using "UADCLDS01" on "UADCLD" "UADCLD_1" (cost=0.57..8.55 rows=1 width=12) (never executed)

  • Index Cond: (("UADR1B_1"."CONTR_ID_N" = "CONTR_ID_N") AND ("UADR1B_1"."THD_ID_N" = "THD_ID_N"))
  • Filter: ("UADR1B_1"."REQT_ID_N" = "REQT_ID_N")