explain.depesz.com

PostgreSQL's explain analyze made readable

Result: eZqY

Settings
# exclusive inclusive rows x rows loops node
1. 0.030 0.243 ↓ 0.0 0 1

Sort (cost=616.08..616.08 rows=1 width=1,880) (actual time=0.243..0.243 rows=0 loops=1)

  • Sort Key: ((SubPlan 3)), refrqst_t.refl_rqst_uid
  • Sort Method: quicksort Memory: 25kB
2. 0.000 0.213 ↓ 0.0 0 1

Sort (cost=616.05..616.06 rows=1 width=1,880) (actual time=0.212..0.213 rows=0 loops=1)

  • Sort Key: refrqst_t.refl_rqst_uid
  • Sort Method: quicksort Memory: 25kB
3.          

Initplan (for Sort)

4. 0.002 0.015 ↑ 1.0 1 1

Limit (cost=0.57..16.61 rows=1 width=4) (actual time=0.014..0.015 rows=1 loops=1)

5. 0.001 0.013 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.57..16.61 rows=1 width=4) (actual time=0.013..0.013 rows=1 loops=1)

6. 0.007 0.007 ↑ 1.0 1 1

Index Scan using ixpk_userorganization_t on userorganization_t userorganization_t_4 (cost=0.29..8.31 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: (user_org_uid = 225)
7. 0.005 0.005 ↑ 1.0 1 1

Index Scan using ixpk_organization_t on organization_t organization_t_3 (cost=0.28..8.30 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: (org_uid = userorganization_t_4.org_uid)
8. 0.000 0.207 ↓ 0.0 0 1

Nested Loop (cost=317.44..599.43 rows=1 width=1,880) (actual time=0.207..0.207 rows=0 loops=1)

  • Join Filter: (dcpscase_t.dcps_case_uid = dcpscase_t_1.dcps_case_uid)
9. 0.000 0.207 ↓ 0.0 0 1

Nested Loop (cost=5.65..211.57 rows=1 width=1,723) (actual time=0.207..0.207 rows=0 loops=1)

  • Join Filter: (NOT (SubPlan 8))
10. 0.001 0.207 ↓ 0.0 0 1

Nested Loop (cost=5.08..164.30 rows=1 width=1,727) (actual time=0.207..0.207 rows=0 loops=1)

11. 0.003 0.176 ↑ 1.4 5 1

Nested Loop (cost=4.80..162.08 rows=7 width=1,731) (actual time=0.135..0.176 rows=5 loops=1)

12. 0.001 0.163 ↑ 1.4 5 1

Nested Loop (cost=4.51..134.66 rows=7 width=1,727) (actual time=0.127..0.163 rows=5 loops=1)

13. 0.004 0.147 ↑ 1.4 5 1

Nested Loop (cost=4.24..132.58 rows=7 width=1,708) (actual time=0.118..0.147 rows=5 loops=1)

14. 0.003 0.128 ↑ 1.4 5 1

Nested Loop (cost=3.95..74.43 rows=7 width=1,704) (actual time=0.109..0.128 rows=5 loops=1)

15. 0.023 0.023 ↑ 1.0 1 1

Index Scan using pkey_prcstuscd_pkey on prcstuscd_t (cost=0.14..8.16 rows=1 width=536) (actual time=0.022..0.023 rows=1 loops=1)

  • Index Cond: ((prc_stus_cd)::text = 'INIT'::text)
16. 0.016 0.102 ↑ 1.4 5 1

Hash Join (cost=3.80..66.19 rows=7 width=1,188) (actual time=0.084..0.102 rows=5 loops=1)

  • Hash Cond: ((refrqst_t.refl_rqst_cd)::text = (refrqstcd_t.refl_rqst_cd)::text)
17. 0.002 0.059 ↑ 1.4 5 1

Nested Loop (cost=1.92..64.30 rows=7 width=672) (actual time=0.043..0.059 rows=5 loops=1)

18. 0.017 0.037 ↑ 1.4 5 1

Hash Join (cost=1.65..14.22 rows=7 width=629) (actual time=0.033..0.037 rows=5 loops=1)

  • Hash Cond: ((refevlcd_t.refl_evaln_cd)::text = (refrqst_t.refl_evaln_cd)::text)
19. 0.003 0.003 ↑ 15.0 8 1

Seq Scan on refevlcd_t (cost=0.00..11.20 rows=120 width=536) (actual time=0.002..0.003 rows=8 loops=1)

20. 0.005 0.017 ↑ 1.4 5 1

Hash (cost=1.56..1.56 rows=7 width=113) (actual time=0.017..0.017 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.012 0.012 ↓ 1.1 8 1

Seq Scan on refrqst_t (cost=0.00..1.56 rows=7 width=113) (actual time=0.005..0.012 rows=8 loops=1)

  • Filter: ((refl_stus_cd)::text = 'INIT'::text)
  • Rows Removed by Filter: 37
22. 0.020 0.020 ↑ 1.0 1 5

Index Scan using ixpk_dcpscase_t on dcpscase_t (cost=0.28..7.15 rows=1 width=43) (actual time=0.004..0.004 rows=1 loops=5)

  • Index Cond: (dcps_case_uid = refrqst_t.dcps_case_uid)
  • Filter: ((case_stus_cd)::text <> '4'::text)
23. 0.018 0.027 ↑ 1.0 39 1

Hash (cost=1.39..1.39 rows=39 width=536) (actual time=0.027..0.027 rows=39 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
24. 0.009 0.009 ↑ 1.0 39 1

Seq Scan on refrqstcd_t (cost=0.00..1.39 rows=39 width=536) (actual time=0.005..0.009 rows=39 loops=1)

25. 0.015 0.015 ↑ 1.0 1 5

Index Scan using ixpk_userorganization_t on userorganization_t (cost=0.29..8.31 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=5)

  • Index Cond: (user_org_uid = refrqst_t.rqstd_by_user_org_uid)
26. 0.015 0.015 ↑ 1.0 1 5

Index Scan using ixpk_dcpsuser_t on dcpsuser_t (cost=0.28..0.30 rows=1 width=23) (actual time=0.003..0.003 rows=1 loops=5)

  • Index Cond: (user_uid = userorganization_t.user_uid)
27. 0.010 0.010 ↑ 1.0 1 5

Index Scan using ixpk_userorganization_t on userorganization_t userorganization_t_1 (cost=0.29..3.92 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=5)

  • Index Cond: (user_org_uid = dcpscase_t.asgnd_user_org_uid)
28. 0.030 0.030 ↓ 0.0 0 5

Index Scan using ixpk_organization_t on organization_t (cost=0.28..0.32 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=5)

  • Index Cond: (org_uid = userorganization_t_1.org_uid)
  • Filter: (site_org_uid = $11)
  • Rows Removed by Filter: 1
29. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.56..16.62 rows=1 width=7) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Index Scan using ixpk_userorganization_t on userorganization_t userorganization_t_2 (cost=0.29..8.31 rows=1 width=4) (never executed)

  • Index Cond: (user_org_uid = 225)
31. 0.000 0.000 ↓ 0.0 0

Index Scan using ixpk_dcpsuser_t on dcpsuser_t dcpsuser_t_1 (cost=0.28..8.29 rows=1 width=11) (never executed)

  • Index Cond: (user_uid = userorganization_t_2.user_uid)
32.          

SubPlan (for Nested Loop)

33. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=29.89..31.14 rows=100 width=32) (never executed)

  • Group Key: unnest($22)
34.          

Initplan (for HashAggregate)

35. 0.000 0.000 ↓ 0.0 0

Subquery Scan on x (cost=29.09..29.12 rows=1 width=32) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=29.09..29.10 rows=1 width=64) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=5.29..29.08 rows=1 width=35) (never executed)

  • Filter: (((anlysgnr_t.sgnr_pin IS NOT NULL) AND (anlysgnr_t.sgnr_deld_pin IS NULL) AND (((anlysgnr_t.sgnr_typ_cd)::text = ANY ('{EXNR,OMED}'::text[])) OR (((asmt_t.asmt
38. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=5.15..28.51 rows=2 width=59) (never executed)

  • Join Filter: (asmt_t_1.case_anl_uid = caseanly_t.case_anl_uid)
39. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=5.01..28.12 rows=2 width=58) (never executed)

40. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=4.86..19.97 rows=1 width=50) (never executed)

41. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=4.72..19.69 rows=1 width=41) (never executed)

  • Join Filter: (anlysgnr_t_1.case_anl_uid = caseanly_t.case_anl_uid)
42. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=4.58..19.35 rows=1 width=31) (never executed)

43. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=4.44..17.67 rows=1 width=4) (never executed)

44. 0.000 0.000 ↓ 0.0 0

Index Scan using ixn01_dcpscase_t on dcpscase_t dcpscase_t_2 (cost=0.28..8.29 rows=1 width=4) (never executed)

  • Index Cond: (fldr_num = dcpscase_t.fldr_num)
  • Filter: (dcps_case_uid <> dcpscase_t.dcps_case_uid)
45. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on caseanly_t (cost=4.16..9.36 rows=1 width=8) (never executed)

  • Recheck Cond: (dcps_case_uid = dcpscase_t_2.dcps_case_uid)
  • Filter: ((deld_by_pin IS NULL) AND ((anl_stus_cd)::text = 'CLSD'::text))
46. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ixn01_caseanly_t (cost=0.00..4.16 rows=2 width=0) (never executed)

  • Index Cond: (dcps_case_uid = dcpscase_t_2.dcps_case_uid)
47. 0.000 0.000 ↓ 0.0 0

Index Scan using ixpk_anlysgnr on anlysgnr_t (cost=0.14..1.65 rows=3 width=31) (never executed)

  • Index Cond: (case_anl_uid = caseanly_t.case_anl_uid)
48. 0.000 0.000 ↓ 0.0 0

Index Scan using ixn01_anlysgnr_t on anlysgnr_t anlysgnr_t_1 (cost=0.14..0.33 rows=1 width=26) (never executed)

  • Index Cond: (asmt_uid = anlysgnr_t.asmt_uid)
  • Filter: ((sgnr_pin IS NULL) AND (sgnr_uid <> anlysgnr_t.sgnr_uid))
49. 0.000 0.000 ↓ 0.0 0

Index Scan using ixpk_asmt on asmt_t (cost=0.14..0.27 rows=1 width=17) (never executed)

  • Index Cond: (asmt_uid = anlysgnr_t.asmt_uid)
50. 0.000 0.000 ↓ 0.0 0

Index Scan using ixf01_cde_t on cde_t (cost=0.15..8.13 rows=2 width=12) (never executed)

  • Index Cond: (case_anl_uid = caseanly_t.case_anl_uid)
51. 0.000 0.000 ↓ 0.0 0

Index Scan using ixpk_asmt on asmt_t asmt_t_1 (cost=0.14..0.18 rows=1 width=17) (never executed)

  • Index Cond: (asmt_uid = cde_t.asmt_uid)
52. 0.000 0.000 ↓ 0.0 0

Index Scan using ixf01_cdesgnr_t on cdesgnr_t (cost=0.14..0.26 rows=1 width=60) (never executed)

  • Index Cond: (cde_uid = cde_t.cde_uid)
53. 0.000 0.000 ↓ 0.0 0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (never executed)

54. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=0) (never executed)

55. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=311.79..314.78 rows=299 width=8) (never executed)

  • Group Key: dcpscase_t_1.dcps_case_uid, caseclm_t.juris_ocd
56. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=209.29..310.30 rows=299 width=8) (never executed)

  • Hash Cond: (dcpscase_t_1.dcps_case_uid = caseclm_t.dcps_case_uid)
57. 0.000 0.000 ↓ 0.0 0

Seq Scan on dcpscase_t dcpscase_t_1 (cost=0.00..94.36 rows=975 width=4) (never executed)

  • Filter: ((case_stus_cd)::text = '5'::text)
58. 0.000 0.000 ↓ 0.0 0

Hash (cost=205.50..205.50 rows=303 width=8) (never executed)

59. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=184.53..205.50 rows=303 width=8) (never executed)

60. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=184.25..184.30 rows=5 width=4) (never executed)

  • Group Key: (organization_t_2.ofc_cd)::text
61. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.57..184.24 rows=5 width=4) (never executed)

  • Join Filter: ((organization_t_1.st_cd)::text = (organization_t_2.st_cd)::text)
62. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.57..16.61 rows=1 width=3) (never executed)

63. 0.000 0.000 ↓ 0.0 0

Index Scan using ixpk_userorganization_t on userorganization_t userorganization_t_3 (cost=0.29..8.31 rows=1 width=4) (never executed)

  • Index Cond: (user_org_uid = 225)
64. 0.000 0.000 ↓ 0.0 0

Index Scan using ixpk_organization_t on organization_t organization_t_1 (cost=0.28..8.30 rows=1 width=7) (never executed)

  • Index Cond: (org_uid = userorganization_t_3.org_uid)
65. 0.000 0.000 ↓ 0.0 0

Seq Scan on organization_t organization_t_2 (cost=0.00..165.85 rows=142 width=7) (never executed)

  • Filter: ((org_typ_cd)::text = 'A'::text)
66. 0.000 0.000 ↓ 0.0 0

Index Scan using ixf04_jurisocd on caseclm_t (cost=0.28..3.63 rows=61 width=8) (never executed)

  • Index Cond: ((juris_ocd)::text = (organization_t_2.ofc_cd)::text)
  • Filter: ((clm_stus_cd)::text = '5'::text)
67.          

SubPlan (for Nested Loop)

68. 0.000 0.000 ↓ 0.0 0

Limit (cost=19.57..19.58 rows=1 width=8) (never executed)

69. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=19.57..19.58 rows=1 width=8) (never executed)

70. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=8.07..19.57 rows=1 width=0) (never executed)

71. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.00..4.36 rows=1 width=4) (never executed)

  • Join Filter: (commrcpnt_t.prc_comm_uid = prccomm_t.prc_comm_uid)
72. 0.000 0.000 ↓ 0.0 0

Seq Scan on prccomm_t (cost=0.00..1.77 rows=1 width=4) (never executed)

  • Filter: ((prnt_prc_id = refrqst_t.refl_rqst_uid) AND ((prc_typ_cd)::text = 'REFL'::text))
73. 0.000 0.000 ↓ 0.0 0

Seq Scan on commrcpnt_t (cost=0.00..2.56 rows=3 width=8) (never executed)

  • Filter: ((rcpnt_user_org_uid = refrqst_t.assgnd_to_user_org_uid) AND ((comm_rcv_sw)::text = 'Y'::text))
74. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on comrcpatvt_t (cost=8.07..15.20 rows=1 width=4) (never executed)

  • Recheck Cond: (comm_rcpnt_uid = commrcpnt_t.comm_rcpnt_uid)
  • Filter: ((comm_read_sw)::text = 'N'::text)
75. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on pkey_comrcpatvt_pkey (cost=0.00..8.07 rows=4 width=0) (never executed)

  • Index Cond: (comm_rcpnt_uid = commrcpnt_t.comm_rcpnt_uid)
76. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=13.35..13.36 rows=1 width=32) (never executed)

77. 0.000 0.000 ↓ 0.0 0

Subquery Scan on t (cost=13.17..13.32 rows=12 width=29) (never executed)

78. 0.000 0.000 ↓ 0.0 0

Sort (cost=13.17..13.20 rows=12 width=37) (never executed)

  • Sort Key: splhdgcd_t_1.sort_ord_num
79. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=9.04..12.95 rows=12 width=37) (never executed)

80. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=8.89..11.91 rows=1 width=21) (never executed)

81. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=8.75..11.45 rows=1 width=19) (never executed)

  • Hash Cond: ((splhdgcd_t.splhdg_cd)::text = (cssplhdg_t.splhdg_cd)::text)
82. 0.000 0.000 ↓ 0.0 0

Seq Scan on splhdgcd_t (cost=0.00..2.55 rows=55 width=16) (never executed)

83. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.73..8.73 rows=1 width=3) (never executed)

84. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on cssplhdg_t (cost=4.16..8.73 rows=1 width=3) (never executed)

  • Recheck Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
  • Filter: ((splhdg_cd)::text = ANY ('{QD,CL,29,31,38}'::text[]))
85. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ixf02_dcps_case_uid (cost=0.00..4.16 rows=2 width=0) (never executed)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
86. 0.000 0.000 ↓ 0.0 0

Index Scan using splhdgcd_pkey on splhdgcd_t splhdgcd_t_1 (cost=0.14..0.45 rows=1 width=5) (never executed)

  • Index Cond: ((splhdg_cd)::text = (splhdgcd_t.splhdg_cd)::text)
87. 0.000 0.000 ↓ 0.0 0

Index Scan using ixpk_splhdglbl on splhdglbl_t (cost=0.15..1.04 rows=1 width=80) (never executed)

  • Index Cond: ((splhdgcd_t.splhdg_cd)::text = (splhdg_cd)::text)
88. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=11.94..11.95 rows=1 width=32) (never executed)

89. 0.000 0.000 ↓ 0.0 0

Subquery Scan on t_1 (cost=11.92..11.94 rows=1 width=26) (never executed)

90. 0.000 0.000 ↓ 0.0 0

Limit (cost=11.92..11.93 rows=1 width=2) (never executed)

91. 0.000 0.000 ↓ 0.0 0

Sort (cost=11.92..11.93 rows=1 width=2) (never executed)

  • Sort Key: splhdgcd_t_3.sort_ord_num
92. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=8.89..11.91 rows=1 width=2) (never executed)

93. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=8.75..11.45 rows=1 width=6) (never executed)

  • Hash Cond: ((splhdgcd_t_2.splhdg_cd)::text = (cssplhdg_t_1.splhdg_cd)::text)
94. 0.000 0.000 ↓ 0.0 0

Seq Scan on splhdgcd_t splhdgcd_t_2 (cost=0.00..2.55 rows=55 width=3) (never executed)

95. 0.000 0.000 ↓ 0.0 0

Hash (cost=8.73..8.73 rows=1 width=3) (never executed)

96. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on cssplhdg_t cssplhdg_t_1 (cost=4.16..8.73 rows=1 width=3) (never executed)

  • Recheck Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
  • Filter: ((splhdg_cd)::text = ANY ('{QD,CL,29,31,38}'::text[]))
97. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ixf02_dcps_case_uid (cost=0.00..4.16 rows=2 width=0) (never executed)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
98. 0.000 0.000 ↓ 0.0 0

Index Scan using splhdgcd_pkey on splhdgcd_t splhdgcd_t_3 (cost=0.14..0.45 rows=1 width=5) (never executed)

  • Index Cond: ((splhdg_cd)::text = (splhdgcd_t_2.splhdg_cd)::text)
99. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=10.72..10.73 rows=1 width=32) (never executed)

100. 0.000 0.000 ↓ 0.0 0

Index Scan using ixf01_caseclm on caseclm_t caseclm_t_1 (cost=0.28..10.71 rows=2 width=15) (never executed)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
101. 0.000 0.000 ↓ 0.0 0

Limit (cost=10.72..10.72 rows=1 width=12) (never executed)

102. 0.000 0.000 ↓ 0.0 0

Sort (cost=10.72..10.72 rows=2 width=12) (never executed)

  • Sort Key: caseclm_t_2.clm_sub_stus_ts DESC
103. 0.000 0.000 ↓ 0.0 0

Index Scan using ixf01_caseclm on caseclm_t caseclm_t_2 (cost=0.28..10.71 rows=2 width=12) (never executed)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
Planning time : 22.752 ms
Execution time : 1.380 ms