explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qrJB

Settings
# exclusive inclusive rows x rows loops node
1. 9.106 10,084.547 ↓ 3,271.0 3,271 1

Sort (cost=32,249.16..32,249.16 rows=1 width=688) (actual time=10,083.632..10,084.547 rows=3,271 loops=1)

  • Sort Key: claims.case_rcpdt
  • Sort Method: quicksort Memory: 1783kB
2.          

CTE claims

3. 5.301 110.415 ↓ 1.3 3,023 1

Hash Join (cost=1,789.14..3,381.13 rows=2,358 width=88) (actual time=83.199..110.415 rows=3,023 loops=1)

  • Hash Cond: (caseclm_t_1.dcps_case_uid = dcpscase_t_1.dcps_case_uid)
4. 24.197 27.485 ↑ 1.0 10,326 1

Bitmap Heap Scan on caseclm_t caseclm_t_1 (cost=207.88..1,772.30 rows=10,503 width=32) (actual time=4.334..27.485 rows=10,326 loops=1)

  • Recheck Cond: ((juris_ocd)::text = 'S22'::text)
  • Filter: ((clm_sub_stus_cd)::text = ANY ('{ASGD,CLSD,CLSP}'::text[]))
  • Rows Removed by Filter: 499
  • Heap Blocks: exact=1372
5. 3.288 3.288 ↓ 1.0 10,825 1

Bitmap Index Scan on ixf04_jurisocd (cost=0.00..205.25 rows=10,795 width=0) (actual time=3.287..3.288 rows=10,825 loops=1)

  • Index Cond: ((juris_ocd)::text = 'S22'::text)
6. 4.409 77.629 ↑ 1.0 7,753 1

Hash (cost=1,483.93..1,483.93 rows=7,787 width=60) (actual time=77.629..77.629 rows=7,753 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 796kB
7. 73.220 73.220 ↑ 1.0 7,753 1

Index Scan using ixf02_case on dcpscase_t dcpscase_t_1 (cost=0.29..1,483.93 rows=7,787 width=60) (actual time=9.326..73.220 rows=7,753 loops=1)

  • Index Cond: ((case_stus_cd)::text = '5'::text)
8. 15.842 10,075.441 ↓ 3,271.0 3,271 1

Nested Loop Left Join (cost=28,174.35..28,868.02 rows=1 width=688) (actual time=256.097..10,075.441 rows=3,271 loops=1)

  • Join Filter: (clminfo_t.dcps_clm_uid = caseclm_t.dcps_clm_uid)
  • Rows Removed by Join Filter: 520
9. 5.911 776.501 ↓ 3,271.0 3,271 1

Nested Loop Left Join (cost=23,345.64..24,015.64 rows=1 width=1,585) (actual time=253.535..776.501 rows=3,271 loops=1)

10. 5.646 743.383 ↓ 3,023.0 3,023 1

Nested Loop Left Join (cost=23,345.35..24,015.13 rows=1 width=1,581) (actual time=253.530..743.383 rows=3,023 loops=1)

  • Join Filter: ((claims.adjulvl_cd)::text = '2'::text)
  • Rows Removed by Join Filter: 2591
11. 6.572 701.461 ↓ 3,023.0 3,023 1

Nested Loop Left Join (cost=23,345.06..24,012.07 rows=1 width=1,581) (actual time=252.907..701.461 rows=3,023 loops=1)

12. 3.110 392.589 ↓ 3,023.0 3,023 1

Nested Loop Left Join (cost=23,344.77..24,009.34 rows=1 width=1,523) (actual time=251.562..392.589 rows=3,023 loops=1)

13. 4.506 386.456 ↓ 3,023.0 3,023 1

Nested Loop Left Join (cost=23,344.63..24,009.19 rows=1 width=1,007) (actual time=250.153..386.456 rows=3,023 loops=1)

  • Join Filter: (qaclmrev_t.qa_csrev_uid = qacsrev_t.qa_csrev_uid)
  • Rows Removed by Join Filter: 141
14. 5.631 363.812 ↓ 3,023.0 3,023 1

Nested Loop Left Join (cost=23,344.35..24,008.77 rows=1 width=1,006) (actual time=250.103..363.812 rows=3,023 loops=1)

15. 3.163 355.158 ↓ 3,023.0 3,023 1

Nested Loop Left Join (cost=23,344.07..24,000.04 rows=1 width=1,002) (actual time=250.095..355.158 rows=3,023 loops=1)

16. 2.872 315.719 ↓ 3,023.0 3,023 1

Nested Loop Left Join (cost=23,343.79..23,991.22 rows=1 width=994) (actual time=249.587..315.719 rows=3,023 loops=1)

17. 6.413 303.778 ↓ 3,023.0 3,023 1

Nested Loop (cost=23,343.51..23,990.89 rows=1 width=978) (actual time=249.102..303.778 rows=3,023 loops=1)

18. 13.847 279.227 ↓ 3,023.0 3,023 1

Hash Right Join (cost=23,343.23..23,990.39 rows=1 width=895) (actual time=248.054..279.227 rows=3,023 loops=1)

  • Hash Cond: ((clmdetn_t.dcps_clm_uid = claims.dcps_clm_uid) AND (clmdetn_t.dcps_case_uid = claims.dcps_case_uid))
19. 16.107 133.351 ↓ 1.1 33,000 1

Unique (cost=23,251.02..23,394.83 rows=28,762 width=1,469) (actual time=108.372..133.351 rows=33,000 loops=1)

20. 32.118 117.244 ↓ 1.2 33,491 1

Sort (cost=23,251.02..23,322.92 rows=28,762 width=1,469) (actual time=108.370..117.244 rows=33,491 loops=1)

  • Sort Key: clmdetn_t.dcps_clm_uid, clmdetn_t.insrt_ts DESC
  • Sort Method: quicksort Memory: 3562kB
21. 33.577 85.126 ↓ 1.2 33,491 1

Hash Left Join (cost=1,430.10..2,735.42 rows=28,762 width=1,469) (actual time=27.974..85.126 rows=33,491 loops=1)

  • Hash Cond: (clmdetn_t.case_anl_uid = caseanly_t_1.case_anl_uid)
  • Filter: ((clmdetn_t.case_anl_uid IS NULL) OR ((caseanly_t_1.anl_stus_cd)::text = 'CLSD'::text))
  • Rows Removed by Filter: 1402
22. 24.444 24.444 ↑ 1.0 34,893 1

Seq Scan on clmdetn_t (cost=0.00..1,213.70 rows=34,900 width=33) (actual time=0.641..24.444 rows=34,893 loops=1)

  • Filter: (detn_typ_cd IS NOT NULL)
  • Rows Removed by Filter: 177
23. 12.056 27.105 ↑ 1.0 36,182 1

Hash (cost=977.82..977.82 rows=36,182 width=9) (actual time=27.105..27.105 rows=36,182 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 1961kB
24. 15.049 15.049 ↑ 1.0 36,182 1

Seq Scan on caseanly_t caseanly_t_1 (cost=0.00..977.82 rows=36,182 width=9) (actual time=0.616..15.049 rows=36,182 loops=1)

25. 2.655 132.029 ↓ 3,023.0 3,023 1

Hash (cost=92.20..92.20 rows=1 width=878) (actual time=132.029..132.029 rows=3,023 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 488kB
26. 2.355 129.374 ↓ 3,023.0 3,023 1

Hash Join (cost=17.63..92.20 rows=1 width=878) (actual time=85.477..129.374 rows=3,023 loops=1)

  • Hash Cond: (userorganization_t.org_uid = organization_t.org_uid)
27. 2.379 125.742 ↓ 51.2 3,023 1

Nested Loop (cost=1.39..75.80 rows=59 width=874) (actual time=84.184..125.742 rows=3,023 loops=1)

28. 2.663 117.317 ↓ 51.2 3,023 1

Hash Join (cost=1.11..54.60 rows=59 width=866) (actual time=83.691..117.317 rows=3,023 loops=1)

  • Hash Cond: ((claims.adjulvl_cd)::text = (adjlvl_t.cd)::text)
29. 114.212 114.212 ↓ 1.3 3,023 1

CTE Scan on claims (cost=0.00..47.16 rows=2,358 width=350) (actual time=83.204..114.212 rows=3,023 loops=1)

30. 0.009 0.442 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=524) (actual time=0.441..0.442 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
31. 0.433 0.433 ↑ 1.0 5 1

Seq Scan on adjlvl_t (cost=0.00..1.05 rows=5 width=524) (actual time=0.431..0.433 rows=5 loops=1)

32. 6.046 6.046 ↑ 1.0 1 3,023

Index Scan using ixpk_userorganization_t on userorganization_t (cost=0.28..0.36 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=3,023)

  • Index Cond: (user_org_uid = claims.asgnd_user_org_uid)
33. 0.009 1.277 ↑ 1.0 8 1

Hash (cost=16.14..16.14 rows=8 width=12) (actual time=1.277..1.277 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
34. 1.268 1.268 ↑ 1.0 8 1

Seq Scan on organization_t (cost=0.00..16.14 rows=8 width=12) (actual time=1.248..1.268 rows=8 loops=1)

  • Filter: (org_uid = ANY ('{303,354,355,366,364,368,370,453}'::integer[]))
  • Rows Removed by Filter: 399
35. 18.138 18.138 ↑ 1.0 1 3,023

Index Scan using ixpk_ofcd_t on ofcd_t (cost=0.28..0.50 rows=1 width=87) (actual time=0.006..0.006 rows=1 loops=3,023)

  • Index Cond: ((ocd)::text = (claims."originatingOfficeCd")::text)
36. 9.069 9.069 ↑ 1.0 1 3,023

Index Scan using ixpk_dcpsuser_t on dcpsuser_t (cost=0.28..0.33 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=3,023)

  • Index Cond: (user_uid = userorganization_t.user_uid)
37. 36.068 36.276 ↓ 0.0 0 3,023

Index Scan using ix_qasamp_t_dcps_case_uid on qasamp_t (cost=0.28..8.82 rows=1 width=12) (actual time=0.012..0.012 rows=0 loops=3,023)

  • Index Cond: (dcps_case_uid = claims.dcps_case_uid)
  • Filter: ((qa_rev_cd IS NOT NULL) AND ((qa_rev_cd)::text = 'FDQA'::text) AND (qasamp_uid = (SubPlan 6)))
  • Rows Removed by Filter: 0
38.          

SubPlan (forIndex Scan)

39. 0.048 0.208 ↑ 1.0 1 16

Limit (cost=8.31..8.32 rows=1 width=12) (actual time=0.011..0.013 rows=1 loops=16)

40. 0.096 0.160 ↑ 1.0 1 16

Sort (cost=8.31..8.32 rows=1 width=12) (actual time=0.010..0.010 rows=1 loops=16)

  • Sort Key: qasamp_t_1.lu_ts DESC
  • Sort Method: quicksort Memory: 25kB
41. 0.064 0.064 ↑ 1.0 1 16

Index Scan using ix_qasamp_t_dcps_case_uid on qasamp_t qasamp_t_1 (cost=0.28..8.30 rows=1 width=12) (actual time=0.003..0.004 rows=1 loops=16)

  • Index Cond: (dcps_case_uid = claims.dcps_case_uid)
  • Filter: ((qa_rev_cd IS NOT NULL) AND ((qa_rev_cd)::text = 'FDQA'::text))
  • Rows Removed by Filter: 0
42. 2.906 3.023 ↓ 0.0 0 3,023

Index Scan using ixf01_qacsrev_t on qacsrev_t (cost=0.28..8.71 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=3,023)

  • Index Cond: (qasamp_uid = qasamp_t.qasamp_uid)
  • Filter: (qa_csrev_uid = (SubPlan 7))
43.          

SubPlan (forIndex Scan)

44. 0.026 0.117 ↑ 1.0 1 13

Limit (cost=8.31..8.31 rows=1 width=12) (actual time=0.008..0.009 rows=1 loops=13)

45. 0.039 0.091 ↑ 1.0 1 13

Sort (cost=8.31..8.31 rows=1 width=12) (actual time=0.007..0.007 rows=1 loops=13)

  • Sort Key: qacsrev_t_1.lu_ts DESC
  • Sort Method: quicksort Memory: 25kB
46. 0.052 0.052 ↑ 1.0 1 13

Index Scan using ixf01_qacsrev_t on qacsrev_t qacsrev_t_1 (cost=0.28..8.30 rows=1 width=12) (actual time=0.003..0.004 rows=1 loops=13)

  • Index Cond: (qasamp_uid = qasamp_t.qasamp_uid)
47. 18.138 18.138 ↓ 0.0 0 3,023

Index Scan using ix_qaclmrev_t_dcps_case_uid on qaclmrev_t (cost=0.28..0.41 rows=1 width=17) (actual time=0.006..0.006 rows=0 loops=3,023)

  • Index Cond: (dcps_case_uid = claims.dcps_case_uid)
  • Filter: (dcps_clm_uid = claims.dcps_clm_uid)
  • Rows Removed by Filter: 0
48. 3.023 3.023 ↓ 0.0 0 3,023

Index Scan using ixpk_detntpcd on detntpcd_t (cost=0.13..0.15 rows=1 width=536) (actual time=0.001..0.001 rows=0 loops=3,023)

  • Index Cond: ((detn_typ_cd)::text = (clmdetn_t.detn_typ_cd)::text)
49. 302.300 302.300 ↑ 1.0 1 3,023

Index Scan using ixf01_clntaddr on clntaddr_t (cost=0.29..2.72 rows=1 width=62) (actual time=0.098..0.100 rows=1 loops=3,023)

  • Index Cond: (claims.dcps_case_uid = dcps_case_uid)
  • Filter: ((addr_subtyp)::text = 'R'::text)
  • Rows Removed by Filter: 1
50. 36.276 36.276 ↑ 1.0 1 3,023

Index Scan using ixn01_dcpscase_t on dcpscase_t (cost=0.29..3.05 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=3,023)

  • Index Cond: (claims.fldr_num = fldr_num)
  • Filter: ((adjulvl_cd)::text = '1'::text)
  • Rows Removed by Filter: 0
51. 27.207 27.207 ↓ 0.0 0 3,023

Index Scan using ixf01_caseclm on caseclm_t (cost=0.29..0.50 rows=1 width=8) (actual time=0.009..0.009 rows=0 loops=3,023)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
52. 3,659.916 9,096.651 ↓ 0.0 0 3,271

Nested Loop (cost=4,828.72..4,833.68 rows=1 width=15) (actual time=1.632..2.781 rows=0 loops=3,271)

  • Join Filter: (clminfo_t.case_anl_uid = caseanly_t_2.case_anl_uid)
  • Rows Removed by Join Filter: 5329
53. 5.335 343.455 ↓ 0.0 0 3,271

Nested Loop (cost=1.00..1.46 rows=1 width=27) (actual time=0.081..0.105 rows=0 loops=3,271)

54. 7.027 55.607 ↓ 0.0 0 3,271

Nested Loop (cost=0.58..0.73 rows=1 width=16) (actual time=0.013..0.017 rows=0 loops=3,271)

55. 22.897 22.897 ↓ 0.0 0 3,271

Index Scan using ix_caseanly_t_dcps_case_uid on caseanly_t (cost=0.29..0.40 rows=1 width=8) (actual time=0.007..0.007 rows=0 loops=3,271)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
56. 25.683 25.683 ↑ 1.0 1 1,223

Index Only Scan using ixpk_clminfo on clminfo_t (cost=0.29..0.32 rows=1 width=8) (actual time=0.021..0.021 rows=1 loops=1,223)

  • Index Cond: (case_anl_uid = caseanly_t.case_anl_uid)
  • Heap Fetches: 0
57. 282.513 282.513 ↑ 1.0 1 1,223

Index Scan using ixpk_anlysgnr on anlysgnr_t (cost=0.42..0.72 rows=1 width=11) (actual time=0.230..0.231 rows=1 loops=1,223)

  • Index Cond: (case_anl_uid = clminfo_t.case_anl_uid)
  • Filter: ((sgnr_deld_pin IS NULL) AND ((sgnr_typ_cd)::text = 'EXNR'::text))
  • Rows Removed by Filter: 4
58. 5,009.671 5,093.280 ↓ 71.7 14,347 1,215

HashAggregate (cost=4,827.72..4,829.72 rows=200 width=4) (actual time=0.080..4.192 rows=14,347 loops=1,215)

  • Group Key: caseanly_t_2.case_anl_uid
59. 13.149 83.609 ↑ 1.0 28,514 1

Unique (cost=4,323.18..4,467.33 rows=28,831 width=16) (actual time=63.420..83.609 rows=28,514 loops=1)

60. 20.390 70.460 ↑ 1.0 28,793 1

Sort (cost=4,323.18..4,395.25 rows=28,831 width=16) (actual time=63.417..70.460 rows=28,793 loops=1)

  • Sort Key: clminfo_t_1.dcps_clm_uid, caseanly_t_2.stus_chgd_ts DESC
  • Sort Method: quicksort Memory: 2118kB
61. 18.701 50.070 ↑ 1.0 28,793 1

Hash Join (cost=1,428.66..2,187.47 rows=28,831 width=16) (actual time=21.157..50.070 rows=28,793 loops=1)

  • Hash Cond: (clminfo_t_1.case_anl_uid = caseanly_t_2.case_anl_uid)
62. 10.262 10.262 ↑ 1.0 36,182 1

Seq Scan on clminfo_t clminfo_t_1 (cost=0.00..663.82 rows=36,182 width=8) (actual time=0.008..10.262 rows=36,182 loops=1)

63. 8.755 21.107 ↑ 1.0 28,793 1

Hash (cost=1,068.28..1,068.28 rows=28,831 width=12) (actual time=21.107..21.107 rows=28,793 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1606kB
64. 12.352 12.352 ↑ 1.0 28,793 1

Seq Scan on caseanly_t caseanly_t_2 (cost=0.00..1,068.28 rows=28,831 width=12) (actual time=0.006..12.352 rows=28,793 loops=1)

  • Filter: ((anl_stus_cd)::text = 'CLSD'::text)
  • Rows Removed by Filter: 7389
65.          

SubPlan (forNested Loop Left Join)

66. 13.084 176.634 ↑ 1.0 1 3,271

Result (cost=10.17..10.18 rows=1 width=32) (actual time=0.054..0.054 rows=1 loops=3,271)

67.          

Initplan (forResult)

68. 24.146 163.550 ↓ 0.0 0 3,271

Hash Join (cost=8.51..10.17 rows=2 width=32) (actual time=0.046..0.050 rows=0 loops=3,271)

  • Hash Cond: ((splhdgcd_t.splhdg_cd)::text = (cssplhdg_t.splhdg_cd)::text)
69. 15.106 15.106 ↑ 1.0 51 1,162

Seq Scan on splhdgcd_t (cost=0.00..1.51 rows=51 width=15) (actual time=0.002..0.013 rows=51 loops=1,162)

70. 3.271 124.298 ↓ 0.0 0 3,271

Hash (cost=8.49..8.49 rows=2 width=3) (actual time=0.038..0.038 rows=0 loops=3,271)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
71. 121.027 121.027 ↓ 0.0 0 3,271

Index Scan using ix_cssplhdg_t_dcps_case_uid on cssplhdg_t (cost=0.29..8.49 rows=2 width=3) (actual time=0.036..0.037 rows=0 loops=3,271)

  • Index Cond: (dcps_case_uid = claims.dcps_case_uid)
72. 3.271 9.813 ↑ 1.0 1 3,271

Result (cost=8.49..8.50 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=3,271)

73.          

Initplan (forResult)

74. 6.542 6.542 ↓ 0.0 0 3,271

Index Scan using ix_cssplhdg_t_dcps_case_uid on cssplhdg_t cssplhdg_t_1 (cost=0.29..8.49 rows=2 width=3) (actual time=0.001..0.002 rows=0 loops=3,271)

  • Index Cond: (dcps_case_uid = claims.dcps_case_uid)