explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JqSO

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 1,274.402 10,583.788 ↑ 167.4 1,222,613 1

Merge Right Join (cost=5,442,016.64..35,644,849.18 rows=204,724,336 width=360) (actual time=8,151.959..10,583.788 rows=1,222,613 loops=1)

  • Merge Cond: ((rtrim(("IBDWRD"."ID_C_A")::text)) = (concat('DECISION', 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 rtrim(("UADLIM"."PRIM_CRD_LIM_DC_C")::text) 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 rtrim(("UADCLD"."CRD_LIM_DC_C")::text) ELSE NULL::text END)))
2. 332.038 405.632 ↑ 12.4 4,460 1

Sort (cost=41,244.24..41,382.16 rows=55,166 width=17) (actual time=403.498..405.632 rows=4,460 loops=1)

  • Sort Key: (rtrim(("IBDWRD"."ID_C_A")::text))
  • Sort Method: external merge Disk: 1992kB
3. 3.486 73.594 ↑ 1.1 52,371 1

Gather (cost=1,000.00..36,899.51 rows=55,166 width=17) (actual time=0.281..73.594 rows=52,371 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 70.108 70.108 ↑ 1.3 17,457 3

Parallel Seq Scan on "IBDWRD" (cost=0.00..30,382.91 rows=22,986 width=17) (actual time=0.007..70.108 rows=17,457 loops=3)

  • Filter: (("LANG_C")::text = 'EN'::text)
  • Rows Removed by Filter: 503561
5. 611.402 8,903.754 ↓ 1.6 1,222,613 1

Materialize (cost=5,400,772.40..5,404,483.46 rows=742,212 width=340) (actual time=7,744.837..8,903.754 rows=1,222,613 loops=1)

6. 1,830.299 8,292.352 ↓ 1.6 1,222,613 1

Sort (cost=5,400,772.40..5,402,627.93 rows=742,212 width=340) (actual time=7,744.833..8,292.352 rows=1,222,613 loops=1)

  • Sort Key: (concat('DECISION', 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 rtrim(("UADLIM"."PRIM_CRD_LIM_DC_C")::text) 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 rtrim(("UADCLD"."CRD_LIM_DC_C")::text) ELSE NULL::text END))
  • Sort Method: external merge Disk: 97576kB
7. 1,781.726 6,462.053 ↓ 1.6 1,222,613 1

Hash Left Join (cost=2,634,292.09..5,095,007.26 rows=742,212 width=340) (actual time=1,383.350..6,462.053 rows=1,222,613 loops=1)

  • Hash Cond: (("UADCLD"."CONTR_ID_N" = "UADEIB"."CONTR_ID_N") AND ("UADCLD"."REQT_ID_N" = "UADEIB"."REQT_ID_N") AND ("UADCLD"."THD_ID_N" = "UADEIB"."THD_ID_N"))
8. 762.449 4,680.309 ↓ 1.6 1,222,613 1

Hash Left Join (cost=2,629,203.72..5,084,073.98 rows=742,212 width=97) (actual time=1,383.318..4,680.309 rows=1,222,613 loops=1)

  • Hash Cond: (("UADCLD"."CONTR_ID_N" = "UADR1B"."CONTR_ID_N") AND ("UADCLD"."REQT_ID_N" = "UADR1B"."REQT_ID_N") AND ("UADCLD"."THD_ID_N" = "UADR1B"."THD_ID_N"))
9. 1,939.820 3,917.733 ↓ 1.6 1,222,613 1

Hash Join (cost=2,618,269.75..5,067,295.08 rows=742,212 width=93) (actual time=1,383.168..3,917.733 rows=1,222,613 loops=1)

  • Hash Cond: (("UADCLD"."LIM_ID_N" = "UADLIM"."LIM_ID_N") AND ("UADCLD"."THD_ID_N" = "UADLIM"."THD_ID_N"))
  • Join Filter: (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 IS NOT NULL)
  • Rows Removed by Join Filter: 615
10. 610.400 678.393 ↓ 1.1 1,223,228 1

Bitmap Heap Scan on "UADCLD" (cost=26,478.13..2,436,098.40 rows=1,143,427 width=36) (actual time=77.486..678.393 rows=1,223,228 loops=1)

  • Recheck Cond: ("CONTR_ID_N" = 12562)
  • Heap Blocks: exact=55533
11. 67.993 67.993 ↓ 1.1 1,223,228 1

Bitmap Index Scan on "UADCLDS01" (cost=0.00..26,192.27 rows=1,143,427 width=0) (actual time=67.993..67.993 rows=1,223,228 loops=1)

  • Index Cond: ("CONTR_ID_N" = 12562)
12. 613.344 1,299.520 ↑ 1.0 1,223,226 1

Hash (cost=2,557,904.67..2,557,904.67 rows=1,223,530 width=73) (actual time=1,299.520..1,299.520 rows=1,223,226 loops=1)

  • Buckets: 65536 Batches: 64 Memory Usage: 1998kB
13. 636.025 686.176 ↑ 1.0 1,223,226 1

Bitmap Heap Scan on "UADLIM" (cost=28,330.93..2,557,904.67 rows=1,223,530 width=73) (actual time=52.971..686.176 rows=1,223,226 loops=1)

  • Recheck Cond: ("CONTR_ID_N" = 12562)
  • Heap Blocks: exact=19117
14. 50.151 50.151 ↑ 1.0 1,223,226 1

Bitmap Index Scan on "UADLIMU01" (cost=0.00..28,025.04 rows=1,223,530 width=0) (actual time=50.151..50.151 rows=1,223,226 loops=1)

  • Index Cond: ("CONTR_ID_N" = 12562)
15. 0.037 0.127 ↑ 82.3 48 1

Hash (cost=10,864.83..10,864.83 rows=3,951 width=16) (actual time=0.127..0.127 rows=48 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 35kB
16. 0.090 0.090 ↑ 82.3 48 1

Index Scan using "UADR1BU01" on "UADR1B" (cost=0.57..10,864.83 rows=3,951 width=16) (actual time=0.026..0.090 rows=48 loops=1)

  • Index Cond: ("CONTR_ID_N" = 12562)
17. 0.005 0.018 ↑ 532.7 3 1

Hash (cost=5,060.40..5,060.40 rows=1,598 width=263) (actual time=0.018..0.018 rows=3 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
18. 0.013 0.013 ↑ 532.7 3 1

Index Scan using "UADEIBU01" on "UADEIB" (cost=0.57..5,060.40 rows=1,598 width=263) (actual time=0.009..0.013 rows=3 loops=1)

  • Index Cond: ("CONTR_ID_N" = 12562)
Planning time : 1.976 ms