explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cQ3B

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 1,053.977 ↑ 1.4 72 1

Limit (cost=845,284,640.38..845,284,640.63 rows=100 width=465) (actual time=1,053.893..1,053.977 rows=72 loops=1)

2. 0.815 1,053.956 ↑ 19,119.6 72 1

Sort (cost=845,284,640.38..845,288,081.91 rows=1,376,612 width=465) (actual time=1,053.886..1,053.956 rows=72 loops=1)

  • Sort Key: i.interaction_date, interactions.id_interaction
  • Sort Method: quicksort Memory: 86kB
3. 0.445 1,053.141 ↑ 19,119.6 72 1

Nested Loop Left Join (cost=1,954,704.94..845,232,027.25 rows=1,376,612 width=465) (actual time=64.086..1,053.141 rows=72 loops=1)

  • Join Filter: (_a_id_hierarchical_data_type.id = f.id_hierarchical_data_type)
  • Rows Removed by Join Filter: 80
4. 0.580 1,052.552 ↑ 19,119.6 72 1

Nested Loop Left Join (cost=1,954,704.94..844,980,974.12 rows=1,376,612 width=453) (actual time=64.053..1,052.552 rows=72 loops=1)

5. 0.828 1,050.964 ↑ 19,119.6 72 1

Nested Loop Left Join (cost=1,954,704.52..844,268,210.09 rows=1,376,612 width=435) (actual time=63.998..1,050.964 rows=72 loops=1)

6. 0.482 1,047.400 ↑ 19,119.6 72 1

Nested Loop Left Join (cost=1,954,704.36..842,825,692.62 rows=1,376,612 width=426) (actual time=63.924..1,047.400 rows=72 loops=1)

7. 6.638 1,044.398 ↑ 19,119.6 72 1

Hash Left Join (cost=1,954,703.79..841,789,963.47 rows=1,376,612 width=378) (actual time=63.779..1,044.398 rows=72 loops=1)

  • Hash Cond: ((COALESCE(icr.id_classification, '9000'::bigint) = re.id_race) AND (COALESCE(ice.id_classification, '90000'::bigint) = re.id_ethnicity))
  • Filter: (COALESCE(re.race_ethnicity, 'Missing'::text) = 'W and H'::text)
  • Rows Removed by Filter: 2144
8.          

CTE time_period

9. 0.009 1.525 ↑ 1,000,000.0 1 1

Result (cost=0.00..285,270.27 rows=1,000,000 width=41) (actual time=1.521..1.525 rows=1 loops=1)

10. 0.142 1.516 ↑ 1,000,000.0 1 1

ProjectSet (cost=0.00..5,270.27 rows=1,000,000 width=32) (actual time=1.513..1.516 rows=1 loops=1)

11. 1.373 1.374 ↑ 1,000.0 1 1

ProjectSet (cost=0.00..5.27 rows=1,000 width=24) (actual time=1.372..1.374 rows=1 loops=1)

12. 0.001 0.001 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

13.          

CTE age_group

14. 0.038 0.038 ↑ 1.0 10 1

CTE Scan on age_groups (cost=0.12..0.33 rows=10 width=40) (actual time=0.018..0.038 rows=10 loops=1)

15.          

CTE age_groups

16. 0.026 0.026 ↑ 1.0 10 1

Values Scan on "*VALUES*" (cost=0.00..0.12 rows=10 width=40) (actual time=0.014..0.026 rows=10 loops=1)

17.          

CTE race_ethnicity

18. 0.193 1.035 ↑ 1.3 21 1

Hash Right Join (cost=21.22..41.23 rows=27 width=116) (actual time=0.745..1.035 rows=21 loops=1)

  • Hash Cond: ((e.category)::text = race_ethn.ethnicity)
19.          

CTE race_ethn

20. 0.037 0.037 ↑ 1.0 21 1

Values Scan on "*VALUES*_1" (cost=0.00..0.26 rows=21 width=100) (actual time=0.009..0.037 rows=21 loops=1)

21. 0.184 0.184 ↑ 1.0 746 1

Seq Scan on classification e (cost=0.00..16.81 rows=781 width=26) (actual time=0.004..0.184 rows=746 loops=1)

22. 0.020 0.658 ↑ 1.1 21 1

Hash (cost=20.66..20.66 rows=24 width=108) (actual time=0.658..0.658 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
23. 0.199 0.638 ↑ 1.1 21 1

Hash Right Join (cost=0.68..20.66 rows=24 width=108) (actual time=0.410..0.638 rows=21 loops=1)

  • Hash Cond: ((r_1.category)::text = race_ethn.race)
24. 0.370 0.370 ↑ 1.0 746 1

Seq Scan on classification r_1 (cost=0.00..16.81 rows=781 width=26) (actual time=0.022..0.370 rows=746 loops=1)

25. 0.013 0.069 ↑ 1.0 21 1

Hash (cost=0.42..0.42 rows=21 width=100) (actual time=0.069..0.069 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
26. 0.056 0.056 ↑ 1.0 21 1

CTE Scan on race_ethn (cost=0.00..0.42 rows=21 width=100) (actual time=0.011..0.056 rows=21 loops=1)

27.          

CTE interactions

28. 5.999 383.684 ↓ 1.4 2,448 1

Nested Loop (cost=45,077.46..1,669,074.51 rows=1,749 width=64) (actual time=3.573..383.684 rows=2,448 loops=1)

29.          

Initplan (forNested Loop)

30. 0.003 0.128 ↑ 1.0 1 1

Nested Loop (cost=0.28..5.38 rows=1 width=0) (actual time=0.128..0.128 rows=1 loops=1)

  • Join Filter: (u.id_app_role = r_2.id)
31. 0.018 0.018 ↑ 1.0 1 1

Seq Scan on app_role r_2 (cost=0.00..1.04 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=1)

  • Filter: ((permission)::text = 'ROLE_ADMIN'::text)
32. 0.107 0.107 ↑ 2.0 1 1

Index Only Scan using app_user_role_pkey on app_user_role u (cost=0.28..4.31 rows=2 width=8) (actual time=0.106..0.107 rows=1 loops=1)

  • Index Cond: (id_app_user = 0)
  • Heap Fetches: 0
33. 0.010 1.539 ↑ 1.0 1 1

Aggregate (cost=22,500.00..22,500.01 rows=1 width=8) (actual time=1.539..1.539 rows=1 loops=1)

34. 1.529 1.529 ↑ 1,000,000.0 1 1

CTE Scan on time_period (cost=0.00..20,000.00 rows=1,000,000 width=8) (actual time=1.524..1.529 rows=1 loops=1)

35. 0.003 0.004 ↑ 1.0 1 1

Aggregate (cost=22,500.00..22,500.01 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=1)

36. 0.001 0.001 ↑ 1,000,000.0 1 1

CTE Scan on time_period time_period_1 (cost=0.00..20,000.00 rows=1,000,000 width=8) (actual time=0.001..0.001 rows=1 loops=1)

37. 6.013 353.982 ↓ 1.4 2,448 1

Nested Loop (cost=71.64..1,623,105.46 rows=1,749 width=40) (actual time=3.497..353.982 rows=2,448 loops=1)

38. 20.138 328.337 ↑ 4.2 2,454 1

Hash Join (cost=71.36..1,619,971.78 rows=10,328 width=40) (actual time=3.453..328.337 rows=2,454 loops=1)

  • Hash Cond: (m.id_geography = c_1.id_contained)
39. 11.053 307.385 ↑ 15.3 59,627 1

Append (cost=0.00..1,617,503.18 rows=913,149 width=32) (actual time=2.581..307.385 rows=59,627 loops=1)

40. 0.021 0.021 ↓ 0.0 0 1

Seq Scan on mv_all_interaction m (cost=0.00..0.00 rows=1 width=32) (actual time=0.020..0.021 rows=0 loops=1)

  • Filter: ((interaction_date >= $10) AND (interaction_date <= $11) AND (interaction_date >= '2016-07-01 00:00:00-04'::timestamp with time zone) AND (id_patient_class = 10) AND (interaction_type = 1))
41. 0.005 1.718 ↓ 0.0 0 1

Bitmap Heap Scan on mv_all_interaction_old_data m_1 (cost=17,650.29..1,353,414.06 rows=519,351 width=32) (actual time=1.717..1.718 rows=0 loops=1)

  • Recheck Cond: ((interaction_date >= $10) AND (interaction_date <= $11) AND (interaction_date >= '2016-07-01 00:00:00-04'::timestamp with time zone) AND (interaction_type = 1) AND (id_patient_class = 10))
42. 1.713 1.713 ↓ 0.0 0 1

Bitmap Index Scan on idx_mv_all_interaction_old_reg (cost=0.00..17,520.46 rows=519,351 width=0) (actual time=1.713..1.713 rows=0 loops=1)

  • Index Cond: ((interaction_date >= $10) AND (interaction_date <= $11) AND (interaction_date >= '2016-07-01 00:00:00-04'::timestamp with time zone))
43. 0.090 0.090 ↓ 0.0 0 1

Index Scan using idx_mv_all_interaction_2016_q3_reg on mv_all_interaction_2016_q3 m_2 (cost=0.43..27,091.65 rows=36,214 width=32) (actual time=0.090..0.090 rows=0 loops=1)

  • Index Cond: ((interaction_date >= $10) AND (interaction_date <= $11) AND (interaction_date >= '2016-07-01 00:00:00-04'::timestamp with time zone))
44. 0.076 0.076 ↓ 0.0 0 1

Index Scan using idx_mv_all_interaction_2016_q4_reg on mv_all_interaction_2016_q4 m_3 (cost=0.43..30,718.72 rows=37,344 width=32) (actual time=0.076..0.076 rows=0 loops=1)

  • Index Cond: ((interaction_date >= $10) AND (interaction_date <= $11) AND (interaction_date >= '2016-07-01 00:00:00-04'::timestamp with time zone))
45. 0.081 0.081 ↓ 0.0 0 1

Index Scan using idx_mv_all_interaction_2017_q1_reg on mv_all_interaction_2017_q1 m_4 (cost=0.43..33,818.86 rows=38,149 width=32) (actual time=0.081..0.081 rows=0 loops=1)

  • Index Cond: ((interaction_date >= $10) AND (interaction_date <= $11) AND (interaction_date >= '2016-07-01 00:00:00-04'::timestamp with time zone))
46. 0.081 0.081 ↓ 0.0 0 1

Index Scan using idx_mv_all_interaction_2017_q2_reg on mv_all_interaction_2017_q2 m_5 (cost=0.43..34,060.79 rows=38,467 width=32) (actual time=0.081..0.081 rows=0 loops=1)

  • Index Cond: ((interaction_date >= $10) AND (interaction_date <= $11) AND (interaction_date >= '2016-07-01 00:00:00-04'::timestamp with time zone))
47. 0.091 0.091 ↓ 0.0 0 1

Index Scan using idx_mv_all_interaction_2017_q3_reg on mv_all_interaction_2017_q3 m_6 (cost=0.43..38,109.45 rows=38,497 width=32) (actual time=0.091..0.091 rows=0 loops=1)

  • Index Cond: ((interaction_date >= $10) AND (interaction_date <= $11) AND (interaction_date >= '2016-07-01 00:00:00-04'::timestamp with time zone))
48. 0.087 0.087 ↓ 0.0 0 1

Index Scan using idx_mv_all_interaction_2017_q4_reg on mv_all_interaction_2017_q4 m_7 (cost=0.43..32,505.67 rows=44,960 width=32) (actual time=0.086..0.087 rows=0 loops=1)

  • Index Cond: ((interaction_date >= $10) AND (interaction_date <= $11) AND (interaction_date >= '2016-07-01 00:00:00-04'::timestamp with time zone))
49. 0.067 0.067 ↓ 0.0 0 1

Index Scan using idx_mv_all_interaction_2018_q1_reg on mv_all_interaction_2018_q1 m_8 (cost=0.43..23,181.65 rows=43,865 width=32) (actual time=0.067..0.067 rows=0 loops=1)

  • Index Cond: ((interaction_date >= $10) AND (interaction_date <= $11) AND (interaction_date >= '2016-07-01 00:00:00-04'::timestamp with time zone))
50. 0.091 0.091 ↓ 0.0 0 1

Index Scan using idx_mv_all_interaction_2018_q2_reg on mv_all_interaction_2018_q2 m_9 (cost=0.43..16,206.27 rows=42,090 width=32) (actual time=0.091..0.091 rows=0 loops=1)

  • Index Cond: ((interaction_date >= $10) AND (interaction_date <= $11) AND (interaction_date >= '2016-07-01 00:00:00-04'::timestamp with time zone))
51. 0.073 0.073 ↓ 0.0 0 1

Index Scan using idx_mv_all_interaction_2018_q3_reg on mv_all_interaction_2018_q3 m_10 (cost=0.43..16,354.27 rows=43,199 width=32) (actual time=0.073..0.073 rows=0 loops=1)

  • Index Cond: ((interaction_date >= $10) AND (interaction_date <= $11) AND (interaction_date >= '2016-07-01 00:00:00-04'::timestamp with time zone))
52. 293.629 293.629 ↓ 1.9 59,627 1

Index Scan using idx_mv_all_interaction_2018_q4_reg on mv_all_interaction_2018_q4 m_11 (cost=0.43..12,009.19 rows=31,008 width=32) (actual time=0.102..293.629 rows=59,627 loops=1)

  • Index Cond: ((interaction_date >= $10) AND (interaction_date <= $11) AND (interaction_date >= '2016-07-01 00:00:00-04'::timestamp with time zone))
53. 0.091 0.091 ↓ 0.0 0 1

Index Scan using idx_mv_all_interaction_2019_q1_reg on mv_all_interaction_2019_q1 m_12 (cost=0.12..8.15 rows=1 width=32) (actual time=0.090..0.091 rows=0 loops=1)

  • Index Cond: ((interaction_date >= $10) AND (interaction_date <= $11) AND (interaction_date >= '2016-07-01 00:00:00-04'::timestamp with time zone))
54. 0.060 0.060 ↓ 0.0 0 1

Index Scan using idx_mv_all_interaction_2019_q2_reg on mv_all_interaction_2019_q2 m_13 (cost=0.12..8.15 rows=1 width=32) (actual time=0.060..0.060 rows=0 loops=1)

  • Index Cond: ((interaction_date >= $10) AND (interaction_date <= $11) AND (interaction_date >= '2016-07-01 00:00:00-04'::timestamp with time zone))
55. 0.038 0.038 ↓ 0.0 0 1

Index Scan using idx_mv_all_interaction_2019_q3_reg on mv_all_interaction_2019_q3 m_14 (cost=0.12..8.15 rows=1 width=32) (actual time=0.038..0.038 rows=0 loops=1)

  • Index Cond: ((interaction_date >= $10) AND (interaction_date <= $11) AND (interaction_date >= '2016-07-01 00:00:00-04'::timestamp with time zone))
56. 0.038 0.038 ↓ 0.0 0 1

Index Scan using idx_mv_all_interaction_2019_q4_reg on mv_all_interaction_2019_q4 m_15 (cost=0.12..8.15 rows=1 width=32) (actual time=0.038..0.038 rows=0 loops=1)

  • Index Cond: ((interaction_date >= $10) AND (interaction_date <= $11) AND (interaction_date >= '2016-07-01 00:00:00-04'::timestamp with time zone))
57. 0.205 0.814 ↓ 1.3 670 1

Hash (cost=65.04..65.04 rows=506 width=8) (actual time=0.814..0.814 rows=670 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
58. 0.609 0.609 ↓ 1.3 670 1

Index Only Scan using containment_pkey on containment c_1 (cost=39.56..65.04 rows=506 width=8) (actual time=0.340..0.609 rows=670 loops=1)

  • Index Cond: (id_container = 19)
  • Filter: ($5 OR (hashed SubPlan 7))
  • Heap Fetches: 0
59.          

SubPlan (forIndex Only Scan)

60. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=29.73..39.06 rows=32 width=8) (never executed)

61. 0.000 0.000 ↓ 0.0 0

Unique (cost=29.31..29.32 rows=2 width=8) (never executed)

62. 0.000 0.000 ↓ 0.0 0

Sort (cost=29.31..29.31 rows=2 width=8) (never executed)

  • Sort Key: o.id_geography
63. 0.000 0.000 ↓ 0.0 0

Append (cost=0.56..29.30 rows=2 width=8) (never executed)

64. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.56..16.62 rows=1 width=8) (never executed)

65. 0.000 0.000 ↓ 0.0 0

Index Scan using app_user_pkey on app_user u_1 (cost=0.28..8.30 rows=1 width=8) (never executed)

  • Index Cond: (id = 0)
66. 0.000 0.000 ↓ 0.0 0

Index Scan using organization_pkey on organization o (cost=0.28..8.29 rows=1 width=16) (never executed)

  • Index Cond: (id = u_1.id_organization)
67. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.56..12.66 rows=1 width=8) (never executed)

68. 0.000 0.000 ↓ 0.0 0

Index Scan using app_user_pkey on app_user u_2 (cost=0.28..8.30 rows=1 width=8) (never executed)

  • Index Cond: (id = 0)
69. 0.000 0.000 ↓ 0.0 0

Index Only Scan using authorized_region_pkey on authorized_region r_3 (cost=0.28..4.34 rows=2 width=16) (never executed)

  • Index Cond: ((id_organization = u_2.id_organization) AND (type = 'FULL'::text))
  • Heap Fetches: 0
70. 0.000 0.000 ↓ 0.0 0

Index Only Scan using containment_pkey on containment c (cost=0.42..4.70 rows=16 width=16) (never executed)

  • Index Cond: (id_container = o.id_geography)
  • Heap Fetches: 0
71. 19.632 19.632 ↑ 1.0 1 2,454

Index Scan using facility_pkey on facility f_1 (cost=0.28..0.30 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=2,454)

  • Index Cond: (id = m.id_facility)
  • Filter: (id_hierarchical_data_type = ANY ('{2,3}'::bigint[]))
  • Rows Removed by Filter: 0
72. 22.032 22.032 ↑ 1.0 1 2,448

Index Scan using geography_pkey on geographies g (cost=0.41..0.55 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=2,448)

  • Index Cond: (id = m.id_geography)
73. 3.223 939.315 ↑ 16,772.8 2,216 1

Nested Loop Left Join (cost=316.51..20,699,769.62 rows=37,168,519 width=218) (actual time=13.802..939.315 rows=2,216 loops=1)

74. 2.924 900.636 ↑ 115.1 2,216 1

Nested Loop Left Join (cost=314.94..182,539.34 rows=254,966 width=210) (actual time=13.730..900.636 rows=2,216 loops=1)

75. 4.857 764.752 ↓ 1.3 2,216 1

Nested Loop Left Join (cost=313.36..41,794.95 rows=1,749 width=202) (actual time=12.343..764.752 rows=2,216 loops=1)

76. 8.908 748.815 ↓ 1.3 2,216 1

Nested Loop Left Join (cost=312.94..35,646.50 rows=1,749 width=202) (actual time=12.218..748.815 rows=2,216 loops=1)

  • Join Filter: ((i.age_at_interaction >= ag.low) AND (i.age_at_interaction <= ag.high))
  • Rows Removed by Join Filter: 22039
  • Filter: (COALESCE(ag.age_group, 'Missing'::text) <> 'Exclude'::text)
  • Rows Removed by Filter: 232
77. 5.454 732.563 ↓ 1.4 2,448 1

Hash Join (cost=312.94..35,165.43 rows=1,749 width=170) (actual time=12.177..732.563 rows=2,448 loops=1)

  • Hash Cond: (interactions.id_facility = f.id)
78. 6.532 718.879 ↓ 1.4 2,448 1

Nested Loop (cost=2.29..34,850.18 rows=1,749 width=162) (actual time=3.877..718.879 rows=2,448 loops=1)

79. 6.428 611.979 ↓ 1.4 2,448 1

Nested Loop (cost=1.72..28,307.95 rows=1,749 width=160) (actual time=3.814..611.979 rows=2,448 loops=1)

80. 6.944 529.663 ↓ 1.4 2,448 1

Nested Loop (cost=1.15..21,751.56 rows=1,749 width=144) (actual time=3.754..529.663 rows=2,448 loops=1)

  • Join Filter: (interactions.id_interaction = r.id)
81. 5.139 454.175 ↓ 1.4 2,448 1

Nested Loop (cost=0.57..15,058.89 rows=1,749 width=60) (actual time=3.674..454.175 rows=2,448 loops=1)

82. 387.836 387.836 ↓ 1.4 2,448 1

CTE Scan on interactions (cost=0.00..34.98 rows=1,749 width=32) (actual time=3.579..387.836 rows=2,448 loops=1)

83. 61.200 61.200 ↑ 1.0 1 2,448

Index Scan using interaction_pkey on interaction i (cost=0.57..8.59 rows=1 width=28) (actual time=0.025..0.025 rows=1 loops=2,448)

  • Index Cond: (id = interactions.id_interaction)
84. 68.544 68.544 ↑ 1.0 1 2,448

Index Scan using registration_pkey on registration r (cost=0.57..3.81 rows=1 width=84) (actual time=0.028..0.028 rows=1 loops=2,448)

  • Index Cond: (id = i.id)
85. 75.888 75.888 ↑ 1.0 1 2,448

Index Scan using patient_detail_pkey on patient_detail pd (cost=0.57..3.75 rows=1 width=32) (actual time=0.031..0.031 rows=1 loops=2,448)

  • Index Cond: (id = i.id_patient_detail)
86. 100.368 100.368 ↑ 1.0 1 2,448

Index Scan using patient_pkey on patient p (cost=0.57..3.74 rows=1 width=18) (actual time=0.041..0.041 rows=1 loops=2,448)

  • Index Cond: (id = pd.id_patient)
87. 1.839 8.230 ↓ 1.0 6,128 1

Hash (cost=234.18..234.18 rows=6,118 width=16) (actual time=8.230..8.230 rows=6,128 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 351kB
88. 6.391 6.391 ↓ 1.0 6,128 1

Seq Scan on facility f (cost=0.00..234.18 rows=6,118 width=16) (actual time=0.017..6.391 rows=6,128 loops=1)

89. 7.344 7.344 ↑ 1.0 10 2,448

CTE Scan on age_group ag (cost=0.00..0.20 rows=10 width=40) (actual time=0.000..0.003 rows=10 loops=2,448)

90. 11.080 11.080 ↑ 1.0 1 2,216

Index Only Scan using geography_pkey on geographies county (cost=0.41..3.52 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=2,216)

  • Index Cond: (id = interactions.id_geo_county)
  • Heap Fetches: 0
91. 10.012 132.960 ↑ 146.0 1 2,216

Hash Semi Join (cost=1.58..80.59 rows=146 width=16) (actual time=0.058..0.060 rows=1 loops=2,216)

  • Hash Cond: (ice.id_classification = race_ethnicity_1.id_ethnicity)
92. 121.880 121.880 ↑ 166.8 8 2,216

Index Only Scan using uk_interaction_classification on interaction_classification ice (cost=0.70..74.59 rows=1,334 width=16) (actual time=0.032..0.055 rows=8 loops=2,216)

  • Index Cond: (id_interaction = interactions.id_interaction)
  • Heap Fetches: 17864
93. 0.011 1.068 ↑ 1.3 21 1

Hash (cost=0.54..0.54 rows=27 width=8) (actual time=1.067..1.068 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
94. 1.057 1.057 ↑ 1.3 21 1

CTE Scan on race_ethnicity race_ethnicity_1 (cost=0.00..0.54 rows=27 width=8) (actual time=0.748..1.057 rows=21 loops=1)

95. 8.847 35.456 ↑ 146.0 1 2,216

Hash Semi Join (cost=1.58..80.59 rows=146 width=16) (actual time=0.013..0.016 rows=1 loops=2,216)

  • Hash Cond: (icr.id_classification = race_ethnicity.id_race)
96. 26.592 26.592 ↑ 166.8 8 2,216

Index Only Scan using uk_interaction_classification on interaction_classification icr (cost=0.70..74.59 rows=1,334 width=16) (actual time=0.007..0.012 rows=8 loops=2,216)

  • Index Cond: (id_interaction = interactions.id_interaction)
  • Heap Fetches: 17862
97. 0.010 0.017 ↑ 1.3 21 1

Hash (cost=0.54..0.54 rows=27 width=8) (actual time=0.016..0.017 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
98. 0.007 0.007 ↑ 1.3 21 1

CTE Scan on race_ethnicity (cost=0.00..0.54 rows=27 width=8) (actual time=0.001..0.007 rows=21 loops=1)

99. 0.012 0.021 ↑ 1.3 21 1

Hash (cost=0.54..0.54 rows=27 width=48) (actual time=0.021..0.021 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
100. 0.009 0.009 ↑ 1.3 21 1

CTE Scan on race_ethnicity re (cost=0.00..0.54 rows=27 width=48) (actual time=0.002..0.009 rows=21 loops=1)

101.          

SubPlan (forHash Left Join)

102. 0.936 19.008 ↑ 1.0 1 72

Aggregate (cost=133.33..133.34 rows=1 width=32) (actual time=0.264..0.264 rows=1 loops=72)

103. 0.000 18.072 ↑ 21.0 1 72

Unique (cost=132.96..133.06 rows=21 width=18) (actual time=0.250..0.251 rows=1 loops=72)

104.          

Initplan (forUnique)

105. 0.007 8.442 ↑ 1.0 1 1

Result (cost=52.67..52.68 rows=1 width=1) (actual time=8.442..8.442 rows=1 loops=1)

106.          

Initplan (forResult)

107. 0.288 8.373 ↓ 0.0 0 1

Nested Loop (cost=17.57..47.29 rows=1 width=0) (actual time=8.372..8.373 rows=0 loops=1)

108.          

Initplan (forNested Loop)

109. 0.188 0.188 ↑ 1.0 1 1

Index Scan using app_user_pkey on app_user au (cost=0.28..8.30 rows=1 width=8) (actual time=0.187..0.188 rows=1 loops=1)

  • Index Cond: (id = 0)
110. 0.185 5.377 ↓ 315.0 630 1

Nested Loop (cost=9.00..38.36 rows=2 width=8) (actual time=0.336..5.377 rows=630 loops=1)

111. 0.028 0.232 ↓ 2.0 2 1

Nested Loop (cost=8.58..32.85 rows=1 width=8) (actual time=0.191..0.232 rows=2 loops=1)

112. 0.027 0.027 ↑ 1.0 1 1

Seq Scan on acl_class ac (cost=0.00..1.10 rows=1 width=8) (actual time=0.020..0.027 rows=1 loops=1)

  • Filter: ((classname)::text = 'com.hmsinc.epicenter.model.analysis.classify.Classifier'::text)
  • Rows Removed by Filter: 7
113. 0.011 0.177 ↑ 1.0 2 1

Nested Loop (cost=8.58..31.73 rows=2 width=16) (actual time=0.146..0.177 rows=2 loops=1)

114. 0.025 0.066 ↑ 1.0 2 1

Bitmap Heap Scan on classifier c_2 (cost=8.30..15.11 rows=2 width=8) (actual time=0.057..0.066 rows=2 loops=1)

  • Recheck Cond: ((name)::text = ANY ('{Race,Ethnicity}'::text[]))
  • Heap Blocks: exact=2
115. 0.041 0.041 ↑ 1.0 2 1

Bitmap Index Scan on classifier_name_key (cost=0.00..8.29 rows=2 width=0) (actual time=0.041..0.041 rows=2 loops=1)

  • Index Cond: ((name)::text = ANY ('{Race,Ethnicity}'::text[]))
116. 0.100 0.100 ↑ 1.0 1 2

Index Scan using acl_secured_object_id_acl_class_key on acl_secured_object aso (cost=0.28..8.30 rows=1 width=24) (actual time=0.049..0.050 rows=1 loops=2)

  • Index Cond: ((id_acl_class = ac.id) AND (objectid = c_2.id))
117. 4.960 4.960 ↓ 8.1 315 2

Index Scan using idx_acl_entry_1 on acl_entry ae (cost=0.42..5.11 rows=39 width=16) (actual time=0.100..2.480 rows=315 loops=2)

  • Index Cond: (id_acl_secured_object = aso.id)
  • Filter: (permission > 0)
  • Rows Removed by Filter: 47
118. 2.520 2.520 ↓ 0.0 0 630

Index Scan using acl_sid_pkey on acl_sid asid (cost=0.28..0.31 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=630)

  • Index Cond: (id = ae.id_acl_sid)
  • Filter: (((sid)::text = 'User:0'::text) OR ((sid)::text = ('Org:'::text || ($15)::text)))
  • Rows Removed by Filter: 1
119. 0.004 0.062 ↑ 1.0 1 1

Nested Loop (cost=0.28..5.38 rows=1 width=0) (actual time=0.062..0.062 rows=1 loops=1)

  • Join Filter: (u_3.id_app_role = r_4.id)
120. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on app_role r_4 (cost=0.00..1.04 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=1)

  • Filter: ((permission)::text = 'ROLE_ADMIN'::text)
121. 0.044 0.044 ↑ 2.0 1 1

Index Only Scan using app_user_role_pkey on app_user_role u_3 (cost=0.28..4.31 rows=2 width=8) (actual time=0.044..0.044 rows=1 loops=1)

  • Index Cond: (id_app_user = 0)
  • Heap Fetches: 0
122. 1.872 17.496 ↑ 21.0 1 72

Sort (cost=80.28..80.33 rows=21 width=18) (actual time=0.242..0.243 rows=1 loops=72)

  • Sort Key: c_3.category
  • Sort Method: quicksort Memory: 25kB
123. 8.928 15.624 ↑ 21.0 1 72

Result (cost=5.24..79.82 rows=21 width=18) (actual time=0.209..0.217 rows=1 loops=72)

  • One-Time Filter: $22
124. 0.792 6.696 ↑ 21.0 1 72

Nested Loop (cost=5.24..79.82 rows=21 width=18) (actual time=0.085..0.093 rows=1 loops=72)

125. 0.792 3.744 ↑ 2.0 6 72

Nested Loop (cost=4.54..21.92 rows=12 width=26) (actual time=0.048..0.052 rows=6 loops=72)

126. 1.080 1.080 ↑ 1.0 1 72

Index Scan using classifier_name_key on classifier cr (cost=0.14..8.16 rows=1 width=8) (actual time=0.015..0.015 rows=1 loops=72)

  • Index Cond: ((name)::text = 'Race'::text)
127. 1.080 1.872 ↑ 2.7 6 72

Bitmap Heap Scan on classification c_3 (cost=4.40..13.60 rows=16 width=34) (actual time=0.024..0.026 rows=6 loops=72)

  • Recheck Cond: (id_classifier = cr.id)
  • Heap Blocks: exact=72
128. 0.792 0.792 ↑ 2.7 6 72

Bitmap Index Scan on classification_id_classifier_key (cost=0.00..4.40 rows=16 width=0) (actual time=0.011..0.011 rows=6 loops=72)

  • Index Cond: (id_classifier = cr.id)
129. 2.160 2.160 ↓ 0.0 0 432

Index Only Scan using uk_interaction_classification on interaction_classification ic (cost=0.70..4.77 rows=5 width=8) (actual time=0.005..0.005 rows=0 loops=432)

  • Index Cond: ((id_interaction = i.id) AND (id_classification = c_3.id))
  • Heap Fetches: 72
130. 0.504 9.216 ↑ 1.0 1 72

Aggregate (cost=133.33..133.34 rows=1 width=32) (actual time=0.128..0.128 rows=1 loops=72)

131. 0.000 8.712 ↑ 21.0 1 72

Unique (cost=132.96..133.06 rows=21 width=18) (actual time=0.120..0.121 rows=1 loops=72)

132.          

Initplan (forUnique)

133. 0.006 3.584 ↑ 1.0 1 1

Result (cost=52.67..52.68 rows=1 width=1) (actual time=3.583..3.584 rows=1 loops=1)

134.          

Initplan (forResult)

135. 0.487 3.547 ↓ 0.0 0 1

Nested Loop (cost=17.57..47.29 rows=1 width=0) (actual time=3.547..3.547 rows=0 loops=1)

136.          

Initplan (forNested Loop)

137. 0.029 0.029 ↑ 1.0 1 1

Index Scan using app_user_pkey on app_user au_1 (cost=0.28..8.30 rows=1 width=8) (actual time=0.028..0.029 rows=1 loops=1)

  • Index Cond: (id = 0)
138. 0.172 1.141 ↓ 315.0 630 1

Nested Loop (cost=9.00..38.36 rows=2 width=8) (actual time=0.119..1.141 rows=630 loops=1)

139. 0.015 0.103 ↓ 2.0 2 1

Nested Loop (cost=8.58..32.85 rows=1 width=8) (actual time=0.087..0.103 rows=2 loops=1)

140. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on acl_class ac_1 (cost=0.00..1.10 rows=1 width=8) (actual time=0.009..0.012 rows=1 loops=1)

  • Filter: ((classname)::text = 'com.hmsinc.epicenter.model.analysis.classify.Classifier'::text)
  • Rows Removed by Filter: 7
141. 0.008 0.076 ↑ 1.0 2 1

Nested Loop (cost=8.58..31.73 rows=2 width=16) (actual time=0.063..0.076 rows=2 loops=1)

142. 0.009 0.044 ↑ 1.0 2 1

Bitmap Heap Scan on classifier c_4 (cost=8.30..15.11 rows=2 width=8) (actual time=0.040..0.044 rows=2 loops=1)

  • Recheck Cond: ((name)::text = ANY ('{Race,Ethnicity}'::text[]))
  • Heap Blocks: exact=2
143. 0.035 0.035 ↑ 1.0 2 1

Bitmap Index Scan on classifier_name_key (cost=0.00..8.29 rows=2 width=0) (actual time=0.035..0.035 rows=2 loops=1)

  • Index Cond: ((name)::text = ANY ('{Race,Ethnicity}'::text[]))
144. 0.024 0.024 ↑ 1.0 1 2

Index Scan using acl_secured_object_id_acl_class_key on acl_secured_object aso_1 (cost=0.28..8.30 rows=1 width=24) (actual time=0.012..0.012 rows=1 loops=2)

  • Index Cond: ((id_acl_class = ac_1.id) AND (objectid = c_4.id))
145. 0.866 0.866 ↓ 8.1 315 2

Index Scan using idx_acl_entry_1 on acl_entry ae_1 (cost=0.42..5.11 rows=39 width=16) (actual time=0.025..0.433 rows=315 loops=2)

  • Index Cond: (id_acl_secured_object = aso_1.id)
  • Filter: (permission > 0)
  • Rows Removed by Filter: 47
146. 1.890 1.890 ↓ 0.0 0 630

Index Scan using acl_sid_pkey on acl_sid asid_1 (cost=0.28..0.31 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=630)

  • Index Cond: (id = ae_1.id_acl_sid)
  • Filter: (((sid)::text = 'User:0'::text) OR ((sid)::text = ('Org:'::text || ($26)::text)))
  • Rows Removed by Filter: 1
147. 0.004 0.031 ↑ 1.0 1 1

Nested Loop (cost=0.28..5.38 rows=1 width=0) (actual time=0.031..0.031 rows=1 loops=1)

  • Join Filter: (u_4.id_app_role = r_5.id)
148. 0.007 0.007 ↑ 1.0 1 1

Seq Scan on app_role r_5 (cost=0.00..1.04 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1)

  • Filter: ((permission)::text = 'ROLE_ADMIN'::text)
149. 0.020 0.020 ↑ 2.0 1 1

Index Only Scan using app_user_role_pkey on app_user_role u_4 (cost=0.28..4.31 rows=2 width=8) (actual time=0.020..0.020 rows=1 loops=1)

  • Index Cond: (id_app_user = 0)
  • Heap Fetches: 0
150. 0.504 8.424 ↑ 21.0 1 72

Sort (cost=80.28..80.33 rows=21 width=18) (actual time=0.117..0.117 rows=1 loops=72)

  • Sort Key: c_5.category
  • Sort Method: quicksort Memory: 25kB
151. 4.032 7.920 ↑ 21.0 1 72

Result (cost=5.24..79.82 rows=21 width=18) (actual time=0.100..0.110 rows=1 loops=72)

  • One-Time Filter: $33
152. 0.360 3.888 ↑ 21.0 1 72

Nested Loop (cost=5.24..79.82 rows=21 width=18) (actual time=0.045..0.054 rows=1 loops=72)

153. 0.576 2.664 ↑ 6.0 2 72

Nested Loop (cost=4.54..21.92 rows=12 width=26) (actual time=0.033..0.037 rows=2 loops=72)

154. 0.648 0.648 ↑ 1.0 1 72

Index Scan using classifier_name_key on classifier cr_1 (cost=0.14..8.16 rows=1 width=8) (actual time=0.009..0.009 rows=1 loops=72)

  • Index Cond: ((name)::text = 'Ethnicity'::text)
155. 0.720 1.440 ↑ 8.0 2 72

Bitmap Heap Scan on classification c_5 (cost=4.40..13.60 rows=16 width=34) (actual time=0.018..0.020 rows=2 loops=72)

  • Recheck Cond: (id_classifier = cr_1.id)
  • Heap Blocks: exact=144
156. 0.720 0.720 ↑ 8.0 2 72

Bitmap Index Scan on classification_id_classifier_key (cost=0.00..4.40 rows=16 width=0) (actual time=0.010..0.010 rows=2 loops=72)

  • Index Cond: (id_classifier = cr_1.id)
157. 0.864 0.864 ↓ 0.0 0 144

Index Only Scan using uk_interaction_classification on interaction_classification ic_1 (cost=0.70..4.77 rows=5 width=8) (actual time=0.006..0.006 rows=0 loops=144)

  • Index Cond: ((id_interaction = i.id) AND (id_classification = c_5.id))
  • Heap Fetches: 72
158. 0.936 5.616 ↑ 1.0 1 72

Aggregate (cost=34.94..34.95 rows=1 width=32) (actual time=0.078..0.078 rows=1 loops=72)

159. 0.720 4.680 ↓ 0.0 0 72

Nested Loop (cost=5.18..34.90 rows=14 width=28) (actual time=0.065..0.065 rows=0 loops=72)

160. 0.504 1.944 ↑ 1.1 7 72

Nested Loop (cost=4.48..21.98 rows=8 width=36) (actual time=0.022..0.027 rows=7 loops=72)

161. 0.648 0.648 ↑ 1.0 1 72

Index Scan using classifier_pkey on classifier cl (cost=0.14..8.16 rows=1 width=36) (actual time=0.009..0.009 rows=1 loops=72)

  • Index Cond: (id = 76)
162. 0.432 0.792 ↑ 1.1 7 72

Bitmap Heap Scan on classification c_6 (cost=4.34..13.74 rows=8 width=16) (actual time=0.008..0.011 rows=7 loops=72)

  • Recheck Cond: (id_classifier = 76)
  • Heap Blocks: exact=72
163. 0.360 0.360 ↑ 1.1 7 72

Bitmap Index Scan on classification_id_classifier_key (cost=0.00..4.33 rows=8 width=0) (actual time=0.005..0.005 rows=7 loops=72)

  • Index Cond: (id_classifier = 76)
164. 2.016 2.016 ↓ 0.0 0 504

Index Only Scan using uk_interaction_classification on interaction_classification ic_2 (cost=0.70..5.26 rows=5 width=8) (actual time=0.004..0.004 rows=0 loops=504)

  • Index Cond: ((id_interaction = r.id) AND (id_classification = c_6.id))
  • Heap Fetches: 1
165. 0.792 4.032 ↑ 1.0 1 72

Aggregate (cost=26.70..26.71 rows=1 width=32) (actual time=0.056..0.056 rows=1 loops=72)

166. 0.576 3.240 ↓ 0.0 0 72

Nested Loop (cost=5.04..26.66 rows=14 width=18) (actual time=0.045..0.045 rows=0 loops=72)

167. 0.360 0.648 ↑ 1.1 7 72

Bitmap Heap Scan on classification c_7 (cost=4.34..13.74 rows=8 width=26) (actual time=0.007..0.009 rows=7 loops=72)

  • Recheck Cond: (id_classifier = 76)
  • Heap Blocks: exact=72
168. 0.288 0.288 ↑ 1.1 7 72

Bitmap Index Scan on classification_id_classifier_key (cost=0.00..4.33 rows=8 width=0) (actual time=0.004..0.004 rows=7 loops=72)

  • Index Cond: (id_classifier = 76)
169. 2.016 2.016 ↓ 0.0 0 504

Index Only Scan using uk_interaction_classification on interaction_classification ic_3 (cost=0.70..5.26 rows=5 width=8) (actual time=0.004..0.004 rows=0 loops=504)

  • Index Cond: ((id_interaction = r.id) AND (id_classification = c_7.id))
  • Heap Fetches: 1
170. 2.160 15.840 ↑ 1.0 1 72

Aggregate (cost=172.11..172.12 rows=1 width=32) (actual time=0.220..0.220 rows=1 loops=72)

171. 13.680 13.680 ↑ 89.0 1 72

Index Scan using idx_observation_4 on observation obs (cost=0.57..171.88 rows=89 width=77) (actual time=0.136..0.190 rows=1 loops=72)

  • Index Cond: (id_interaction = r.id)
  • Filter: (id_standardized_code = 8)
  • Rows Removed by Filter: 16
172. 0.864 44.712 ↑ 1.0 1 72

Aggregate (cost=94.37..94.38 rows=1 width=32) (actual time=0.621..0.621 rows=1 loops=72)

173. 1.368 43.848 ↓ 0.0 0 72

Nested Loop (cost=19.00..93.42 rows=63 width=46) (actual time=0.600..0.609 rows=0 loops=72)

174. 10.005 32.112 ↑ 1.5 24 72

Hash Join (cost=18.30..37.29 rows=37 width=54) (actual time=0.256..0.446 rows=24 loops=72)

  • Hash Cond: (c_8.id_classifier = cl_1.id)
175. 22.032 22.032 ↑ 1.0 746 72

Seq Scan on classification c_8 (cost=0.00..16.81 rows=781 width=34) (actual time=0.007..0.306 rows=746 loops=72)

176. 0.013 0.075 ↑ 1.0 3 1

Hash (cost=18.26..18.26 rows=3 width=36) (actual time=0.075..0.075 rows=3 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
177. 0.033 0.062 ↑ 1.0 3 1

Bitmap Heap Scan on classifier cl_1 (cost=8.44..18.26 rows=3 width=36) (actual time=0.042..0.062 rows=3 loops=1)

  • Recheck Cond: ((name)::text = ANY ('{"Diagnosis Opioid-Related Evidence Classifier","Medical Note Opioid-Related Evidence Classifier","Chief Complaint Opioid-Related Evidence Classifier"}'::text[]))
  • Heap Blocks: exact=2
178. 0.029 0.029 ↑ 1.0 3 1

Bitmap Index Scan on classifier_name_key (cost=0.00..8.44 rows=3 width=0) (actual time=0.029..0.029 rows=3 loops=1)

  • Index Cond: ((name)::text = ANY ('{"Diagnosis Opioid-Related Evidence Classifier","Medical Note Opioid-Related Evidence Classifier","Chief Complaint Opioid-Related Evidence Classifier"}'::text[]))
179. 10.368 10.368 ↓ 0.0 0 1,728

Index Only Scan using uk_interaction_classification on interaction_classification ic_4 (cost=0.70..4.77 rows=5 width=8) (actual time=0.006..0.006 rows=0 loops=1,728)

  • Index Cond: ((id_interaction = r.id) AND (id_classification = c_8.id))
  • Heap Fetches: 3
180. 0.936 2.520 ↑ 1.0 1 72

Nested Loop (cost=0.56..0.73 rows=1 width=56) (actual time=0.034..0.035 rows=1 loops=72)

181. 0.936 0.936 ↑ 1.0 1 72

Index Only Scan using data_source_pkey on data_source (cost=0.28..0.30 rows=1 width=8) (actual time=0.012..0.013 rows=1 loops=72)

  • Index Cond: (id = f.id)
  • Heap Fetches: 0
182. 0.648 0.648 ↑ 1.0 1 72

Index Scan using facility_pkey on facility (cost=0.28..0.43 rows=1 width=56) (actual time=0.009..0.009 rows=1 loops=72)

  • Index Cond: (id = data_source.id)
183. 1.368 2.736 ↑ 1.0 1 72

Hash Join (cost=0.17..1.20 rows=1 width=17) (actual time=0.037..0.038 rows=1 loops=72)

  • Hash Cond: (gender.id = attribute.id)
184. 0.216 0.216 ↑ 1.0 3 72

Seq Scan on gender (cost=0.00..1.03 rows=3 width=8) (actual time=0.002..0.003 rows=3 loops=72)

185. 0.432 1.152 ↑ 1.0 1 72

Hash (cost=0.15..0.15 rows=1 width=17) (actual time=0.016..0.016 rows=1 loops=72)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
186. 0.720 0.720 ↑ 1.0 1 72

Index Scan using attribute_pkey on attribute (cost=0.14..0.15 rows=1 width=17) (actual time=0.010..0.010 rows=1 loops=72)

  • Index Cond: (id = interactions.id_gender)
187. 1.008 1.008 ↑ 1.0 1 72

Index Scan using geography_pkey on geographies (cost=0.41..0.52 rows=1 width=26) (actual time=0.014..0.014 rows=1 loops=72)

  • Index Cond: (id = interactions.id_geo_county)
  • Filter: (type = ANY ('{X,R,J,C,A,O,S,Z}'::bpchar[]))
188. 0.124 0.144 ↑ 6.5 2 72

Materialize (cost=0.00..1.19 rows=13 width=20) (actual time=0.001..0.002 rows=2 loops=72)

189. 0.020 0.020 ↑ 4.3 3 1

Seq Scan on hierarchical_provider_type _a_id_hierarchical_data_type (cost=0.00..1.13 rows=13 width=20) (actual time=0.016..0.020 rows=3 loops=1)