explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NW57

Settings
# exclusive inclusive rows x rows loops node
1. 0.031 10,983.482 ↓ 16.0 16 1

Nested Loop (cost=922,242.51..922,243.86 rows=1 width=40) (actual time=10,982.691..10,983.482 rows=16 loops=1)

  • Join Filter: (NOT (((uma_3.qcngroup)::text) IS DISTINCT FROM ((uma.qcngroup)::text)))
  • Rows Removed by Join Filter: 240
2. 0.554 10,936.363 ↓ 16.0 16 1

GroupAggregate (cost=909,329.79..909,329.81 rows=1 width=64) (actual time=10,935.621..10,936.363 rows=16 loops=1)

  • Group Key: ((uma.qcngroup)::text)
3. 2.196 10,935.809 ↓ 6,098.0 6,098 1

Sort (cost=909,329.79..909,329.80 rows=1 width=40) (actual time=10,935.558..10,935.809 rows=6,098 loops=1)

  • Sort Key: ((uma.qcngroup)::text)
  • Sort Method: quicksort Memory: 582kB
4. 7,646.296 10,933.613 ↓ 6,098.0 6,098 1

Nested Loop (cost=428,061.69..909,329.78 rows=1 width=40) (actual time=573.206..10,933.613 rows=6,098 loops=1)

  • Join Filter: ((NOT ((((to_char((uma.calendardate)::timestamp with time zone, 'FMMonth'::text) || ' '::text) || to_char((uma.calendardate)::timestam
  • Rows Removed by Join Filter: 86561110
5. 17.459 91.965 ↑ 1.6 6,098 1

HashAggregate (cost=13,137.67..13,387.62 rows=9,998 width=154) (actual time=85.565..91.965 rows=6,098 loops=1)

  • Group Key: ((to_char((uma.calendardate)::timestamp with time zone, 'FMMonth'::text) || ' '::text) || to_char((uma.calendardate)::timestamp wi
6. 24.101 74.506 ↓ 1.8 18,469 1

Nested Loop (cost=1,823.72..12,987.70 rows=9,998 width=154) (actual time=8.088..74.506 rows=18,469 loops=1)

  • Join Filter: ((usr.security_role_description = 'UMMS Advanced User'::text) OR ((uma.hierarchylevel)::text = 'QCN'::text) OR (((uma.hier
7. 43.533 50.405 ↓ 1.9 18,469 1

Hash Join (cost=1,772.52..11,547.55 rows=9,717 width=101) (actual time=7.663..50.405 rows=18,469 loops=1)

  • Hash Cond: (uma.populationid = pdl.populationid)
  • Join Filter: (uma.calendardate <= pdl."limit")
  • -> Bitmap Heap Scan on utilizationmembermonthagg uma (cost=1737.10..11435.47 rows=29152 width=103) (actual time=7.627..41.954 r
  • Recheck Cond: (((payorsourcename)::text = 'Blue Cross Blue Shield'::text) AND (calendardate >= '2018-01-01'::date) AND (cal
  • Filter: (((hierarchylevel)::text = 'Provider'::text) AND ((qcngroup)::text = ANY ('{"CLINICAL ASSOCIATES",CMG,"DIVINE INTER
  • Rows Removed by Filter: 24838
  • Heap Blocks: exact=5127
8. 6.854 6.854 ↑ 1.2 43,307 1

Bitmap Index Scan on utilizationmembermonthagg_payorsourcename_calendardate_idx (cost=0.00..1,729.81 rows=50,991 width=0) (actual time=6.854..6.854 rows=43,307 loops=1)

  • Index Cond: (((payorsourcename)::text = 'Blue Cross Blue Shield'::text) AND (calendardate >= '2018-01-01'::date) AND (calendardate <= '2018-12-31'::date))
9. 0.007 0.018 ↑ 66.5 17 1

Hash (cost=21.30..21.30 rows=1,130 width=12) (actual time=0.018..0.018 rows=17 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
10. 0.011 0.011 ↑ 66.5 17 1

Seq Scan on populationdatalimit pdl (cost=0.00..21.30 rows=1,130 width=12) (actual time=0.008..0.011 rows=17 loops=1)

11. 0.000 0.000 ↑ 3.0 1 18,469

Materialize (cost=51.20..51.27 rows=3 width=44) (actual time=0.000..0.000 rows=1 loops=18,469)

12. 0.002 0.395 ↑ 3.0 1 1

Subquery Scan on usr (cost=51.20..51.26 rows=3 width=44) (actual time=0.395..0.395 rows=1 loops=1)

13. 0.010 0.393 ↑ 3.0 1 1

HashAggregate (cost=51.20..51.23 rows=3 width=85) (actual time=0.393..0.393 rows=1 loops=1)

  • Group Key: usr_1.id, usr_1.account, usr_1.web_role, srf.role_description, srf.internal_to_umms, usr_1.group_if_clinical_group_fk, usr_1.subgroup_if_clinical_practice_fk, usr_1.npi_if_provider
14. 0.003 0.383 ↑ 3.0 1 1

Append (cost=0.00..51.14 rows=3 width=85) (actual time=0.148..0.383 rows=1 loops=1)

15. 0.006 0.153 ↑ 1.0 1 1

Nested Loop (cost=0.00..16.51 rows=1 width=51) (actual time=0.145..0.153 rows=1 loops=1)

  • Join Filter: (usr_1.security_role_function_fk = srf.id)
16. 0.144 0.144 ↑ 1.0 1 1

Seq Scan on "user" usr_1 (cost=0.00..15.33 rows=1 width=38) (actual time=0.136..0.144 rows=1 loops=1)

  • Filter: ((npi_if_provider IS NULL) AND (lower(account) = 'denish.patel'::text))
  • Rows Removed by Filter: 422
17. 0.003 0.003 ↑ 8.0 1 1

Seq Scan on security_role_function srf (cost=0.00..1.08 rows=8 width=21) (actual time=0.003..0.003 rows=1 loops=1)

18. 0.001 0.227 ↓ 0.0 0 1

Nested Loop (cost=0.71..34.58 rows=2 width=51) (actual time=0.227..0.227 rows=0 loops=1)

19. 0.002 0.226 ↓ 0.0 0 1

Nested Loop (cost=0.57..34.09 rows=2 width=51) (actual time=0.226..0.226 rows=0 loops=1)

20. 0.001 0.224 ↓ 0.0 0 1

Nested Loop (cost=0.43..33.61 rows=2 width=47) (actual time=0.224..0.224 rows=0 loops=1)

21. 0.007 0.223 ↓ 0.0 0 1

Nested Loop (cost=0.28..33.27 rows=2 width=47) (actual time=0.223..0.223 rows=0 loops=1)

22. 0.008 0.211 ↑ 2.0 1 1

Nested Loop (cost=0.00..16.66 rows=2 width=43) (actual time=0.205..0.211 rows=1 loops=1)

  • Join Filter: (usr_2.security_role_function_fk = srf_1.id)
  • Rows Removed by Join Filter: 7
23. 0.003 0.003 ↑ 1.0 8 1

Seq Scan on security_role_function srf_1 (cost=0.00..1.08 rows=8 width=21) (actual time=0.002..0.003 rows=8 loops=1)

24. 0.001 0.200 ↑ 2.0 1 8

Materialize (cost=0.00..15.34 rows=2 width=30) (actual time=0.025..0.025 rows=1 loops=8)

25. 0.199 0.199 ↑ 2.0 1 1

Seq Scan on "user" usr_2 (cost=0.00..15.33 rows=2 width=30) (actual time=0.197..0.199 rows=1 loops=1)

  • Filter: (lower(account) = 'denish.patel'::text)
  • Rows Removed by Filter: 422
26. 0.005 0.005 ↓ 0.0 0 1

Index Scan using provider_to_subgroup_address_provider_fk_idx on provider_to_subgroup_address ptsa (cost=0.28..8.30 rows=1 width=8) (actual time=0.005..0.005 rows=0 loops=1)

  • Index Cond: (provider_fk = usr_2.npi_if_provider)
27. 0.000 0.000 ↓ 0.0 0

Index Scan using subgroup_address_pkey on subgroup_address subgadd (cost=0.14..0.17 rows=1 width=8) (never executed)

  • Index Cond: (subgroup_address_id = ptsa.subgroup_address_fk)
28. 0.000 0.000 ↓ 0.0 0

Index Scan using subgroup_pkey on subgroup subg (cost=0.14..0.24 rows=1 width=8) (never executed)

  • Index Cond: (subgroup_id = subgadd.subgroup_fk)
29. 0.000 0.000 ↓ 0.0 0

Index Only Scan using group_pkey on "group" grp (cost=0.14..0.25 rows=1 width=4) (never executed)

  • Index Cond: (group_id = subg.group_fk)
  • Heap Fetches: 0
30. 2,704.458 3,195.352 ↓ 8.1 14,196 6,098

Materialize (cost=414,924.03..414,967.75 rows=1,749 width=162) (actual time=0.080..0.524 rows=14,196 loops=6,098)

31. 0.928 490.894 ↓ 8.1 14,196 1

Subquery Scan on t4 (cost=414,924.03..414,959.01 rows=1,749 width=162) (actual time=487.594..490.894 rows=14,196 loops=1)

32. 9.931 489.966 ↓ 8.1 14,196 1

HashAggregate (cost=414,924.03..414,941.52 rows=1,749 width=162) (actual time=487.592..489.966 rows=14,196 loops=1)

  • Group Key: (((to_char((uma_1.calendardate)::timestamp with time zone, 'FMMonth'::text) || ' '::text) || to_char((uma_1.calendardate)::timestamp with time zone, 'YYYY'::text))), uma_1.payorsourcename, ((uma_1.attributedprovider)::text), uma_1.attributedprovidernpi, ((uma_1.qcngroup)::text), ((uma_1.qcnsubgroup)::text)
33. 201.883 480.035 ↑ 9.1 14,196 1

Nested Loop (cost=25,891.33..412,671.57 rows=128,712 width=158) (actual time=148.118..480.035 rows=14,196 loops=1)

  • Join Filter: (NOT (uma_1.currentattribution IS DISTINCT FROM t3.currentattribution))
  • Rows Removed by Join Filter: 3761940
34. 24.491 107.800 ↑ 1.2 14,196 1

HashAggregate (cost=13,186.12..13,623.32 rows=17,488 width=158) (actual time=105.043..107.800 rows=14,196 loops=1)

  • Group Key: ((to_char((uma_1.calendardate)::timestamp with time zone, 'FMMonth'::text) || ' '::text) || to_char((uma_1.calendardate)::timestamp with time zone, 'YYYY'::text)), uma_1.payorsourcename, (uma_1.attributedprovider)::text, uma_1.attributedprovidernpi, uma_1.currentattribution, (uma_1.qcngroup)::text, (uma_1.qcnsubgroup)::text
35. 43.073 83.309 ↓ 2.5 43,307 1

Nested Loop (cost=1,829.18..12,880.08 rows=17,488 width=158) (actual time=7.043..83.309 rows=43,307 loops=1)

  • Join Filter: ((usr_3.security_role_description = 'UMMS Advanced User'::text) OR ((uma_1.hierarchylevel)::text = 'QCN'::text) OR (((uma_1.hierarchylevel)::text = 'QCN Group'::text) AND (usr_3.group_id = uma_1.qcngroupid)) OR (((uma_1.hierarchylevel)::text = 'Provider'::text) AND (((usr_3.security_role_description = 'Clinical Group'::text) AND (usr_3.group_id = uma_1.qcngroupid)) OR ((usr_3.security_role_description = 'Clinical Practice'::text) AND (usr_3.subgroup_id = uma_1.qcnsubgroupid)) OR ((usr_3.security_role_description = 'Provider'::text) AND (usr_3.npi_if_provider = uma_1.attributedprovidernpi) AND (usr_3.group_id = uma_1.qcngroupid)))))
36. 22.678 40.236 ↓ 2.5 43,307 1

Hash Join (cost=1,777.98..10,399.38 rows=16,997 width=105) (actual time=6.692..40.236 rows=43,307 loops=1)

  • Hash Cond: (uma_1.populationid = pdl_1.populationid)
  • Join Filter: (uma_1.calendardate <= pdl_1."limit")
37. 11.635 17.542 ↑ 1.2 43,307 1

Bitmap Heap Scan on utilizationmembermonthagg uma_1 (cost=1,742.56..10,229.90 rows=50,991 width=107) (actual time=6.648..17.542 rows=43,307 loops=1)

  • Recheck Cond: (((payorsourcename)::text = 'Blue Cross Blue Shield'::text) AND (calendardate >= '2018-01-01'::date) AND (calendardate <= '2018-12-31'::date))
  • Heap Blocks: exact=5127
38. 5.907 5.907 ↑ 1.2 43,307 1

Bitmap Index Scan on utilizationmembermonthagg_payorsourcename_calendardate_idx (cost=0.00..1,729.81 rows=50,991 width=0) (actual time=5.907..5.907 rows=43,307 loops=1)

  • Index Cond: (((payorsourcename)::text = 'Blue Cross Blue Shield'::text) AND (calendardate >= '2018-01-01'::date) AND (calendardate <= '2018-12-31'::date))
39. 0.007 0.016 ↑ 66.5 17 1

Hash (cost=21.30..21.30 rows=1,130 width=12) (actual time=0.016..0.016 rows=17 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
40. 0.009 0.009 ↑ 66.5 17 1

Seq Scan on populationdatalimit pdl_1 (cost=0.00..21.30 rows=1,130 width=12) (actual time=0.007..0.009 rows=17 loops=1)

41. 0.000 0.000 ↑ 3.0 1 43,307

Materialize (cost=51.20..51.27 rows=3 width=44) (actual time=0.000..0.000 rows=1 loops=43,307)

42. 0.002 0.333 ↑ 3.0 1 1

Subquery Scan on usr_3 (cost=51.20..51.26 rows=3 width=44) (actual time=0.333..0.333 rows=1 loops=1)

43. 0.007 0.331 ↑ 3.0 1 1

HashAggregate (cost=51.20..51.23 rows=3 width=85) (actual time=0.331..0.331 rows=1 loops=1)

  • Group Key: usr_4.id, usr_4.account, usr_4.web_role, srf_2.role_description, srf_2.internal_to_umms, usr_4.group_if_clinical_group_fk, usr_4.subgroup_if_clinical_practice_fk, usr_4.npi_if_provider
44. 0.002 0.324 ↑ 3.0 1 1

Append (cost=0.00..51.14 rows=3 width=85) (actual time=0.094..0.324 rows=1 loops=1)

45. 0.005 0.097 ↑ 1.0 1 1

Nested Loop (cost=0.00..16.51 rows=1 width=51) (actual time=0.093..0.097 rows=1 loops=1)

  • Join Filter: (usr_4.security_role_function_fk = srf_2.id)
46. 0.090 0.090 ↑ 1.0 1 1

Seq Scan on "user" usr_4 (cost=0.00..15.33 rows=1 width=38) (actual time=0.086..0.090 rows=1 loops=1)

  • Filter: ((npi_if_provider IS NULL) AND (lower(account) = 'denish.patel'::text))
  • Rows Removed by Filter: 422
47. 0.002 0.002 ↑ 8.0 1 1

Seq Scan on security_role_function srf_2 (cost=0.00..1.08 rows=8 width=21) (actual time=0.002..0.002 rows=1 loops=1)

48. 0.001 0.225 ↓ 0.0 0 1

Nested Loop (cost=0.71..34.58 rows=2 width=51) (actual time=0.225..0.225 rows=0 loops=1)

49. 0.003 0.224 ↓ 0.0 0 1

Nested Loop (cost=0.57..34.09 rows=2 width=51) (actual time=0.224..0.224 rows=0 loops=1)

50. 0.001 0.221 ↓ 0.0 0 1

Nested Loop (cost=0.43..33.61 rows=2 width=47) (actual time=0.221..0.221 rows=0 loops=1)

51. 0.008 0.220 ↓ 0.0 0 1

Nested Loop (cost=0.28..33.27 rows=2 width=47) (actual time=0.220..0.220 rows=0 loops=1)

52. 0.007 0.210 ↑ 2.0 1 1

Nested Loop (cost=0.00..16.66 rows=2 width=43) (actual time=0.205..0.210 rows=1 loops=1)

  • Join Filter: (usr_5.security_role_function_fk = srf_3.id)
  • Rows Removed by Join Filter: 7
53. 0.003 0.003 ↑ 1.0 8 1

Seq Scan on security_role_function srf_3 (cost=0.00..1.08 rows=8 width=21) (actual time=0.002..0.003 rows=8 loops=1)

54. 0.003 0.200 ↑ 2.0 1 8

Materialize (cost=0.00..15.34 rows=2 width=30) (actual time=0.025..0.025 rows=1 loops=8)

55. 0.197 0.197 ↑ 2.0 1 1

Seq Scan on "user" usr_5 (cost=0.00..15.33 rows=2 width=30) (actual time=0.196..0.197 rows=1 loops=1)

  • Filter: (lower(account) = 'denish.patel'::text)
  • Rows Removed by Filter: 422
56. 0.002 0.002 ↓ 0.0 0 1

Index Scan using provider_to_subgroup_address_provider_fk_idx on provider_to_subgroup_address ptsa_1 (cost=0.28..8.30 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: (provider_fk = usr_5.npi_if_provider)
57. 0.000 0.000 ↓ 0.0 0

Index Scan using subgroup_address_pkey on subgroup_address subgadd_1 (cost=0.14..0.17 rows=1 width=8) (never executed)

  • Index Cond: (subgroup_address_id = ptsa_1.subgroup_address_fk)
58. 0.000 0.000 ↓ 0.0 0

Index Scan using subgroup_pkey on subgroup subg_1 (cost=0.14..0.24 rows=1 width=8) (never executed)

  • Index Cond: (subgroup_id = subgadd_1.subgroup_fk)
59. 0.000 0.000 ↓ 0.0 0

Index Only Scan using group_pkey on "group" grp_1 (cost=0.14..0.25 rows=1 width=4) (never executed)

  • Index Cond: (group_id = subg_1.group_fk)
  • Heap Fetches: 0
60. 127.311 170.352 ↑ 5.5 266 14,196

Materialize (cost=12,705.20..12,742.00 rows=1,472 width=8) (actual time=0.003..0.012 rows=266 loops=14,196)

61. 0.018 43.041 ↑ 5.5 266 1

Subquery Scan on t3 (cost=12,705.20..12,734.64 rows=1,472 width=8) (actual time=42.993..43.041 rows=266 loops=1)

62. 5.233 43.023 ↑ 5.5 266 1

HashAggregate (cost=12,705.20..12,719.92 rows=1,472 width=8) (actual time=42.991..43.023 rows=266 loops=1)

  • Group Key: uma_2.currentattribution
63. 9.922 37.790 ↓ 2.5 43,307 1

Nested Loop (cost=1,829.18..12,617.76 rows=17,488 width=4) (actual time=5.007..37.790 rows=43,307 loops=1)

  • Join Filter: ((usr_6.security_role_description = 'UMMS Advanced User'::text) OR ((uma_2.hierarchylevel)::text = 'QCN'::text) OR (((uma_2.hierarchylevel)::text = 'QCN Group'::text) AND (usr_6.group_id = uma_2.qcngroupid)) OR (((uma_2.hierarchylevel)::text = 'Provider'::text) AND (((usr_6.security_role_description = 'Clinical Group'::text) AND (usr_6.group_id = uma_2.qcngroupid)) OR ((usr_6.security_role_description = 'Clinical Practice'::text) AND (usr_6.subgroup_id = uma_2.qcnsubgroupid)) OR ((usr_6.security_role_description = 'Provider'::text) AND (usr_6.npi_if_provider = uma_2.attributedprovidernpi) AND (usr_6.group_id = uma_2.qcngroupid)))))
64. 16.341 27.868 ↓ 2.5 43,307 1

Hash Join (cost=1,777.98..10,399.38 rows=16,997 width=29) (actual time=4.751..27.868 rows=43,307 loops=1)

  • Hash Cond: (uma_2.populationid = pdl_2.populationid)
  • Join Filter: (uma_2.calendardate <= pdl_2."limit")
65. 7.313 11.500 ↑ 1.2 43,307 1

Bitmap Heap Scan on utilizationmembermonthagg uma_2 (cost=1,742.56..10,229.90 rows=50,991 width=35) (actual time=4.714..11.500 rows=43,307 loops=1)

  • Recheck Cond: (((payorsourcename)::text = 'Blue Cross Blue Shield'::text) AND (calendardate >= '2018-01-01'::date) AND (calendardate <= '2018-12-31'::date))
  • Heap Blocks: exact=5127
66. 4.187 4.187 ↑ 1.2 43,307 1

Bitmap Index Scan on utilizationmembermonthagg_payorsourcename_calendardate_idx (cost=0.00..1,729.81 rows=50,991 width=0) (actual time=4.186..4.187 rows=43,307 loops=1)

  • Index Cond: (((payorsourcename)::text = 'Blue Cross Blue Shield'::text) AND (calendardate >= '2018-01-01'::date) AND (calendardate <= '2018-12-31'::date))
67. 0.004 0.027 ↑ 66.5 17 1

Hash (cost=21.30..21.30 rows=1,130 width=12) (actual time=0.027..0.027 rows=17 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
68. 0.023 0.023 ↑ 66.5 17 1

Seq Scan on populationdatalimit pdl_2 (cost=0.00..21.30 rows=1,130 width=12) (actual time=0.020..0.023 rows=17 loops=1)

69. 0.000 0.000 ↑ 3.0 1 43,307

Materialize (cost=51.20..51.27 rows=3 width=44) (actual time=0.000..0.000 rows=1 loops=43,307)

70. 0.002 0.248 ↑ 3.0 1 1

Subquery Scan on usr_6 (cost=51.20..51.26 rows=3 width=44) (actual time=0.247..0.248 rows=1 loops=1)

71. 0.008 0.246 ↑ 3.0 1 1

HashAggregate (cost=51.20..51.23 rows=3 width=85) (actual time=0.246..0.246 rows=1 loops=1)

  • Group Key: usr_7.id, usr_7.account, usr_7.web_role, srf_4.role_description, srf_4.internal_to_umms, usr_7.group_if_clinical_group_fk, usr_7.subgroup_if_clinical_practice_fk, usr_7.npi_if_provider
72. 0.001 0.238 ↑ 3.0 1 1

Append (cost=0.00..51.14 rows=3 width=85) (actual time=0.071..0.238 rows=1 loops=1)

73. 0.004 0.072 ↑ 1.0 1 1

Nested Loop (cost=0.00..16.51 rows=1 width=51) (actual time=0.069..0.072 rows=1 loops=1)

  • Join Filter: (usr_7.security_role_function_fk = srf_4.id)
74. 0.066 0.066 ↑ 1.0 1 1

Seq Scan on "user" usr_7 (cost=0.00..15.33 rows=1 width=38) (actual time=0.064..0.066 rows=1 loops=1)

  • Filter: ((npi_if_provider IS NULL) AND (lower(account) = 'denish.patel'::text))
  • Rows Removed by Filter: 422
75. 0.002 0.002 ↑ 8.0 1 1

Seq Scan on security_role_function srf_4 (cost=0.00..1.08 rows=8 width=21) (actual time=0.002..0.002 rows=1 loops=1)

76. 0.002 0.165 ↓ 0.0 0 1

Nested Loop (cost=0.71..34.58 rows=2 width=51) (actual time=0.165..0.165 rows=0 loops=1)

77. 0.001 0.163 ↓ 0.0 0 1

Nested Loop (cost=0.57..34.09 rows=2 width=51) (actual time=0.163..0.163 rows=0 loops=1)

78. 0.001 0.162 ↓ 0.0 0 1

Nested Loop (cost=0.43..33.61 rows=2 width=47) (actual time=0.162..0.162 rows=0 loops=1)

79. 0.006 0.161 ↓ 0.0 0 1

Nested Loop (cost=0.28..33.27 rows=2 width=47) (actual time=0.161..0.161 rows=0 loops=1)

80. 0.007 0.154 ↑ 2.0 1 1

Nested Loop (cost=0.00..16.66 rows=2 width=43) (actual time=0.150..0.154 rows=1 loops=1)

  • Join Filter: (usr_8.security_role_function_fk = srf_5.id)
  • Rows Removed by Join Filter: 7
81. 0.003 0.003 ↑ 1.0 8 1

Seq Scan on security_role_function srf_5 (cost=0.00..1.08 rows=8 width=21) (actual time=0.002..0.003 rows=8 loops=1)

82. 0.000 0.144 ↑ 2.0 1 8

Materialize (cost=0.00..15.34 rows=2 width=30) (actual time=0.018..0.018 rows=1 loops=8)

83. 0.144 0.144 ↑ 2.0 1 1

Seq Scan on "user" usr_8 (cost=0.00..15.33 rows=2 width=30) (actual time=0.143..0.144 rows=1 loops=1)

  • Filter: (lower(account) = 'denish.patel'::text)
  • Rows Removed by Filter: 422
84. 0.001 0.001 ↓ 0.0 0 1

Index Scan using provider_to_subgroup_address_provider_fk_idx on provider_to_subgroup_address ptsa_2 (cost=0.28..8.30 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: (provider_fk = usr_8.npi_if_provider)
85. 0.000 0.000 ↓ 0.0 0

Index Scan using subgroup_address_pkey on subgroup_address subgadd_2 (cost=0.14..0.17 rows=1 width=8) (never executed)

  • Index Cond: (subgroup_address_id = ptsa_2.subgroup_address_fk)
86. 0.000 0.000 ↓ 0.0 0

Index Scan using subgroup_pkey on subgroup subg_2 (cost=0.14..0.24 rows=1 width=8) (never executed)

  • Index Cond: (subgroup_id = subgadd_2.subgroup_fk)
87. 0.000 0.000 ↓ 0.0 0

Index Only Scan using group_pkey on "group" grp_2 (cost=0.14..0.25 rows=1 width=4) (never executed)

  • Index Cond: (group_id = subg_2.group_fk)
  • Heap Fetches: 0
88. 2.896 47.088 ↑ 2.5 16 16

HashAggregate (cost=12,912.71..12,913.11 rows=40 width=40) (actual time=2.941..2.943 rows=16 loops=16)

  • Group Key: (uma_3.qcngroup)::text
89. 4.771 44.192 ↓ 1.8 18,469 1

Nested Loop (cost=1,823.72..12,837.73 rows=9,998 width=40) (actual time=5.593..44.192 rows=18,469 loops=1)

  • Join Filter: ((usr_9.security_role_description = 'UMMS Advanced User'::text) OR ((uma_3.hierarchylevel)::text = 'QCN'::text) OR (((uma_3.hierarchylevel)::text = 'QCN Group'::text) AND (usr_9.group_id = uma_3.qcngroupid)) OR (((uma_3.hierarchylevel)::text = 'Provider'::text) AND (((usr_9.security_role_description = 'Clinical Group'::text) AND (usr_9.group_id = uma_3.qcngroupid)) OR ((usr_9.security_role_description = 'Clinical Practice'::text) AND (usr_9.subgroup_id = uma_3.qcnsubgroupid)) OR ((usr_9.security_role_description = 'Provider'::text) AND (usr_9.npi_if_provider = uma_3.attributedprovidernpi) AND (usr_9.group_id = uma_3.qcngroupid)))))
90. 6.409 39.421 ↓ 1.9 18,469 1

Hash Join (cost=1,772.52..11,547.55 rows=9,717 width=51) (actual time=5.307..39.421 rows=18,469 loops=1)

  • Hash Cond: (uma_3.populationid = pdl_3.populationid)
  • Join Filter: (uma_3.calendardate <= pdl_3."limit")
91. 28.271 32.984 ↑ 1.6 18,469 1

Bitmap Heap Scan on utilizationmembermonthagg uma_3 (cost=1,737.10..11,435.47 rows=29,152 width=57) (actual time=5.253..32.984 rows=18,469 loops=1)

  • Recheck Cond: (((payorsourcename)::text = 'Blue Cross Blue Shield'::text) AND (calendardate >= '2018-01-01'::date) AND (calendardate <= '2018-12-31'::date))
  • Filter: (((hierarchylevel)::text = 'Provider'::text) AND ((qcngroup)::text = ANY ('{"CLINICAL ASSOCIATES",CMG,"DIVINE INTERNAL MEDICINE","FAMILY HEALTHCARE OF ELKTON","HARFORD PRIMARY CARE","MARYLAND FAMILY CARE (MERCY)",MPCP,"MULVEY FAMILY PRACTICE","NEIL LATTIN, MD","NORTH BAY MEDICAL ASSOCIATES","PLUMTREE FAMILY HEALTH CENTER","SETON MEDICAL GROUP","SMALDORE FAMILY PRACTICE","ST. JOSEPH MEDICAL GROUP","UNION HOSPITAL FAMILY PHYSICIANS","UPPER CHESAPEAKE PRIMARY CARE","WEST CECIL HEALTH CENTER"}'::text[])))
  • Rows Removed by Filter: 24838
  • Heap Blocks: exact=5127
92. 4.713 4.713 ↑ 1.2 43,307 1

Bitmap Index Scan on utilizationmembermonthagg_payorsourcename_calendardate_idx (cost=0.00..1,729.81 rows=50,991 width=0) (actual time=4.713..4.713 rows=43,307 loops=1)

  • Index Cond: (((payorsourcename)::text = 'Blue Cross Blue Shield'::text) AND (calendardate >= '2018-01-01'::date) AND (calendardate <= '2018-12-31'::date))
93. 0.007 0.028 ↑ 66.5 17 1

Hash (cost=21.30..21.30 rows=1,130 width=12) (actual time=0.028..0.028 rows=17 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
94. 0.021 0.021 ↑ 66.5 17 1

Seq Scan on populationdatalimit pdl_3 (cost=0.00..21.30 rows=1,130 width=12) (actual time=0.019..0.021 rows=17 loops=1)

95. 0.000 0.000 ↑ 3.0 1 18,469

Materialize (cost=51.20..51.27 rows=3 width=44) (actual time=0.000..0.000 rows=1 loops=18,469)

96. 0.002 0.277 ↑ 3.0 1 1

Subquery Scan on usr_9 (cost=51.20..51.26 rows=3 width=44) (actual time=0.277..0.277 rows=1 loops=1)

97. 0.008 0.275 ↑ 3.0 1 1

HashAggregate (cost=51.20..51.23 rows=3 width=85) (actual time=0.275..0.275 rows=1 loops=1)

  • Group Key: usr_10.id, usr_10.account, usr_10.web_role, srf_6.role_description, srf_6.internal_to_umms, usr_10.group_if_clinical_group_fk, usr_10.subgroup_if_clinical_practice_fk, usr_10.npi_if_provider
98. 0.002 0.267 ↑ 3.0 1 1

Append (cost=0.00..51.14 rows=3 width=85) (actual time=0.094..0.267 rows=1 loops=1)

99. 0.005 0.099 ↑ 1.0 1 1

Nested Loop (cost=0.00..16.51 rows=1 width=51) (actual time=0.093..0.099 rows=1 loops=1)

  • Join Filter: (usr_10.security_role_function_fk = srf_6.id)
100. 0.089 0.089 ↑ 1.0 1 1

Seq Scan on "user" usr_10 (cost=0.00..15.33 rows=1 width=38) (actual time=0.083..0.089 rows=1 loops=1)

  • Filter: ((npi_if_provider IS NULL) AND (lower(account) = 'denish.patel'::text))
  • Rows Removed by Filter: 422
101. 0.005 0.005 ↑ 8.0 1 1

Seq Scan on security_role_function srf_6 (cost=0.00..1.08 rows=8 width=21) (actual time=0.005..0.005 rows=1 loops=1)

102. 0.001 0.166 ↓ 0.0 0 1

Nested Loop (cost=0.71..34.58 rows=2 width=51) (actual time=0.166..0.166 rows=0 loops=1)

103. 0.001 0.165 ↓ 0.0 0 1

Nested Loop (cost=0.57..34.09 rows=2 width=51) (actual time=0.165..0.165 rows=0 loops=1)

104. 0.000 0.164 ↓ 0.0 0 1

Nested Loop (cost=0.43..33.61 rows=2 width=47) (actual time=0.164..0.164 rows=0 loops=1)

105. 0.004 0.164 ↓ 0.0 0 1

Nested Loop (cost=0.28..33.27 rows=2 width=47) (actual time=0.164..0.164 rows=0 loops=1)

106. 0.009 0.154 ↑ 2.0 1 1

Nested Loop (cost=0.00..16.66 rows=2 width=43) (actual time=0.150..0.154 rows=1 loops=1)

  • Join Filter: (usr_11.security_role_function_fk = srf_7.id)
  • Rows Removed by Join Filter: 7
107. 0.001 0.001 ↑ 1.0 8 1

Seq Scan on security_role_function srf_7 (cost=0.00..1.08 rows=8 width=21) (actual time=0.001..0.001 rows=8 loops=1)

108. 0.000 0.144 ↑ 2.0 1 8

Materialize (cost=0.00..15.34 rows=2 width=30) (actual time=0.018..0.018 rows=1 loops=8)

109. 0.145 0.145 ↑ 2.0 1 1

Seq Scan on "user" usr_11 (cost=0.00..15.33 rows=2 width=30) (actual time=0.144..0.145 rows=1 loops=1)

  • Filter: (lower(account) = 'denish.patel'::text)
  • Rows Removed by Filter: 422
110. 0.006 0.006 ↓ 0.0 0 1

Index Scan using provider_to_subgroup_address_provider_fk_idx on provider_to_subgroup_address ptsa_3 (cost=0.28..8.30 rows=1 width=8) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: (provider_fk = usr_11.npi_if_provider)
111. 0.000 0.000 ↓ 0.0 0

Index Scan using subgroup_address_pkey on subgroup_address subgadd_3 (cost=0.14..0.17 rows=1 width=8) (never executed)

  • Index Cond: (subgroup_address_id = ptsa_3.subgroup_address_fk)
112. 0.000 0.000 ↓ 0.0 0

Index Scan using subgroup_pkey on subgroup subg_3 (cost=0.14..0.24 rows=1 width=8) (never executed)

  • Index Cond: (subgroup_id = subgadd_3.subgroup_fk)
113. 0.000 0.000 ↓ 0.0 0

Index Only Scan using group_pkey on "group" grp_3 (cost=0.14..0.25 rows=1 width=4) (never executed)

  • Index Cond: (group_id = subg_3.group_fk)
  • Heap Fetches: 0
Planning time : 9.180 ms
Execution time : 10,984.971 ms