explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fFHc

Settings
# exclusive inclusive rows x rows loops node
1. 0.264 6,740.108 ↑ 1.0 71 1

Sort (cost=254,276.78..254,276.96 rows=74 width=665) (actual time=6,740.098..6,740.108 rows=71 loops=1)

  • Sort Key: dcpscase_t.apl_fl_dt
  • Sort Method: quicksort Memory: 87kB
2. 30.837 6,739.844 ↑ 1.0 71 1

Hash Right Join (cost=229,728.02..254,274.48 rows=74 width=665) (actual time=6,402.921..6,739.844 rows=71 loops=1)

  • Hash Cond: ((caseanly_t.dcps_case_uid = dcpscase_t_1.dcps_case_uid) AND (clminfo_t.dcps_clm_uid = caseclm_t_1.dcps_clm_uid))
3. 316.262 5,902.140 ↓ 1.9 416,487 1

Hash Join (cost=203,955.77..224,010.72 rows=213,836 width=15) (actual time=5,529.562..5,902.140 rows=416,487 loops=1)

  • Hash Cond: (clminfo_t.case_anl_uid = caseanly_t.case_anl_uid)
4. 57.277 57.277 ↑ 1.0 557,454 1

Seq Scan on clminfo_t (cost=0.00..10,268.15 rows=559,315 width=8) (actual time=0.009..57.277 rows=557,454 loops=1)

5. 129.767 5,528.601 ↓ 2.0 401,328 1

Hash (cost=200,257.13..200,257.13 rows=201,411 width=23) (actual time=5,528.601..5,528.601 rows=401,328 loops=1)

  • Buckets: 262,144 (originally 262144) Batches: 4 (originally 2) Memory Usage: 9,953kB
6. 307.580 5,398.834 ↓ 2.0 401,328 1

Hash Join (cost=143,384.98..200,257.13 rows=201,411 width=23) (actual time=4,396.125..5,398.834 rows=401,328 loops=1)

  • Hash Cond: (anlysgnr_t.case_anl_uid = caseanly_t.case_anl_uid)
7. 696.117 696.117 ↓ 1.0 413,140 1

Seq Scan on anlysgnr_t (cost=0.00..48,126.46 rows=402,822 width=11) (actual time=0.011..696.117 rows=413,140 loops=1)

  • Filter: ((sgnr_deld_pin IS NULL) AND ((sgnr_typ_cd)::text = 'EXNR'::text))
  • Rows Removed by Filter: 1,348,630
8. 97.053 4,395.137 ↓ 1.5 401,313 1

Hash (cost=138,805.39..138,805.39 rows=263,407 width=12) (actual time=4,395.137..4,395.137 rows=401,313 loops=1)

  • Buckets: 262,144 Batches: 2 Memory Usage: 10,682kB
9. 10.224 4,298.084 ↓ 1.5 401,313 1

Nested Loop (cost=137,421.90..138,805.39 rows=263,407 width=12) (actual time=2,157.191..4,298.084 rows=401,313 loops=1)

10. 293.941 2,281.295 ↓ 2,006.6 401,313 1

HashAggregate (cost=137,421.47..137,423.47 rows=200 width=4) (actual time=2,155.914..2,281.295 rows=401,313 loops=1)

  • Group Key: caseanly_t_1.case_anl_uid
11. 61.999 1,987.354 ↑ 1.0 416,445 1

Unique (cost=129,789.11..131,969.78 rows=436,135 width=16) (actual time=1,867.052..1,987.354 rows=416,445 loops=1)

12. 248.273 1,925.355 ↑ 1.0 424,007 1

Sort (cost=129,789.11..130,879.45 rows=436,135 width=16) (actual time=1,867.050..1,925.355 rows=424,007 loops=1)

  • Sort Key: clminfo_t_1.dcps_clm_uid, caseanly_t_1.stus_chgd_ts DESC
  • Sort Method: external merge Disk: 10,792kB
13. 320.194 1,677.082 ↑ 1.0 424,007 1

Hash Join (cost=63,368.08..81,480.44 rows=436,135 width=16) (actual time=1,269.814..1,677.082 rows=424,007 loops=1)

  • Hash Cond: (clminfo_t_1.case_anl_uid = caseanly_t_1.case_anl_uid)
14. 88.025 88.025 ↑ 1.0 557,454 1

Seq Scan on clminfo_t clminfo_t_1 (cost=0.00..10,268.15 rows=559,315 width=8) (actual time=0.005..88.025 rows=557,454 loops=1)

15. 99.975 1,268.863 ↑ 1.0 408,623 1

Hash (cost=56,227.18..56,227.18 rows=410,792 width=12) (actual time=1,268.863..1,268.863 rows=408,623 loops=1)

  • Buckets: 262,144 Batches: 2 Memory Usage: 11,636kB
16. 1,168.888 1,168.888 ↑ 1.0 408,623 1

Seq Scan on caseanly_t caseanly_t_1 (cost=0.00..56,227.18 rows=410,792 width=12) (actual time=0.661..1,168.888 rows=408,623 loops=1)

  • Filter: ((anl_stus_cd)::text = 'CLSD'::text)
  • Rows Removed by Filter: 116,628
17. 2,006.565 2,006.565 ↑ 1.0 1 401,313

Index Scan using ixpk_caseanly on caseanly_t (cost=0.42..8.20 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=401,313)

  • Index Cond: (case_anl_uid = caseanly_t_1.case_anl_uid)
18. 0.160 767.959 ↑ 1.0 71 1

Hash (cost=25,771.14..25,771.14 rows=74 width=513) (actual time=767.959..767.959 rows=71 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 36kB
19. 0.120 767.799 ↑ 1.0 71 1

Nested Loop Left Join (cost=3,674.38..25,771.14 rows=74 width=513) (actual time=277.153..767.799 rows=71 loops=1)

20. 0.093 725.502 ↑ 1.1 51 1

Nested Loop Left Join (cost=3,673.96..25,702.12 rows=57 width=509) (actual time=277.145..725.502 rows=51 loops=1)

  • Join Filter: ((dcpscase_t.adjulvl_cd)::text = '2'::text)
  • Rows Removed by Join Filter: 16
21. 0.077 683.436 ↑ 1.1 51 1

Nested Loop Left Join (cost=3,673.53..25,631.62 rows=57 width=505) (actual time=275.887..683.436 rows=51 loops=1)

22. 0.067 682.747 ↑ 1.1 51 1

Nested Loop Left Join (cost=3,673.25..25,587.79 rows=57 width=409) (actual time=275.874..682.747 rows=51 loops=1)

23. 0.044 675.387 ↑ 1.1 51 1

Nested Loop Left Join (cost=3,672.97..25,520.63 rows=57 width=297) (actual time=275.352..675.387 rows=51 loops=1)

24. 0.054 634.798 ↑ 1.1 51 1

Nested Loop Left Join (cost=3,672.54..25,460.23 rows=57 width=293) (actual time=259.583..634.798 rows=51 loops=1)

25. 0.053 634.744 ↑ 1.1 51 1

Nested Loop Left Join (cost=3,672.26..25,415.67 rows=57 width=275) (actual time=259.579..634.744 rows=51 loops=1)

26. 0.059 634.691 ↑ 1.1 51 1

Nested Loop Left Join (cost=3,671.98..25,371.11 rows=57 width=256) (actual time=259.573..634.691 rows=51 loops=1)

27. 0.075 634.632 ↑ 1.1 51 1

Nested Loop Left Join (cost=3,671.69..25,324.34 rows=57 width=256) (actual time=259.568..634.632 rows=51 loops=1)

28. 0.117 634.557 ↑ 1.1 51 1

Nested Loop (cost=3,671.40..25,215.81 rows=57 width=252) (actual time=259.562..634.557 rows=51 loops=1)

  • Join Filter: ((dcpscase_t.adjulvl_cd)::text = (adjlvl_t.cd)::text)
  • Rows Removed by Join Filter: 204
29. 0.010 0.010 ↑ 1.0 5 1

Index Scan using ixpk_adjlvl_t on adjlvl_t (cost=0.13..12.21 rows=5 width=17) (actual time=0.007..0.010 rows=5 loops=1)

30. 0.213 634.430 ↑ 1.1 51 5

Materialize (cost=3,671.27..25,199.47 rows=57 width=237) (actual time=50.317..126.886 rows=51 loops=5)

31. 0.049 634.217 ↑ 1.1 51 1

Nested Loop (cost=3,671.27..25,199.18 rows=57 width=237) (actual time=251.577..634.217 rows=51 loops=1)

32. 0.807 0.807 ↑ 1.0 1 1

Seq Scan on substuscd_t (cost=0.00..1.52 rows=1 width=20) (actual time=0.801..0.807 rows=1 loops=1)

  • Filter: ((sub_stus_cd)::text = 'PRCT'::text)
  • Rows Removed by Filter: 41
33. 0.078 633.361 ↑ 1.1 51 1

Nested Loop Left Join (cost=3,671.27..25,197.09 rows=57 width=221) (actual time=250.769..633.361 rows=51 loops=1)

34. 0.102 608.650 ↑ 1.1 51 1

Nested Loop (cost=3,670.84..25,128.93 rows=57 width=154) (actual time=249.291..608.650 rows=51 loops=1)

35. 0.331 520.216 ↑ 19.5 51 1

Nested Loop (cost=3,670.41..14,344.39 rows=993 width=133) (actual time=247.269..520.216 rows=51 loops=1)

36. 415.008 486.021 ↑ 19.5 51 1

Bitmap Heap Scan on caseclm_t (cost=3,669.98..6,988.99 rows=993 width=54) (actual time=246.168..486.021 rows=51 loops=1)

  • Recheck Cond: (((clm_sub_stus_cd)::text = 'PRCT'::text) AND ((juris_ocd)::text = 'S38'::text))
  • Heap Blocks: exact=41
37. 0.408 71.013 ↓ 0.0 0 1

BitmapAnd (cost=3,669.98..3,669.98 rows=993 width=0) (actual time=71.012..71.013 rows=0 loops=1)

38. 27.351 27.351 ↓ 3.8 14,122 1

Bitmap Index Scan on ixf03_clmsubstuscd (cost=0.00..76.55 rows=3,750 width=0) (actual time=27.351..27.351 rows=14,122 loops=1)

  • Index Cond: ((clm_sub_stus_cd)::text = 'PRCT'::text)
39. 43.254 43.254 ↓ 1.0 188,059 1

Bitmap Index Scan on ixf04_jurisocd (cost=0.00..3,592.69 rows=186,168 width=0) (actual time=43.254..43.254 rows=188,059 loops=1)

  • Index Cond: ((juris_ocd)::text = 'S38'::text)
40. 33.864 33.864 ↑ 1.0 1 51

Index Scan using ixpk_dcpscase_t on dcpscase_t (cost=0.42..7.41 rows=1 width=79) (actual time=0.664..0.664 rows=1 loops=51)

  • Index Cond: (dcps_case_uid = caseclm_t.dcps_case_uid)
41. 88.332 88.332 ↑ 1.0 1 51

Index Scan using ix_todoitemlist_t_dcps_case_uid on todoitemlist_t (cost=0.43..10.85 rows=1 width=29) (actual time=1.293..1.732 rows=1 loops=51)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
  • Filter: ((todo_itm_cmpld_dt IS NULL) AND ((todo_itm_cd)::text = 'PRCT'::text))
  • Rows Removed by Filter: 1
42. 24.633 24.633 ↑ 1.0 1 51

Index Scan using ixf01_clntaddr on clntaddr_t (cost=0.43..1.19 rows=1 width=71) (actual time=0.482..0.483 rows=1 loops=51)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
  • Filter: ((addr_subtyp)::text = 'R'::text)
  • Rows Removed by Filter: 1
43. 0.000 0.000 ↓ 0.0 0 51

Index Scan using ixpk_userorganization_t on userorganization_t (cost=0.29..1.90 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=51)

  • Index Cond: (user_org_uid = caseclm_t.rcptd_user_org_uid)
44. 0.000 0.000 ↓ 0.0 0 51

Index Scan using ixpk_userorganization_t on userorganization_t userorganization_t_1 (cost=0.29..0.82 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=51)

  • Index Cond: (user_org_uid = dcpscase_t.asgnd_user_org_uid)
45. 0.000 0.000 ↓ 0.0 0 51

Index Scan using ixpk_dcpsuser_t on dcpsuser_t (cost=0.28..0.78 rows=1 width=27) (actual time=0.000..0.000 rows=0 loops=51)

  • Index Cond: (user_uid = userorganization_t.user_uid)
46. 0.000 0.000 ↓ 0.0 0 51

Index Scan using ixpk_dcpsuser_t on dcpsuser_t dcpsuser_t_1 (cost=0.28..0.78 rows=1 width=26) (actual time=0.000..0.000 rows=0 loops=51)

  • Index Cond: (user_uid = userorganization_t_1.user_uid)
47. 40.545 40.545 ↑ 1.0 1 51

Index Scan using ixpk_allgn_t on allgn_t (cost=0.42..1.06 rows=1 width=8) (actual time=0.795..0.795 rows=1 loops=51)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
48. 7.293 7.293 ↑ 1.0 1 51

Index Scan using ixpk_ofcd_t on ofcd_t (cost=0.28..1.18 rows=1 width=116) (actual time=0.143..0.143 rows=1 loops=51)

  • Index Cond: ((ocd)::text = (caseclm_t.origg_ocd)::text)
49. 0.612 0.612 ↑ 1.0 1 51

Index Scan using ixpk_ofcd_t on ofcd_t ofcd_t_1 (cost=0.28..0.77 rows=1 width=100) (actual time=0.012..0.012 rows=1 loops=51)

  • Index Cond: ((ocd)::text = (dcpscase_t.fo_cntct_ocd)::text)
50. 41.973 41.973 ↑ 1.0 1 51

Index Scan using ixn01_dcpscase_t on dcpscase_t dcpscase_t_1 (cost=0.42..1.22 rows=1 width=8) (actual time=0.821..0.823 rows=1 loops=51)

  • Index Cond: (fldr_num = dcpscase_t.fldr_num)
  • Filter: ((adjulvl_cd)::text = '1'::text)
  • Rows Removed by Filter: 1
51. 42.177 42.177 ↑ 1.0 1 51

Index Scan using ixf01_caseclm on caseclm_t caseclm_t_1 (cost=0.42..1.20 rows=1 width=8) (actual time=0.779..0.827 rows=1 loops=51)

  • Index Cond: (dcps_case_uid = dcpscase_t_1.dcps_case_uid)
52.          

SubPlan (for Hash Right Join)

53. 0.355 29.394 ↑ 1.0 1 71

Result (cost=12.42..12.43 rows=1 width=32) (actual time=0.414..0.414 rows=1 loops=71)

54.          

Initplan (for Result)

55. 0.711 29.039 ↑ 2.0 1 71

Hash Join (cost=8.71..12.42 rows=2 width=32) (actual time=0.404..0.409 rows=1 loops=71)

  • Hash Cond: ((splhdgcd_t.splhdg_cd)::text = (cssplhdg_t.splhdg_cd)::text)
56. 0.496 0.496 ↑ 1.0 55 62

Seq Scan on splhdgcd_t (cost=0.00..3.55 rows=55 width=16) (actual time=0.002..0.008 rows=55 loops=62)

57. 0.142 27.832 ↑ 2.0 1 71

Hash (cost=8.68..8.68 rows=2 width=3) (actual time=0.392..0.392 rows=1 loops=71)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
58. 27.690 27.690 ↑ 2.0 1 71

Index Scan using ix_cssplhdg_t_dcps_case_uid on cssplhdg_t (cost=0.42..8.68 rows=2 width=3) (actual time=0.382..0.390 rows=1 loops=71)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
59. 0.071 0.213 ↑ 1.0 1 71

Result (cost=8.68..8.69 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=71)

60.          

Initplan (for Result)

61. 0.142 0.142 ↑ 2.0 1 71

Index Scan using ix_cssplhdg_t_dcps_case_uid on cssplhdg_t cssplhdg_t_1 (cost=0.42..8.68 rows=2 width=3) (actual time=0.002..0.002 rows=1 loops=71)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
62. 0.071 8.520 ↑ 1.0 1 71

Result (cost=8.31..8.32 rows=1 width=32) (actual time=0.120..0.120 rows=1 loops=71)

63.          

Initplan (for Result)

64. 8.449 8.449 ↑ 1.0 1 71

Index Scan using ixf02_csrcpisu on csrcpisu_t (cost=0.29..8.31 rows=1 width=5) (actual time=0.119..0.119 rows=1 loops=71)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
65. 0.213 0.781 ↑ 1.0 1 71

Result (cost=9.57..9.58 rows=1 width=32) (actual time=0.011..0.011 rows=1 loops=71)

66.          

Initplan (for Result)

67. 0.284 0.568 ↑ 1.0 1 71

Hash Join (cost=8.32..9.57 rows=1 width=22) (actual time=0.006..0.008 rows=1 loops=71)

  • Hash Cond: ((rcptisucd_t.rcpt_isu_cd)::text = (csrcpisu_t_1.rcpt_isu_cd)::text)
68. 0.142 0.142 ↑ 1.0 17 71

Seq Scan on rcptisucd_t (cost=0.00..1.17 rows=17 width=27) (actual time=0.001..0.002 rows=17 loops=71)

69. 0.071 0.142 ↑ 1.0 1 71

Hash (cost=8.31..8.31 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=71)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
70. 0.071 0.071 ↑ 1.0 1 71

Index Scan using ixf02_csrcpisu on csrcpisu_t csrcpisu_t_1 (cost=0.29..8.31 rows=1 width=5) (actual time=0.001..0.001 rows=1 loops=71)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
Planning time : 261.768 ms
Execution time : 6,745.753 ms