explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kahT

Settings
# exclusive inclusive rows x rows loops node
1. 0.033 32,172.347 ↓ 14.0 14 1

Nested Loop (cost=750,676.22..750,677.57 rows=1 width=40) (actual time=32,171.917..32,172.347 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.288 32,143.656 ↓ 14.0 14 1

GroupAggregate (cost=740,787.85..740,787.87 rows=1 width=64) (actual time=32,143.264..32,143.656 rows=14 loops=1)

  • Group Key: ((uma.qcngroup)::text)
3. 1.818 32,143.368 ↓ 3,061.0 3,061 1

Sort (cost=740,787.85..740,787.86 rows=1 width=40) (actual time=32,143.245..32,143.368 rows=3,061 loops=1)

  • Sort Key: ((uma.qcngroup)::text)
  • Sort Method: quicksort Memory: 295kB
4. 15,510.882 32,141.550 ↓ 3,061.0 3,061 1

Nested Loop (cost=486,256.57..740,787.84 rows=1 width=40) (actual time=10,894.593..32,141.550 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. 15.746 64.536 ↑ 1.5 3,061 1

HashAggregate (cost=9,994.48..10,112.38 rows=4,716 width=153) (actual time=58.739..64.536 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.031 48.790 ↓ 3.5 16,424 1

Nested Loop (cost=995.72..9,923.74 rows=4,716 width=153) (actual time=3.322..48.790 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. 6.004 27.759 ↓ 3.6 16,424 1

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

  • Hash Cond: (uma.populationid = pdl.populationid)
  • Join Filter: (uma.calendardate <= pdl."limit")
8. 19.192 21.732 ↓ 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.896..21.732 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.540 2.540 ↑ 1.0 31,743 1

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

  • Index Cond: ((payorsourcename)::text = 'CMS Medicare ACO'::text)
10. 0.010 0.023 ↑ 66.5 17 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
11. 0.013 0.013 ↑ 66.5 17 1

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

12. 0.000 0.000 ↑ 3.0 1 16,424

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

13. 0.001 0.358 ↑ 3.0 1 1

Subquery Scan on usr (cost=127.69..127.75 rows=3 width=44) (actual time=0.357..0.358 rows=1 loops=1)

14. 0.006 0.357 ↑ 3.0 1 1

HashAggregate (cost=127.69..127.72 rows=3 width=85) (actual time=0.357..0.357 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.351 ↑ 3.0 1 1

Append (cost=0.00..127.63 rows=3 width=85) (actual time=0.047..0.351 rows=1 loops=1)

16. 0.003 0.133 ↑ 1.0 1 1

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

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

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

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

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

19. 0.000 0.217 ↓ 0.0 0 1

Nested Loop (cost=15.78..111.08 rows=2 width=51) (actual time=0.217..0.217 rows=0 loops=1)

20. 0.001 0.217 ↓ 0.0 0 1

Nested Loop (cost=15.64..110.58 rows=2 width=51) (actual time=0.217..0.217 rows=0 loops=1)

21. 0.000 0.216 ↓ 0.0 0 1

Nested Loop (cost=15.50..110.11 rows=2 width=47) (actual time=0.216..0.216 rows=0 loops=1)

22. 0.000 0.216 ↓ 0.0 0 1

Nested Loop (cost=15.36..109.77 rows=2 width=47) (actual time=0.216..0.216 rows=0 loops=1)

  • Join Filter: (usr_2.security_role_function_fk = srf_1.id)
23. 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)

24. 0.004 0.216 ↓ 0.0 0 8

Materialize (cost=15.36..108.45 rows=2 width=34) (actual time=0.027..0.027 rows=0 loops=8)

25. 0.004 0.212 ↓ 0.0 0 1

Hash Join (cost=15.36..108.44 rows=2 width=34) (actual time=0.212..0.212 rows=0 loops=1)

  • Hash Cond: (ptsa.provider_fk = usr_2.npi_if_provider)
26. 0.011 0.011 ↑ 3,471.0 1 1

Seq Scan on provider_to_subgroup_address ptsa (cost=0.00..75.71 rows=3,471 width=8) (actual time=0.011..0.011 rows=1 loops=1)

27. 0.001 0.197 ↓ 0.0 0 1

Hash (cost=15.33..15.33 rows=2 width=30) (actual time=0.197..0.197 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
28. 0.196 0.196 ↑ 2.0 1 1

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

  • Filter: (lower(account) = 'trn8'::text)
  • Rows Removed by Filter: 422
29. 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)
30. 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)
31. 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
32. 5,740.373 16,566.132 ↓ 30.2 59,279 3,061

Materialize (cost=476,262.09..476,311.12 rows=1,961 width=161) (actual time=3.529..5.412 rows=59,279 loops=3,061)

33. 3.963 10,825.759 ↓ 30.2 59,279 1

Subquery Scan on t4 (cost=476,262.09..476,301.31 rows=1,961 width=161) (actual time=10,802.976..10,825.759 rows=59,279 loops=1)

34. 57.213 10,821.796 ↓ 30.2 59,279 1

HashAggregate (cost=476,262.09..476,281.70 rows=1,961 width=161) (actual time=10,802.975..10,821.796 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)
35. 6,386.291 10,764.583 ↑ 2.4 59,279 1

Nested Loop (cost=39,665.38..473,734.22 rows=144,450 width=157) (actual time=626.636..10,764.583 rows=59,279 loops=1)

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

HashAggregate (cost=20,758.16..21,248.49 rows=19,613 width=157) (actual time=444.963..465.878 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
37. 204.606 340.549 ↓ 2.9 196,094 1

Nested Loop (cost=163.12..19,580.29 rows=67,307 width=157) (actual time=0.367..340.549 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)))))
38. 101.724 135.943 ↓ 3.0 196,094 1

Hash Join (cost=35.42..10,107.36 rows=65,377 width=104) (actual time=0.038..135.943 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
39. 34.206 34.206 ↑ 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.007..34.206 rows=196,130 loops=1)

40. 0.007 0.013 ↑ 66.5 17 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
41. 0.006 0.006 ↑ 66.5 17 1

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

42. 0.000 0.000 ↑ 3.0 1 196,094

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

43. 0.001 0.318 ↑ 3.0 1 1

Subquery Scan on usr_3 (cost=127.69..127.75 rows=3 width=44) (actual time=0.317..0.318 rows=1 loops=1)

44. 0.003 0.317 ↑ 3.0 1 1

HashAggregate (cost=127.69..127.72 rows=3 width=85) (actual time=0.317..0.317 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
45. 0.001 0.314 ↑ 3.0 1 1

Append (cost=0.00..127.63 rows=3 width=85) (actual time=0.029..0.314 rows=1 loops=1)

46. 0.003 0.091 ↑ 1.0 1 1

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

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

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

  • Filter: ((npi_if_provider IS NULL) AND (lower(account) = 'trn8'::text))
  • Rows Removed by Filter: 422
48. 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)

49. 0.000 0.222 ↓ 0.0 0 1

Nested Loop (cost=15.78..111.08 rows=2 width=51) (actual time=0.222..0.222 rows=0 loops=1)

50. 0.000 0.222 ↓ 0.0 0 1

Nested Loop (cost=15.64..110.58 rows=2 width=51) (actual time=0.222..0.222 rows=0 loops=1)

51. 0.000 0.222 ↓ 0.0 0 1

Nested Loop (cost=15.50..110.11 rows=2 width=47) (actual time=0.222..0.222 rows=0 loops=1)

52. 0.004 0.222 ↓ 0.0 0 1

Nested Loop (cost=15.36..109.77 rows=2 width=47) (actual time=0.222..0.222 rows=0 loops=1)

  • Join Filter: (usr_5.security_role_function_fk = srf_3.id)
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.000 0.216 ↓ 0.0 0 8

Materialize (cost=15.36..108.45 rows=2 width=34) (actual time=0.027..0.027 rows=0 loops=8)

55. 0.014 0.216 ↓ 0.0 0 1

Hash Join (cost=15.36..108.44 rows=2 width=34) (actual time=0.215..0.216 rows=0 loops=1)

  • Hash Cond: (ptsa_1.provider_fk = usr_5.npi_if_provider)
56. 0.007 0.007 ↑ 3,471.0 1 1

Seq Scan on provider_to_subgroup_address ptsa_1 (cost=0.00..75.71 rows=3,471 width=8) (actual time=0.007..0.007 rows=1 loops=1)

57. 0.001 0.195 ↓ 0.0 0 1

Hash (cost=15.33..15.33 rows=2 width=30) (actual time=0.195..0.195 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
58. 0.194 0.194 ↑ 2.0 1 1

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

  • Filter: (lower(account) = 'trn8'::text)
  • Rows Removed by Filter: 422
59. 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)
60. 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)
61. 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
62. 3,730.431 3,912.414 ↓ 1.4 2,020 59,279

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

63. 0.118 181.983 ↓ 1.4 2,020 1

Subquery Scan on t3 (cost=18,907.22..18,936.68 rows=1,473 width=8) (actual time=181.654..181.983 rows=2,020 loops=1)

64. 25.330 181.865 ↓ 1.4 2,020 1

HashAggregate (cost=18,907.22..18,921.95 rows=1,473 width=8) (actual time=181.653..181.865 rows=2,020 loops=1)

  • Group Key: uma_2.currentattribution
65. 44.102 156.535 ↓ 2.9 196,094 1

Nested Loop (cost=163.12..18,570.69 rows=67,307 width=4) (actual time=0.300..156.535 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)))))
66. 82.188 112.433 ↓ 3.0 196,094 1

Hash Join (cost=35.42..10,107.36 rows=65,377 width=29) (actual time=0.038..112.433 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
67. 30.233 30.233 ↑ 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..30.233 rows=196,130 loops=1)

68. 0.004 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
69. 0.008 0.008 ↑ 66.5 17 1

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

70. 0.000 0.000 ↑ 3.0 1 196,094

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

71. 0.001 0.258 ↑ 3.0 1 1

Subquery Scan on usr_6 (cost=127.69..127.75 rows=3 width=44) (actual time=0.258..0.258 rows=1 loops=1)

72. 0.003 0.257 ↑ 3.0 1 1

HashAggregate (cost=127.69..127.72 rows=3 width=85) (actual time=0.257..0.257 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
73. 0.002 0.254 ↑ 3.0 1 1

Append (cost=0.00..127.63 rows=3 width=85) (actual time=0.034..0.254 rows=1 loops=1)

74. 0.004 0.092 ↑ 1.0 1 1

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

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

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

  • Filter: ((npi_if_provider IS NULL) AND (lower(account) = 'trn8'::text))
  • Rows Removed by Filter: 422
76. 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)

77. 0.000 0.160 ↓ 0.0 0 1

Nested Loop (cost=15.78..111.08 rows=2 width=51) (actual time=0.160..0.160 rows=0 loops=1)

78. 0.000 0.160 ↓ 0.0 0 1

Nested Loop (cost=15.64..110.58 rows=2 width=51) (actual time=0.160..0.160 rows=0 loops=1)

79. 0.001 0.160 ↓ 0.0 0 1

Nested Loop (cost=15.50..110.11 rows=2 width=47) (actual time=0.160..0.160 rows=0 loops=1)

80. 0.000 0.159 ↓ 0.0 0 1

Nested Loop (cost=15.36..109.77 rows=2 width=47) (actual time=0.159..0.159 rows=0 loops=1)

  • Join Filter: (usr_8.security_role_function_fk = srf_5.id)
81. 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)

82. 0.005 0.160 ↓ 0.0 0 8

Materialize (cost=15.36..108.45 rows=2 width=34) (actual time=0.020..0.020 rows=0 loops=8)

83. 0.007 0.155 ↓ 0.0 0 1

Hash Join (cost=15.36..108.44 rows=2 width=34) (actual time=0.154..0.155 rows=0 loops=1)

  • Hash Cond: (ptsa_2.provider_fk = usr_8.npi_if_provider)
84. 0.006 0.006 ↑ 3,471.0 1 1

Seq Scan on provider_to_subgroup_address ptsa_2 (cost=0.00..75.71 rows=3,471 width=8) (actual time=0.006..0.006 rows=1 loops=1)

85. 0.000 0.142 ↓ 0.0 0 1

Hash (cost=15.33..15.33 rows=2 width=30) (actual time=0.142..0.142 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
86. 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.013..0.142 rows=1 loops=1)

  • Filter: (lower(account) = 'trn8'::text)
  • Rows Removed by Filter: 422
87. 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)
88. 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)
89. 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
90. 2.620 28.658 ↑ 2.9 14 14

HashAggregate (cost=9,888.37..9,888.77 rows=40 width=40) (actual time=2.045..2.047 rows=14 loops=14)

  • Group Key: (uma_3.qcngroup)::text
91. 4.226 26.038 ↓ 3.5 16,424 1

Nested Loop (cost=995.72..9,853.00 rows=4,716 width=40) (actual time=2.565..26.038 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)))))
92. 4.625 21.812 ↓ 3.6 16,424 1

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

  • Hash Cond: (uma_3.populationid = pdl_3.populationid)
  • Join Filter: (uma_3.calendardate <= pdl_3."limit")
93. 15.224 17.152 ↓ 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.187..17.152 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
94. 1.928 1.928 ↑ 1.0 31,743 1

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

  • Index Cond: ((payorsourcename)::text = 'CMS Medicare ACO'::text)
95. 0.008 0.035 ↑ 66.5 17 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 17kB
96. 0.027 0.027 ↑ 66.5 17 1

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

97. 0.000 0.000 ↑ 3.0 1 16,424

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

98. 0.002 0.295 ↑ 3.0 1 1

Subquery Scan on usr_9 (cost=127.69..127.75 rows=3 width=44) (actual time=0.295..0.295 rows=1 loops=1)

99. 0.009 0.293 ↑ 3.0 1 1

HashAggregate (cost=127.69..127.72 rows=3 width=85) (actual time=0.293..0.293 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
100. 0.003 0.284 ↑ 3.0 1 1

Append (cost=0.00..127.63 rows=3 width=85) (actual time=0.046..0.284 rows=1 loops=1)

101. 0.007 0.106 ↑ 1.0 1 1

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

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

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

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

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

104. 0.001 0.175 ↓ 0.0 0 1

Nested Loop (cost=15.78..111.08 rows=2 width=51) (actual time=0.175..0.175 rows=0 loops=1)

105. 0.001 0.174 ↓ 0.0 0 1

Nested Loop (cost=15.64..110.58 rows=2 width=51) (actual time=0.174..0.174 rows=0 loops=1)

106. 0.001 0.173 ↓ 0.0 0 1

Nested Loop (cost=15.50..110.11 rows=2 width=47) (actual time=0.173..0.173 rows=0 loops=1)

107. 0.002 0.172 ↓ 0.0 0 1

Nested Loop (cost=15.36..109.77 rows=2 width=47) (actual time=0.172..0.172 rows=0 loops=1)

  • Join Filter: (usr_11.security_role_function_fk = srf_7.id)
108. 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.001..0.002 rows=8 loops=1)

109. 0.003 0.168 ↓ 0.0 0 8

Materialize (cost=15.36..108.45 rows=2 width=34) (actual time=0.021..0.021 rows=0 loops=8)

110. 0.012 0.165 ↓ 0.0 0 1

Hash Join (cost=15.36..108.44 rows=2 width=34) (actual time=0.165..0.165 rows=0 loops=1)

  • Hash Cond: (ptsa_3.provider_fk = usr_11.npi_if_provider)
111. 0.008 0.008 ↑ 3,471.0 1 1

Seq Scan on provider_to_subgroup_address ptsa_3 (cost=0.00..75.71 rows=3,471 width=8) (actual time=0.008..0.008 rows=1 loops=1)

112. 0.002 0.145 ↓ 0.0 0 1

Hash (cost=15.33..15.33 rows=2 width=30) (actual time=0.145..0.145 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
113. 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
114. 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)
115. 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)
116. 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 : 10.383 ms
Execution time : 32,175.092 ms