explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ede9 : Optimization for: plan #JqSO

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1,305.560 10,511.525 ↑ 167.4 1,222,613 1

Merge Right Join (cost=5,433,454.15..35,636,286.69 rows=204,724,336 width=360) (actual time=8,020.578..10,511.525 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. 320.838 388.730 ↑ 12.4 4,460 1

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

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

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

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

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

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

Materialize (cost=5,392,209.91..5,395,920.97 rows=742,212 width=340) (actual time=7,630.165..8,817.235 rows=1,222,613 loops=1)

6. 1,849.204 8,188.862 ↓ 1.6 1,222,613 1

Sort (cost=5,392,209.91..5,394,065.44 rows=742,212 width=340) (actual time=7,630.160..8,188.862 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,765.550 6,339.658 ↓ 1.6 1,222,613 1

Hash Left Join (cost=2,633,055.22..5,086,444.78 rows=742,212 width=340) (actual time=1,416.199..6,339.658 rows=1,222,613 loops=1)

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

Hash Left Join (cost=2,627,978.85..5,077,471.79 rows=742,212 width=93) (actual time=1,416.158..4,574.089 rows=1,222,613 loops=1)

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

Hash Join (cost=2,617,074.75..5,062,671.08 rows=742,212 width=89) (actual time=1,416.054..3,927.077 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. 617.851 686.587 ↓ 1.1 1,223,228 1

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

  • Recheck Cond: ("CONTR_ID_N" = 12562)
  • Heap Blocks: exact=55533
11. 68.736 68.736 ↓ 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=68.735..68.736 rows=1,223,228 loops=1)

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

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

  • Buckets: 65536 Batches: 64 Memory Usage: 1923kB
13. 666.000 719.427 ↑ 1.0 1,223,226 1

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

  • Recheck Cond: ("CONTR_ID_N" = 12562)
  • Heap Blocks: exact=19117
14. 53.427 53.427 ↑ 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=53.427..53.427 rows=1,223,226 loops=1)

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

Hash (cost=10,844.83..10,844.83 rows=3,951 width=12) (actual time=0.068..0.069 rows=48 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 34kB
16. 0.049 0.049 ↑ 82.3 48 1

Index Scan using "UADR1BS11" on "UADR1B" (cost=0.57..10,844.83 rows=3,951 width=12) (actual time=0.017..0.049 rows=48 loops=1)

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

Hash (cost=5,052.40..5,052.40 rows=1,598 width=259) (actual time=0.019..0.019 rows=3 loops=1)

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

Index Scan using "UADEIBS01" on "UADEIB" (cost=0.57..5,052.40 rows=1,598 width=259) (actual time=0.011..0.014 rows=3 loops=1)

  • Index Cond: ("CONTR_ID_N" = 12562)