explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6FYp

Settings
# exclusive inclusive rows x rows loops node
1. 9.820 13,510.359 ↓ 3,314.0 3,314 1

Sort (cost=36,680.46..36,680.47 rows=1 width=687) (actual time=13,509.432..13,510.359 rows=3,314 loops=1)

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

CTE claims

3. 4.855 128.470 ↓ 1.2 3,043 1

Hash Join (cost=1,957.11..3,746.82 rows=2,531 width=88) (actual time=96.240..128.470 rows=3,043 loops=1)

  • Hash Cond: (caseclm_t_1.dcps_case_uid = dcpscase_t_1.dcps_case_uid)
4. 28.867 34.083 ↑ 1.0 11,308 1

Bitmap Heap Scan on caseclm_t caseclm_t_1 (cost=228.13..1,987.95 rows=11,385 width=32) (actual time=5.825..34.083 rows=11,308 loops=1)

  • Recheck Cond: ((juris_ocd)::text = 'S22'::text)
  • Filter: ((clm_sub_stus_cd)::text = ANY ('{ASGD,CLSD,CLSP}'::text[]))
  • Rows Removed by Filter: 569
  • Heap Blocks: exact=1541
5. 5.216 5.216 ↓ 1.0 11,877 1

Bitmap Index Scan on ixf04_jurisocd (cost=0.00..225.28 rows=11,866 width=0) (actual time=5.215..5.216 rows=11,877 loops=1)

  • Index Cond: ((juris_ocd)::text = 'S22'::text)
6. 5.667 89.532 ↑ 1.0 8,690 1

Hash (cost=1,620.27..1,620.27 rows=8,697 width=60) (actual time=89.532..89.532 rows=8,690 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 948kB
7. 83.865 83.865 ↑ 1.0 8,690 1

Index Scan using ixf02_case on dcpscase_t dcpscase_t_1 (cost=0.29..1,620.27 rows=8,697 width=60) (actual time=1.647..83.865 rows=8,690 loops=1)

  • Index Cond: ((case_stus_cd)::text = '5'::text)
8. 15.830 13,500.539 ↓ 3,314.0 3,314 1

Nested Loop Left Join (cost=32,158.70..32,933.63 rows=1 width=687) (actual time=323.288..13,500.539 rows=3,314 loops=1)

  • Join Filter: (clminfo_t.dcps_clm_uid = caseclm_t.dcps_clm_uid)
  • Rows Removed by Join Filter: 554
9. 7.096 2,147.515 ↓ 3,314.0 3,314 1

Nested Loop Left Join (cost=26,716.26..27,467.51 rows=1 width=1,584) (actual time=322.304..2,147.515 rows=3,314 loops=1)

10. 5.564 2,106.946 ↓ 3,043.0 3,043 1

Nested Loop Left Join (cost=26,715.97..27,467.00 rows=1 width=1,580) (actual time=322.299..2,106.946 rows=3,043 loops=1)

  • Join Filter: ((claims.adjulvl_cd)::text = '2'::text)
  • Rows Removed by Join Filter: 2567
11. 5.938 2,043.565 ↓ 3,043.0 3,043 1

Nested Loop Left Join (cost=26,715.68..27,463.88 rows=1 width=1,580) (actual time=321.867..2,043.565 rows=3,043 loops=1)

12. 5.557 1,620.736 ↓ 3,043.0 3,043 1

Nested Loop Left Join (cost=26,715.39..27,461.08 rows=1 width=1,523) (actual time=318.797..1,620.736 rows=3,043 loops=1)

13. 5.562 1,615.179 ↓ 3,043.0 3,043 1

Nested Loop Left Join (cost=26,715.25..27,460.92 rows=1 width=1,007) (actual time=318.316..1,615.179 rows=3,043 loops=1)

  • Join Filter: (qaclmrev_t.qa_csrev_uid = qacsrev_t.qa_csrev_uid)
  • Rows Removed by Join Filter: 184
14. 1,144.779 1,576.144 ↓ 3,043.0 3,043 1

Nested Loop Left Join (cost=26,714.97..27,460.47 rows=1 width=1,006) (actual time=318.272..1,576.144 rows=3,043 loops=1)

15. 4.936 428.322 ↓ 3,043.0 3,043 1

Nested Loop Left Join (cost=26,551.50..27,296.85 rows=1 width=1,002) (actual time=312.119..428.322 rows=3,043 loops=1)

16. 4.247 392.956 ↓ 3,043.0 3,043 1

Nested Loop Left Join (cost=26,551.21..27,287.98 rows=1 width=994) (actual time=311.543..392.956 rows=3,043 loops=1)

17. 4.354 379.580 ↓ 3,043.0 3,043 1

Nested Loop (cost=26,550.94..27,287.64 rows=1 width=978) (actual time=311.528..379.580 rows=3,043 loops=1)

18. 15.785 347.839 ↓ 3,043.0 3,043 1

Hash Right Join (cost=26,550.66..27,287.16 rows=1 width=895) (actual time=310.565..347.839 rows=3,043 loops=1)

  • Hash Cond: ((clmdetn_t.dcps_clm_uid = claims.dcps_clm_uid) AND (clmdetn_t.dcps_case_uid = claims.dcps_case_uid))
19. 18.959 183.667 ↓ 1.1 37,341 1

Unique (cost=26,452.81..26,616.47 rows=32,733 width=1,469) (actual time=154.306..183.667 rows=37,341 loops=1)

20. 44.211 164.708 ↓ 1.2 37,894 1

Sort (cost=26,452.81..26,534.64 rows=32,733 width=1,469) (actual time=154.303..164.708 rows=37,894 loops=1)

  • Sort Key: clmdetn_t.dcps_clm_uid, clmdetn_t.insrt_ts DESC
  • Sort Method: quicksort Memory: 3906kB
21. 50.038 120.497 ↓ 1.2 37,894 1

Hash Left Join (cost=1,597.00..3,075.09 rows=32,733 width=1,469) (actual time=37.637..120.497 rows=37,894 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: 1560
22. 34.004 34.004 ↑ 1.0 39,454 1

Seq Scan on clmdetn_t (cost=0.00..1,374.48 rows=39,464 width=33) (actual time=0.938..34.004 rows=39,454 loops=1)

  • Filter: (detn_typ_cd IS NOT NULL)
  • Rows Removed by Filter: 194
23. 12.882 36.455 ↑ 1.0 40,400 1

Hash (cost=1,092.00..1,092.00 rows=40,400 width=9) (actual time=36.455..36.455 rows=40,400 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2130kB
24. 23.573 23.573 ↑ 1.0 40,400 1

Seq Scan on caseanly_t caseanly_t_1 (cost=0.00..1,092.00 rows=40,400 width=9) (actual time=2.061..23.573 rows=40,400 loops=1)

25. 2.475 148.387 ↓ 3,043.0 3,043 1

Hash (cost=97.83..97.83 rows=1 width=878) (actual time=148.387..148.387 rows=3,043 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 491kB
26. 2.169 145.912 ↓ 3,043.0 3,043 1

Hash Join (cost=17.81..97.83 rows=1 width=878) (actual time=97.978..145.912 rows=3,043 loops=1)

  • Hash Cond: (userorganization_t.org_uid = organization_t.org_uid)
27. 1.546 142.577 ↓ 48.3 3,043 1

Nested Loop (cost=1.39..81.25 rows=63 width=874) (actual time=96.797..142.577 rows=3,043 loops=1)

28. 2.469 134.945 ↓ 48.3 3,043 1

Hash Join (cost=1.11..58.52 rows=63 width=866) (actual time=96.763..134.945 rows=3,043 loops=1)

  • Hash Cond: ((claims.adjulvl_cd)::text = (adjlvl_t.cd)::text)
29. 132.004 132.004 ↓ 1.2 3,043 1

CTE Scan on claims (cost=0.00..50.62 rows=2,531 width=350) (actual time=96.245..132.004 rows=3,043 loops=1)

30. 0.007 0.472 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=524) (actual time=0.471..0.472 rows=5 loops=1)

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

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

32. 6.086 6.086 ↑ 1.0 1 3,043

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,043)

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

Hash (cost=16.32..16.32 rows=8 width=12) (actual time=1.166..1.166 rows=8 loops=1)

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

Seq Scan on organization_t (cost=0.00..16.32 rows=8 width=12) (actual time=0.995..1.156 rows=8 loops=1)

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

Index Scan using ixpk_ofcd_t on ofcd_t (cost=0.28..0.49 rows=1 width=87) (actual time=0.009..0.009 rows=1 loops=3,043)

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

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,043)

  • Index Cond: (user_uid = userorganization_t.user_uid)
37. 30.304 30.430 ↓ 0.0 0 3,043

Index Scan using ix_qasamp_t_dcps_case_uid on qasamp_t (cost=0.29..8.86 rows=1 width=12) (actual time=0.010..0.010 rows=0 loops=3,043)

  • 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.036 0.126 ↑ 1.0 1 9

Limit (cost=8.31..8.32 rows=1 width=12) (actual time=0.012..0.014 rows=1 loops=9)

40. 0.054 0.090 ↑ 1.0 1 9

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

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

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

  • 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. 0.000 3.043 ↓ 0.0 0 3,043

Index Scan using ixpk_qacsrev on qacsrev_t (cost=163.47..163.61 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=3,043)

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

SubPlan (forIndex Scan)

44. 3.043 1,138.082 ↓ 0.0 0 3,043

Limit (cost=163.19..163.19 rows=1 width=12) (actual time=0.374..0.374 rows=0 loops=3,043)

45. 9.129 1,135.039 ↓ 0.0 0 3,043

Sort (cost=163.19..163.19 rows=1 width=12) (actual time=0.373..0.373 rows=0 loops=3,043)

  • Sort Key: qacsrev_t_1.lu_ts DESC
  • Sort Method: quicksort Memory: 25kB
46. 1,125.910 1,125.910 ↓ 0.0 0 3,043

Seq Scan on qacsrev_t qacsrev_t_1 (cost=0.00..163.18 rows=1 width=12) (actual time=0.370..0.370 rows=0 loops=3,043)

  • Filter: (qasamp_uid = qasamp_t.qasamp_uid)
  • Rows Removed by Filter: 5054
47. 33.473 33.473 ↓ 0.0 0 3,043

Index Scan using ix_qaclmrev_t_dcps_case_uid on qaclmrev_t (cost=0.28..0.44 rows=1 width=17) (actual time=0.010..0.011 rows=0 loops=3,043)

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

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

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

Index Scan using ixf01_clntaddr on clntaddr_t (cost=0.29..2.80 rows=1 width=61) (actual time=0.135..0.137 rows=1 loops=3,043)

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

Index Scan using ixn01_dcpscase_t on dcpscase_t (cost=0.29..3.10 rows=1 width=8) (actual time=0.018..0.019 rows=1 loops=3,043)

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

Index Scan using ixf01_caseclm on caseclm_t (cost=0.29..0.51 rows=1 width=8) (actual time=0.011..0.011 rows=0 loops=3,043)

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
52. 4,411.062 11,085.330 ↓ 0.0 0 3,314

Nested Loop (cost=5,442.44..5,447.44 rows=1 width=15) (actual time=2.077..3.345 rows=0 loops=3,314)

  • Join Filter: (clminfo_t.case_anl_uid = caseanly_t_2.case_anl_uid)
  • Rows Removed by Join Filter: 6400
53. 3.184 523.612 ↓ 0.0 0 3,314

Nested Loop (cost=1.00..1.50 rows=1 width=27) (actual time=0.121..0.158 rows=0 loops=3,314)

54. 6.676 79.536 ↓ 0.0 0 3,314

Nested Loop (cost=0.58..0.76 rows=1 width=16) (actual time=0.018..0.024 rows=0 loops=3,314)

55. 33.140 33.140 ↓ 0.0 0 3,314

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

  • Index Cond: (dcps_case_uid = dcpscase_t.dcps_case_uid)
56. 39.720 39.720 ↑ 1.0 1 1,324

Index Only Scan using ixpk_clminfo on clminfo_t (cost=0.29..0.35 rows=1 width=8) (actual time=0.029..0.030 rows=1 loops=1,324)

  • Index Cond: (case_anl_uid = caseanly_t.case_anl_uid)
  • Heap Fetches: 1324
57. 440.892 440.892 ↑ 1.0 1 1,324

Index Scan using ixpk_anlysgnr on anlysgnr_t (cost=0.42..0.73 rows=1 width=11) (actual time=0.332..0.333 rows=1 loops=1,324)

  • 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. 6,052.532 6,150.656 ↓ 80.8 16,167 1,312

HashAggregate (cost=5,441.44..5,443.44 rows=200 width=4) (actual time=0.086..4.688 rows=16,167 loops=1,312)

  • Group Key: caseanly_t_2.case_anl_uid
59. 14.943 98.124 ↑ 1.0 32,099 1

Unique (cost=4,874.12..5,036.21 rows=32,418 width=16) (actual time=75.383..98.124 rows=32,099 loops=1)

60. 24.584 83.181 ↓ 1.0 32,425 1

Sort (cost=4,874.12..4,955.17 rows=32,418 width=16) (actual time=75.381..83.181 rows=32,425 loops=1)

  • Sort Key: clminfo_t_1.dcps_clm_uid, caseanly_t_2.stus_chgd_ts DESC
  • Sort Method: quicksort Memory: 2288kB
61. 21.481 58.597 ↓ 1.0 32,425 1

Hash Join (cost=1,598.22..2,445.29 rows=32,418 width=16) (actual time=23.873..58.597 rows=32,425 loops=1)

  • Hash Cond: (clminfo_t_1.case_anl_uid = caseanly_t_2.case_anl_uid)
62. 13.291 13.291 ↑ 1.0 40,400 1

Seq Scan on clminfo_t clminfo_t_1 (cost=0.00..741.00 rows=40,400 width=8) (actual time=0.006..13.291 rows=40,400 loops=1)

63. 9.978 23.825 ↓ 1.0 32,425 1

Hash (cost=1,193.00..1,193.00 rows=32,418 width=12) (actual time=23.825..23.825 rows=32,425 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1776kB
64. 13.847 13.847 ↓ 1.0 32,425 1

Seq Scan on caseanly_t caseanly_t_2 (cost=0.00..1,193.00 rows=32,418 width=12) (actual time=0.009..13.847 rows=32,425 loops=1)

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

SubPlan (forNested Loop Left Join)

66. 13.256 238.608 ↑ 1.0 1 3,314

Result (cost=10.16..10.17 rows=1 width=32) (actual time=0.072..0.072 rows=1 loops=3,314)

67.          

Initplan (forResult)

68. 28.142 225.352 ↓ 0.0 0 3,314

Hash Join (cost=8.50..10.16 rows=2 width=32) (actual time=0.064..0.068 rows=0 loops=3,314)

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

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

70. 6.628 182.270 ↓ 0.0 0 3,314

Hash (cost=8.48..8.48 rows=2 width=3) (actual time=0.055..0.055 rows=0 loops=3,314)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
71. 175.642 175.642 ↓ 0.0 0 3,314

Index Scan using ix_cssplhdg_t_dcps_case_uid on cssplhdg_t (cost=0.29..8.48 rows=2 width=3) (actual time=0.051..0.053 rows=0 loops=3,314)

  • Index Cond: (dcps_case_uid = claims.dcps_case_uid)
72. 6.628 13.256 ↑ 1.0 1 3,314

Result (cost=8.48..8.49 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=3,314)

73.          

Initplan (forResult)

74. 6.628 6.628 ↓ 0.0 0 3,314

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

  • Index Cond: (dcps_case_uid = claims.dcps_case_uid)