explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ptde

Settings
# exclusive inclusive rows x rows loops node
1. 0.040 33,374.734 ↓ 14.0 14 1

Nested Loop (cost=750,370.24..750,371.59 rows=1 width=40) (actual time=33,374.302..33,374.734 rows=14 loops=1)

  • Join Filter: (NOT (((uma_3.qcngroup)::text) IS DISTINCT FROM ((uma.qcngroup)::text)))
  • Rows Removed by Join Filter: 182
2. 0.289 33,344.706 ↓ 14.0 14 1

GroupAggregate (cost=740,558.37..740,558.39 rows=1 width=64) (actual time=33,344.311..33,344.706 rows=14 loops=1)

  • Group Key: ((uma.qcngroup)::text)
3. 1.836 33,344.417 ↓ 3,061.0 3,061 1

Sort (cost=740,558.37..740,558.37 rows=1 width=40) (actual time=33,344.291..33,344.417 rows=3,061 loops=1)

  • Sort Key: ((uma.qcngroup)::text)
  • Sort Method: quicksort Memory: 295kB
4. 16,656.946 33,342.581 ↓ 3,061.0 3,061 1

Nested Loop (cost=486,027.08..740,558.36 rows=1 width=40) (actual time=10,904.102..33,342.581 rows=3,061 loops=1)

  • Join Filter: ((NOT ((((to_char((uma.calendardate)::timestamp with time zone, 'FMMonth'::text) || ' '::text) || to_char((uma.calendardate)::timestamp with time zone, 'YYYY'::text))) IS DISTINCT FROM t4."Calculation_1184165276645896197")) AND (NOT ((uma.payorsourcename)::text IS DISTINCT FROM (t4."Payor Source Name (copy)")::text)) AND (NOT (((uma.attributedprovider)::text) IS DISTINCT FROM t4.attributedprovider)) AND (NOT (uma.attributedprovidernpi IS DISTINCT FROM t4.attributedprovidernpi)) AND (NOT (((uma.qcngroup)::text) IS DISTINCT FROM t4.qcngroup)) AND (NOT (((uma.qcnsubgroup)::text) IS DISTINCT FROM t4.qcnsubgroup)))
  • Rows Removed by Join Filter: 181449958
5. 14.936 64.405 ↑ 1.5 3,061 1

HashAggregate (cost=9,917.98..10,035.88 rows=4,716 width=153) (actual time=58.752..64.405 rows=3,061 loops=1)

  • Group Key: ((to_char((uma.calendardate)::timestamp with time zone, 'FMMonth'::text) || ' '::text) || to_char((uma.calendardate)::timestamp with time zone, 'YYYY'::text)), uma.payorsourcename, (uma.attributedprovider)::text, uma.attributedprovidernpi, (uma.qcngroup)::text, (uma.qcnsubgroup)::text
6. 21.492 49.469 ↓ 3.5 16,424 1

Nested Loop (cost=919.23..9,847.24 rows=4,716 width=153) (actual time=3.205..49.469 rows=16,424 loops=1)

  • Join Filter: ((usr.security_role_description = 'UMMS Advanced User'::text) OR ((uma.hierarchylevel)::text = 'QCN'::text) OR (((uma.hierarchylevel)::text = 'QCN Group'::text) AND (usr.group_id = uma.qcngroupid)) OR (((uma.hierarchylevel)::text = 'Provider'::text) AND (((usr.security_role_description = 'Clinical Group'::text) AND (usr.group_id = uma.qcngroupid)) OR ((usr.security_role_description = 'Clinical Practice'::text) AND (usr.subgroup_id = uma.qcnsubgroupid)) OR ((usr.security_role_description = 'Provider'::text) AND (usr.npi_if_provider = uma.attributedprovidernpi) AND (usr.group_id = uma.qcngroupid)))))
7. 5.549 27.977 ↓ 3.6 16,424 1

Hash Join (cost=868.03..9,141.16 rows=4,581 width=100) (actual time=2.851..27.977 rows=16,424 loops=1)

  • Hash Cond: (uma.populationid = pdl.populationid)
  • Join Filter: (uma.calendardate <= pdl."limit")
8. 19.951 22.400 ↓ 1.2 16,424 1

Bitmap Heap Scan on utilizationmembermonthagg uma (cost=832.61..9,069.61 rows=13,742 width=102) (actual time=2.806..22.400 rows=16,424 loops=1)

  • Recheck Cond: ((payorsourcename)::text = 'CMS Medicare ACO'::text)
  • Filter: ((calendardate >= '2018-01-01'::date) AND (calendardate <= '2018-12-31'::date) AND ((hierarchylevel)::text = 'Provider'::text))
  • Rows Removed by Filter: 15319
  • Heap Blocks: exact=2557
9. 2.449 2.449 ↑ 1.0 31,743 1

Bitmap Index Scan on utilizationmembermonthagg2_idx (cost=0.00..829.17 rows=32,100 width=0) (actual time=2.449..2.449 rows=31,743 loops=1)

  • Index Cond: ((payorsourcename)::text = 'CMS Medicare ACO'::text)
10. 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
11. 0.021 0.021 ↑ 66.5 17 1

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

12. 0.000 0.000 ↑ 3.0 1 16,424

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

13. 0.001 0.335 ↑ 3.0 1 1

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

14. 0.006 0.334 ↑ 3.0 1 1

HashAggregate (cost=51.20..51.23 rows=3 width=85) (actual time=0.334..0.334 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
15. 0.001 0.328 ↑ 3.0 1 1

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

16. 0.003 0.119 ↑ 1.0 1 1

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

  • Join Filter: (usr_1.security_role_function_fk = srf.id)
  • Rows Removed by Join Filter: 2
17. 0.111 0.111 ↑ 1.0 1 1

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

  • Filter: ((npi_if_provider IS NULL) AND (lower(account) = 'trn8'::text))
  • Rows Removed by Filter: 422
18. 0.005 0.005 ↑ 2.7 3 1

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

19. 0.000 0.208 ↓ 0.0 0 1

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

20. 0.001 0.208 ↓ 0.0 0 1

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

21. 0.000 0.207 ↓ 0.0 0 1

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

22. 0.002 0.207 ↓ 0.0 0 1

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

23. 0.001 0.203 ↑ 2.0 1 1

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

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

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

25. 0.005 0.200 ↑ 2.0 1 8

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

26. 0.195 0.195 ↑ 2.0 1 1

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

  • Filter: (lower(account) = 'trn8'::text)
  • Rows Removed by Filter: 422
27. 0.002 0.002 ↓ 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.002..0.002 rows=0 loops=1)

  • Index Cond: (provider_fk = usr_2.npi_if_provider)
28. 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)
29. 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)
30. 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
31. 5,782.557 16,621.230 ↓ 30.2 59,279 3,061

Materialize (cost=476,109.10..476,158.13 rows=1,961 width=161) (actual time=3.533..5.430 rows=59,279 loops=3,061)

32. 3.865 10,838.673 ↓ 30.2 59,279 1

Subquery Scan on t4 (cost=476,109.10..476,148.32 rows=1,961 width=161) (actual time=10,814.918..10,838.673 rows=59,279 loops=1)

33. 106.301 10,834.808 ↓ 30.2 59,279 1

HashAggregate (cost=476,109.10..476,128.71 rows=1,961 width=161) (actual time=10,814.916..10,834.808 rows=59,279 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)
34. 6,343.101 10,728.507 ↑ 2.4 59,279 1

Nested Loop (cost=39,512.39..473,581.23 rows=144,450 width=157) (actual time=637.864..10,728.507 rows=59,279 loops=1)

  • Join Filter: (NOT (uma_1.currentattribution IS DISTINCT FROM t3.currentattribution))
  • Rows Removed by Join Filter: 119684301
35. 134.221 472.992 ↓ 3.0 59,279 1

HashAggregate (cost=20,681.67..21,171.99 rows=19,613 width=157) (actual time=452.775..472.992 rows=59,279 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
36. 202.196 338.771 ↓ 2.9 196,094 1

Nested Loop (cost=86.62..19,503.80 rows=67,307 width=157) (actual time=0.336..338.771 rows=196,094 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)))))
37. 101.156 136.575 ↓ 3.0 196,094 1

Hash Join (cost=35.42..10,107.36 rows=65,377 width=104) (actual time=0.023..136.575 rows=196,094 loops=1)

  • Hash Cond: (uma_1.populationid = pdl_1.populationid)
  • Join Filter: (uma_1.calendardate <= pdl_1."limit")
  • Rows Removed by Join Filter: 36
38. 35.407 35.407 ↑ 1.0 196,130 1

Seq Scan on utilizationmembermonthagg uma_1 (cost=0.00..9,556.30 rows=196,130 width=106) (actual time=0.004..35.407 rows=196,130 loops=1)

39. 0.005 0.012 ↑ 66.5 17 1

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

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

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

41. 0.000 0.000 ↑ 3.0 1 196,094

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

42. 0.000 0.303 ↑ 3.0 1 1

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

43. 0.003 0.303 ↑ 3.0 1 1

HashAggregate (cost=51.20..51.23 rows=3 width=85) (actual time=0.302..0.303 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.000 0.300 ↑ 3.0 1 1

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

45. 0.003 0.094 ↑ 1.0 1 1

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

  • Join Filter: (usr_4.security_role_function_fk = srf_2.id)
  • Rows Removed by Join Filter: 2
46. 0.089 0.089 ↑ 1.0 1 1

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

  • Filter: ((npi_if_provider IS NULL) AND (lower(account) = 'trn8'::text))
  • Rows Removed by Filter: 422
47. 0.002 0.002 ↑ 2.7 3 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=3 loops=1)

48. 0.001 0.206 ↓ 0.0 0 1

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

49. 0.000 0.205 ↓ 0.0 0 1

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

50. 0.000 0.205 ↓ 0.0 0 1

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

51. 0.001 0.205 ↓ 0.0 0 1

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

52. 0.000 0.202 ↑ 2.0 1 1

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

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

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

54. 0.005 0.200 ↑ 2.0 1 8

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

55. 0.195 0.195 ↑ 2.0 1 1

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

  • Filter: (lower(account) = 'trn8'::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. 3,727.006 3,912.414 ↓ 1.4 2,020 59,279

Materialize (cost=18,830.73..18,867.55 rows=1,473 width=8) (actual time=0.003..0.066 rows=2,020 loops=59,279)

61. 0.121 185.408 ↓ 1.4 2,020 1

Subquery Scan on t3 (cost=18,830.73..18,860.19 rows=1,473 width=8) (actual time=185.075..185.408 rows=2,020 loops=1)

62. 26.191 185.287 ↓ 1.4 2,020 1

HashAggregate (cost=18,830.73..18,845.46 rows=1,473 width=8) (actual time=185.073..185.287 rows=2,020 loops=1)

  • Group Key: uma_2.currentattribution
63. 46.113 159.096 ↓ 2.9 196,094 1

Nested Loop (cost=86.62..18,494.19 rows=67,307 width=4) (actual time=0.307..159.096 rows=196,094 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. 81.653 112.983 ↓ 3.0 196,094 1

Hash Join (cost=35.42..10,107.36 rows=65,377 width=29) (actual time=0.040..112.983 rows=196,094 loops=1)

  • Hash Cond: (uma_2.populationid = pdl_2.populationid)
  • Join Filter: (uma_2.calendardate <= pdl_2."limit")
  • Rows Removed by Join Filter: 36
65. 31.316 31.316 ↑ 1.0 196,130 1

Seq Scan on utilizationmembermonthagg uma_2 (cost=0.00..9,556.30 rows=196,130 width=35) (actual time=0.009..31.316 rows=196,130 loops=1)

66. 0.005 0.014 ↑ 66.5 17 1

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

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

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

68. 0.000 0.000 ↑ 3.0 1 196,094

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

69. 0.001 0.263 ↑ 3.0 1 1

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

70. 0.004 0.262 ↑ 3.0 1 1

HashAggregate (cost=51.20..51.23 rows=3 width=85) (actual time=0.262..0.262 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
71. 0.002 0.258 ↑ 3.0 1 1

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

72. 0.004 0.097 ↑ 1.0 1 1

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

  • Join Filter: (usr_7.security_role_function_fk = srf_4.id)
  • Rows Removed by Join Filter: 2
73. 0.089 0.089 ↑ 1.0 1 1

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

  • Filter: ((npi_if_provider IS NULL) AND (lower(account) = 'trn8'::text))
  • Rows Removed by Filter: 422
74. 0.004 0.004 ↑ 2.7 3 1

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

75. 0.001 0.159 ↓ 0.0 0 1

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

76. 0.000 0.158 ↓ 0.0 0 1

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

77. 0.000 0.158 ↓ 0.0 0 1

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

78. 0.003 0.158 ↓ 0.0 0 1

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

79. 0.004 0.150 ↑ 2.0 1 1

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

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

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

81. 0.002 0.144 ↑ 2.0 1 8

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

82. 0.142 0.142 ↑ 2.0 1 1

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

  • Filter: (lower(account) = 'trn8'::text)
  • Rows Removed by Filter: 422
83. 0.005 0.005 ↓ 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.005..0.005 rows=0 loops=1)

  • Index Cond: (provider_fk = usr_8.npi_if_provider)
84. 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)
85. 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)
86. 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
87. 2.899 29.988 ↑ 2.9 14 14

HashAggregate (cost=9,811.87..9,812.27 rows=40 width=40) (actual time=2.141..2.142 rows=14 loops=14)

  • Group Key: (uma_3.qcngroup)::text
88. 4.331 27.089 ↓ 3.5 16,424 1

Nested Loop (cost=919.23..9,776.50 rows=4,716 width=40) (actual time=2.543..27.089 rows=16,424 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)))))
89. 5.197 22.758 ↓ 3.6 16,424 1

Hash Join (cost=868.03..9,141.16 rows=4,581 width=51) (actual time=2.247..22.758 rows=16,424 loops=1)

  • Hash Cond: (uma_3.populationid = pdl_3.populationid)
  • Join Filter: (uma_3.calendardate <= pdl_3."limit")
90. 15.619 17.531 ↓ 1.2 16,424 1

Bitmap Heap Scan on utilizationmembermonthagg uma_3 (cost=832.61..9,069.61 rows=13,742 width=57) (actual time=2.175..17.531 rows=16,424 loops=1)

  • Recheck Cond: ((payorsourcename)::text = 'CMS Medicare ACO'::text)
  • Filter: ((calendardate >= '2018-01-01'::date) AND (calendardate <= '2018-12-31'::date) AND ((hierarchylevel)::text = 'Provider'::text))
  • Rows Removed by Filter: 15319
  • Heap Blocks: exact=2557
91. 1.912 1.912 ↑ 1.0 31,743 1

Bitmap Index Scan on utilizationmembermonthagg2_idx (cost=0.00..829.17 rows=32,100 width=0) (actual time=1.912..1.912 rows=31,743 loops=1)

  • Index Cond: ((payorsourcename)::text = 'CMS Medicare ACO'::text)
92. 0.007 0.030 ↑ 66.5 17 1

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

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

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

94. 0.000 0.000 ↑ 3.0 1 16,424

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

95. 0.002 0.287 ↑ 3.0 1 1

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

96. 0.009 0.285 ↑ 3.0 1 1

HashAggregate (cost=51.20..51.23 rows=3 width=85) (actual time=0.284..0.285 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
97. 0.003 0.276 ↑ 3.0 1 1

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

98. 0.006 0.106 ↑ 1.0 1 1

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

  • Join Filter: (usr_10.security_role_function_fk = srf_6.id)
  • Rows Removed by Join Filter: 2
99. 0.094 0.094 ↑ 1.0 1 1

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

  • Filter: ((npi_if_provider IS NULL) AND (lower(account) = 'trn8'::text))
  • Rows Removed by Filter: 422
100. 0.006 0.006 ↑ 2.7 3 1

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

101. 0.001 0.167 ↓ 0.0 0 1

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

102. 0.002 0.166 ↓ 0.0 0 1

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

103. 0.001 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)

104. 0.006 0.163 ↓ 0.0 0 1

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

105. 0.006 0.152 ↑ 2.0 1 1

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

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

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

107. 0.001 0.144 ↑ 2.0 1 8

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

108. 0.143 0.143 ↑ 2.0 1 1

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

  • Filter: (lower(account) = 'trn8'::text)
  • Rows Removed by Filter: 422
109. 0.005 0.005 ↓ 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.005..0.005 rows=0 loops=1)

  • Index Cond: (provider_fk = usr_11.npi_if_provider)
110. 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)
111. 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)
112. 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 : 11.078 ms
Execution time : 33,377.663 ms