explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Rp23

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 3,657.178 ↑ 2.0 41 1

Sort (cost=212,805.66..212,805.87 rows=82 width=662) (actual time=3,657.174..3,657.178 rows=41 loops=1)

  • Sort Key: dcpscase_t.apl_fl_dt
  • Sort Method: quicksort Memory: 53kB
2. 37.363 3,676.876 ↑ 2.0 41 1

Gather (cost=209,394.38..212,803.06 rows=82 width=662) (actual time=3,645.836..3,676.876 rows=41 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
3. 1.588 3,638.529 ↑ 2.4 20 2 / 2

Parallel Hash Left Join (cost=208,394.38..208,594.30 rows=48 width=517) (actual time=3,637.215..3,638.529 rows=20 loops=2)

  • Hash Cond: ((dcpscase_t_1.dcps_case_uid = caseanly_t.dcps_case_uid) AND (caseclm_t_1.dcps_clm_uid = clminfo_t.dcps_clm_uid))
4. 0.025 13.264 ↑ 2.4 20 2 / 2

Nested Loop Left Join (cost=15,147.59..15,347.13 rows=48 width=510) (actual time=12.885..13.264 rows=20 loops=2)

5. 0.028 13.206 ↑ 2.4 16 2 / 2

Nested Loop Left Join (cost=15,147.16..15,321.46 rows=38 width=506) (actual time=12.879..13.206 rows=16 loops=2)

  • Join Filter: ((dcpscase_t.adjulvl_cd)::text = '2'::text)
  • Rows Removed by Join Filter: 10
6. 0.011 13.095 ↑ 2.4 16 2 / 2

Nested Loop Left Join (cost=15,146.74..15,294.49 rows=38 width=502) (actual time=12.865..13.095 rows=16 loops=2)

7. 0.015 13.035 ↑ 2.4 16 2 / 2

Nested Loop Left Join (cost=15,146.45..15,283.06 rows=38 width=406) (actual time=12.855..13.035 rows=16 loops=2)

8. 0.011 12.954 ↑ 2.4 16 2 / 2

Nested Loop Left Join (cost=15,146.17..15,256.00 rows=38 width=294) (actual time=12.840..12.954 rows=16 loops=2)

9. 0.012 12.877 ↑ 2.4 16 2 / 2

Nested Loop Left Join (cost=15,145.75..15,236.48 rows=38 width=290) (actual time=12.826..12.877 rows=16 loops=2)

10. 0.015 12.865 ↑ 2.4 16 2 / 2

Nested Loop Left Join (cost=15,145.46..15,223.67 rows=38 width=272) (actual time=12.822..12.865 rows=16 loops=2)

11. 0.013 12.850 ↑ 2.4 16 2 / 2

Nested Loop Left Join (cost=15,145.18..15,210.86 rows=38 width=253) (actual time=12.816..12.850 rows=16 loops=2)

12. 0.015 12.837 ↑ 2.4 16 2 / 2

Nested Loop Left Join (cost=15,144.89..15,199.19 rows=38 width=253) (actual time=12.811..12.837 rows=16 loops=2)

13. 0.021 12.822 ↑ 2.4 16 2 / 2

Merge Join (cost=15,144.61..15,145.19 rows=38 width=249) (actual time=12.805..12.822 rows=16 loops=2)

  • Merge Cond: ((dcpscase_t.adjulvl_cd)::text = (adjlvl_t.cd)::text)
14. 0.060 12.789 ↑ 2.4 16 2 / 2

Sort (cost=15,143.50..15,143.59 rows=38 width=234) (actual time=12.787..12.789 rows=16 loops=2)

  • Sort Key: dcpscase_t.adjulvl_cd
  • Sort Method: quicksort Memory: 30kB
  • Worker 0: Sort Method: quicksort Memory: 28kB
15. 0.006 12.729 ↑ 2.4 16 2 / 2

Nested Loop (cost=3,569.27..15,142.50 rows=38 width=234) (actual time=12.198..12.729 rows=16 loops=2)

16. 0.020 12.640 ↑ 2.4 16 2 / 2

Nested Loop Left Join (cost=3,569.27..15,084.17 rows=38 width=218) (actual time=12.183..12.640 rows=16 loops=2)

17. 0.008 12.554 ↑ 2.4 16 2 / 2

Nested Loop (cost=3,568.84..15,060.00 rows=38 width=155) (actual time=12.169..12.554 rows=16 loops=2)

18. 0.045 12.447 ↑ 42.8 16 2 / 2

Nested Loop (cost=3,568.41..12,340.94 rows=685 width=134) (actual time=12.154..12.447 rows=16 loops=2)

19. 6.308 12.336 ↑ 42.8 16 2 / 2

Parallel Bitmap Heap Scan on caseclm_t (cost=3,567.98..7,366.89 rows=685 width=55) (actual time=12.130..12.336 rows=16 loops=2)

  • Recheck Cond: (((clm_sub_stus_cd)::text = 'PRCT'::text) AND ((juris_ocd)::text = 'S38'::text))
  • Heap Blocks: exact=13
20. 0.066 6.028 ↓ 0.0 0 1 / 2

BitmapAnd (cost=3,567.98..3,567.98 rows=1,165 width=0) (actual time=12.056..12.056 rows=0 loops=1)

21. 0.352 0.352 ↓ 1.6 7,195 1 / 2

Bitmap Index Scan on ixf03_clmsubstuscd (cost=0.00..85.23 rows=4,374 width=0) (actual time=0.705..0.705 rows=7,195 loops=1)

  • Index Cond: ((clm_sub_stus_cd)::text = 'PRCT'::text)
22. 5.609 5.609 ↑ 1.0 183,322 1 / 2

Bitmap Index Scan on ixf04_jurisocd (cost=0.00..3,481.92 rows=186,866 width=0) (actual time=11.218..11.218 rows=183,322 loops=1)

  • Index Cond: ((juris_ocd)::text = 'S38'::text)
23. 0.066 0.066 ↑ 1.0 1 33 / 2

Index Scan using ixpk_dcpscase_t on dcpscase_t (cost=0.42..7.26 rows=1 width=79) (actual time=0.004..0.004 rows=1 loops=33)

  • Index Cond: (dcps_case_uid = caseclm_t.dcps_case_uid)
24. 0.099 0.099 ↑ 1.0 1 33 / 2

Index Scan using ixpk_todoitemlist_t on todoitemlist_t (cost=0.43..3.96 rows=1 width=29) (actual time=0.005..0.006 rows=1 loops=33)

  • 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: 2
25. 0.066 0.066 ↑ 1.0 1 33 / 2

Index Scan using clntaddr_pkey on clntaddr_t (cost=0.43..0.63 rows=1 width=67) (actual time=0.004..0.004 rows=1 loops=33)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
  • Filter: ((addr_subtyp)::text = 'R'::text)
  • Rows Removed by Filter: 1
26. 0.083 0.083 ↑ 1.0 1 33 / 2

Seq Scan on substuscd_t (cost=0.00..1.52 rows=1 width=20) (actual time=0.003..0.005 rows=1 loops=33)

  • Filter: ((sub_stus_cd)::text = 'PRCT'::text)
  • Rows Removed by Filter: 41
27. 0.006 0.012 ↑ 2.5 2 2 / 2

Sort (cost=1.11..1.12 rows=5 width=17) (actual time=0.012..0.012 rows=2 loops=2)

  • Sort Key: adjlvl_t.cd
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
28. 0.006 0.006 ↑ 1.0 5 2 / 2

Seq Scan on adjlvl_t (cost=0.00..1.05 rows=5 width=17) (actual time=0.005..0.006 rows=5 loops=2)

29. 0.000 0.000 ↓ 0.0 0 33 / 2

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

  • Index Cond: (user_org_uid = caseclm_t.rcptd_user_org_uid)
30. 0.000 0.000 ↓ 0.0 0 33 / 2

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

  • Index Cond: (user_org_uid = dcpscase_t.asgnd_user_org_uid)
31. 0.000 0.000 ↓ 0.0 0 33 / 2

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

  • Index Cond: (user_uid = userorganization_t.user_uid)
32. 0.000 0.000 ↓ 0.0 0 33 / 2

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

  • Index Cond: (user_uid = userorganization_t_1.user_uid)
33. 0.066 0.066 ↑ 1.0 1 33 / 2

Index Scan using ixpk_allgn_t on allgn_t (cost=0.42..0.51 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=33)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
34. 0.066 0.066 ↑ 1.0 1 33 / 2

Index Scan using ixpk_ofcd_t on ofcd_t (cost=0.28..0.71 rows=1 width=116) (actual time=0.004..0.004 rows=1 loops=33)

  • Index Cond: ((ocd)::text = (caseclm_t.origg_ocd)::text)
35. 0.050 0.050 ↑ 1.0 1 33 / 2

Index Scan using ixpk_ofcd_t on ofcd_t ofcd_t_1 (cost=0.28..0.30 rows=1 width=100) (actual time=0.003..0.003 rows=1 loops=33)

  • Index Cond: ((ocd)::text = (dcpscase_t.fo_cntct_ocd)::text)
36. 0.083 0.083 ↑ 1.0 1 33 / 2

Index Scan using ixn01_dcpscase_t on dcpscase_t dcpscase_t_1 (cost=0.42..0.70 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=33)

  • Index Cond: (fldr_num = dcpscase_t.fldr_num)
  • Filter: ((adjulvl_cd)::text = '1'::text)
  • Rows Removed by Filter: 0
37. 0.033 0.033 ↑ 1.0 1 33 / 2

Index Scan using ixf01_caseclm on caseclm_t caseclm_t_1 (cost=0.42..0.67 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=33)

  • Index Cond: (dcps_case_uid = dcpscase_t_1.dcps_case_uid)
38. 67.515 3,623.677 ↓ 2.3 209,010 2 / 2

Parallel Hash (cost=191,872.90..191,872.90 rows=91,593 width=15) (actual time=3,623.677..3,623.677 rows=209,010 loops=2)

  • Buckets: 524,288 (originally 262144) Batches: 1 (originally 1) Memory Usage: 25,792kB
39. 175.840 3,556.162 ↓ 2.3 209,010 2 / 2

Parallel Hash Join (cost=183,619.45..191,872.90 rows=91,593 width=15) (actual time=3,432.366..3,556.162 rows=209,010 loops=2)

  • Hash Cond: (clminfo_t.case_anl_uid = caseanly_t.case_anl_uid)
40. 24.514 24.514 ↓ 1.2 279,702 2 / 2

Parallel Seq Scan on clminfo_t (cost=0.00..6,997.23 rows=233,222 width=8) (actual time=0.009..24.514 rows=279,702 loops=2)

41. 101.871 3,355.808 ↓ 2.4 201,354 2 / 2

Parallel Hash (cost=182,549.59..182,549.59 rows=85,589 width=23) (actual time=3,355.807..3,355.808 rows=201,354 loops=2)

  • Buckets: 262,144 (originally 262144) Batches: 4 (originally 1) Memory Usage: 7,616kB
42. 158.767 3,253.937 ↓ 2.4 201,354 2 / 2

Hash Join (cost=143,077.24..182,549.59 rows=85,589 width=23) (actual time=2,898.188..3,253.937 rows=201,354 loops=2)

  • Hash Cond: (anlysgnr_t.case_anl_uid = caseanly_t.case_anl_uid)
43. 197.951 197.951 ↓ 1.2 207,258 2 / 2

Parallel Seq Scan on anlysgnr_t (cost=0.00..35,025.55 rows=171,178 width=11) (actual time=0.009..197.951 rows=207,258 loops=2)

  • Filter: ((sgnr_deld_pin IS NULL) AND ((sgnr_typ_cd)::text = 'EXNR'::text))
  • Rows Removed by Filter: 676,420
44. 96.041 2,897.219 ↓ 1.5 402,693 2 / 2

Hash (cost=138,531.21..138,531.21 rows=261,522 width=12) (actual time=2,897.219..2,897.219 rows=402,693 loops=2)

  • Buckets: 262,144 Batches: 2 Memory Usage: 10,712kB
45. 39.614 2,801.178 ↓ 1.5 402,693 2 / 2

Nested Loop (cost=137,148.22..138,531.21 rows=261,522 width=12) (actual time=1,042.954..2,801.178 rows=402,693 loops=2)

46. 256.420 1,150.792 ↓ 2,013.5 402,693 2 / 2

HashAggregate (cost=137,147.80..137,149.80 rows=200 width=4) (actual time=1,042.932..1,150.792 rows=402,693 loops=2)

  • Group Key: caseanly_t_1.case_anl_uid
47. 59.581 894.372 ↑ 1.0 417,978 2 / 2

Unique (cost=129,563.58..131,730.50 rows=433,384 width=16) (actual time=778.830..894.372 rows=417,978 loops=2)

48. 242.298 834.791 ↑ 1.0 425,572 2 / 2

Sort (cost=129,563.58..130,647.04 rows=433,384 width=16) (actual time=778.829..834.791 rows=425,572 loops=2)

  • Sort Key: clminfo_t_1.dcps_clm_uid, caseanly_t_1.stus_chgd_ts DESC
  • Sort Method: external merge Disk: 10,832kB
  • Worker 0: Sort Method: external merge Disk: 10,832kB
49. 275.615 592.493 ↑ 1.0 425,572 2 / 2

Hash Join (cost=63,494.22..81,577.88 rows=433,384 width=16) (actual time=256.906..592.493 rows=425,572 loops=2)

  • Hash Cond: (clminfo_t_1.case_anl_uid = caseanly_t_1.case_anl_uid)
50. 60.909 60.909 ↑ 1.0 559,404 2 / 2

Seq Scan on clminfo_t clminfo_t_1 (cost=0.00..10,262.34 rows=559,734 width=8) (actual time=0.010..60.909 rows=559,404 loops=2)

51. 80.789 255.969 ↓ 1.0 410,033 2 / 2

Hash (cost=56,454.04..56,454.04 rows=404,975 width=12) (actual time=255.969..255.969 rows=410,033 loops=2)

  • Buckets: 262,144 Batches: 2 Memory Usage: 11,670kB
52. 175.180 175.180 ↓ 1.0 410,033 2 / 2

Seq Scan on caseanly_t caseanly_t_1 (cost=0.00..56,454.04 rows=404,975 width=12) (actual time=0.009..175.180 rows=410,033 loops=2)

  • Filter: ((anl_stus_cd)::text = 'CLSD'::text)
  • Rows Removed by Filter: 116,915
53. 1,610.772 1,610.772 ↑ 1.0 1 805,386 / 2

Index Scan using ixpk_caseanly on caseanly_t (cost=0.42..8.20 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=805,386)

  • Index Cond: (case_anl_uid = caseanly_t_1.case_anl_uid)
54.          

SubPlan (for Gather)

55. 0.041 0.451 ↑ 1.0 1 41

Result (cost=12.42..12.43 rows=1 width=32) (actual time=0.011..0.011 rows=1 loops=41)

56.          

Initplan (for Result)

57. 0.173 0.410 ↑ 2.0 1 41

Hash Join (cost=8.71..12.42 rows=2 width=32) (actual time=0.008..0.010 rows=1 loops=41)

  • Hash Cond: ((splhdgcd_t.splhdg_cd)::text = (cssplhdg_t.splhdg_cd)::text)
58. 0.114 0.114 ↑ 1.1 52 19

Seq Scan on splhdgcd_t (cost=0.00..3.55 rows=55 width=16) (actual time=0.001..0.006 rows=52 loops=19)

59. 0.000 0.123 ↑ 2.0 1 41

Hash (cost=8.69..8.69 rows=2 width=3) (actual time=0.003..0.003 rows=1 loops=41)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
60. 0.123 0.123 ↑ 2.0 1 41

Index Scan using ix_cssplhdg_t_dcps_case_uid on cssplhdg_t (cost=0.42..8.69 rows=2 width=3) (actual time=0.003..0.003 rows=1 loops=41)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
61. 0.041 0.082 ↑ 1.0 1 41

Result (cost=8.69..8.70 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=41)

62.          

Initplan (for Result)

63. 0.041 0.041 ↑ 2.0 1 41

Index Scan using ix_cssplhdg_t_dcps_case_uid on cssplhdg_t cssplhdg_t_1 (cost=0.42..8.69 rows=2 width=3) (actual time=0.001..0.001 rows=1 loops=41)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
64. 0.041 0.123 ↑ 1.0 1 41

Result (cost=8.31..8.32 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=41)

65.          

Initplan (for Result)

66. 0.082 0.082 ↑ 1.0 1 41

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

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
67. 0.082 0.328 ↑ 1.0 1 41

Result (cost=9.57..9.58 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=41)

68.          

Initplan (for Result)

69. 0.082 0.246 ↑ 1.0 1 41

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

  • Hash Cond: ((rcptisucd_t.rcpt_isu_cd)::text = (csrcpisu_t_1.rcpt_isu_cd)::text)
70. 0.082 0.082 ↑ 1.0 17 41

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

71. 0.041 0.082 ↑ 1.0 1 41

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
72. 0.041 0.041 ↑ 1.0 1 41

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=41)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
Planning time : 25.094 ms
Execution time : 3,682.420 ms