explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mpIL

Settings
# exclusive inclusive rows x rows loops node
1. 0.007 274,875.244 ↑ 1.4 72 1

Limit (cost=1,477,765,855.78..1,477,765,856.03 rows=100 width=464) (actual time=274,875.223..274,875.244 rows=72 loops=1)

2. 0.269 274,875.237 ↑ 22,830.9 72 1

Sort (cost=1,477,765,855.78..1,477,769,965.34 rows=1,643,824 width=464) (actual time=274,875.220..274,875.237 rows=72 loops=1)

  • Sort Key: i.interaction_date, interactions.id_interaction
  • Sort Method: quicksort Memory: 85kB
3. 0.191 274,874.968 ↑ 22,830.9 72 1

Nested Loop Left Join (cost=64,962,954.86..1,477,703,030.01 rows=1,643,824 width=464) (actual time=253,615.376..274,874.968 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.245 274,874.705 ↑ 22,830.9 72 1

Nested Loop Left Join (cost=64,962,954.86..1,477,403,249.33 rows=1,643,824 width=452) (actual time=253,615.335..274,874.705 rows=72 loops=1)

5. 0.153 274,873.740 ↑ 22,830.9 72 1

Nested Loop Left Join (cost=64,962,954.45..1,476,572,244.47 rows=1,643,824 width=434) (actual time=253,615.299..274,873.740 rows=72 loops=1)

6. 0.161 274,872.867 ↑ 22,830.9 72 1

Nested Loop Left Join (cost=64,962,954.31..1,474,550,328.95 rows=1,643,824 width=425) (actual time=253,615.241..274,872.867 rows=72 loops=1)

7. 2.562 274,871.482 ↑ 22,830.9 72 1

Hash Left Join (cost=64,962,953.75..1,473,305,993.41 rows=1,643,824 width=377) (actual time=253,615.153..274,871.482 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: 2091
8.          

CTE time_period

9. 0.005 1.054 ↑ 1,000,000.0 1 1

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

10. 0.110 1.049 ↑ 1,000,000.0 1 1

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

11. 0.939 0.939 ↑ 1,000.0 1 1

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

12. 0.000 0.000 ↑ 1.0 1 1

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

13.          

CTE age_group

14. 0.032 0.032 ↑ 1.0 10 1

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

15.          

CTE age_groups

16. 0.024 0.024 ↑ 1.0 10 1

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

17.          

CTE race_ethnicity

18. 0.116 0.531 ↑ 1.3 21 1

Hash Right Join (cost=19.03..36.84 rows=27 width=116) (actual time=0.366..0.531 rows=21 loops=1)

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

CTE race_ethn

20. 0.018 0.018 ↑ 1.0 21 1

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

21. 0.105 0.105 ↑ 1.0 694 1

Seq Scan on classification e (cost=0.00..14.94 rows=694 width=26) (actual time=0.005..0.105 rows=694 loops=1)

22. 0.012 0.310 ↑ 1.1 21 1

Hash (cost=18.46..18.46 rows=24 width=108) (actual time=0.310..0.310 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
23. 0.116 0.298 ↑ 1.1 21 1

Hash Right Join (cost=0.68..18.46 rows=24 width=108) (actual time=0.210..0.298 rows=21 loops=1)

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

Seq Scan on classification r_1 (cost=0.00..14.94 rows=694 width=26) (actual time=0.016..0.143 rows=694 loops=1)

25. 0.008 0.039 ↑ 1.0 21 1

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

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

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

27.          

CTE interactions

28. 1.277 185.248 ↓ 1.7 2,387 1

Nested Loop (cost=45,074.27..1,671,977.74 rows=1,422 width=64) (actual time=2.079..185.248 rows=2,387 loops=1)

29.          

Initplan (forNested Loop)

30. 0.002 0.079 ↑ 1.0 1 1

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

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

Seq Scan on app_role r_2 (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)
32. 0.070 0.070 ↑ 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.070..0.070 rows=1 loops=1)

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

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

34. 1.056 1.056 ↑ 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.052..1.056 rows=1 loops=1)

35. 0.001 0.002 ↑ 1.0 1 1

Aggregate (cost=22,500.00..22,500.01 rows=1 width=8) (actual time=0.002..0.002 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.000..0.001 rows=1 loops=1)

37. 2.096 173.283 ↓ 1.7 2,387 1

Nested Loop (cost=68.46..1,626,189.14 rows=1,422 width=40) (actual time=2.042..173.283 rows=2,387 loops=1)

38. 10.951 161.603 ↑ 3.5 2,396 1

Hash Join (cost=68.18..1,623,639.54 rows=8,403 width=40) (actual time=2.022..161.603 rows=2,396 loops=1)

  • Hash Cond: (m.id_geography = c_1.id_contained)
39. 6.356 150.197 ↑ 13.9 65,775 1

Append (cost=0.00..1,612,022.94 rows=914,741 width=32) (actual time=1.557..150.197 rows=65,775 loops=1)

40. 0.008 0.008 ↓ 0.0 0 1

Seq Scan on mv_all_interaction m (cost=0.00..0.00 rows=1 width=32) (actual time=0.008..0.008 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.003 1.138 ↓ 0.0 0 1

Bitmap Heap Scan on mv_all_interaction_old_data m_1 (cost=17,675.60..1,354,787.35 rows=520,081 width=32) (actual time=1.138..1.138 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.135 1.135 ↓ 0.0 0 1

Bitmap Index Scan on idx_mv_all_interaction_old_reg (cost=0.00..17,545.58 rows=520,081 width=0) (actual time=1.135..1.135 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.055 0.055 ↓ 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..28,724.89 rows=36,212 width=32) (actual time=0.055..0.055 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.046 0.046 ↓ 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..29,659.42 rows=37,112 width=32) (actual time=0.046..0.046 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.049 0.049 ↓ 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,377.47 rows=38,289 width=32) (actual time=0.049..0.049 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.030 0.030 ↓ 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,458.50 rows=38,445 width=32) (actual time=0.030..0.030 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.037 0.037 ↓ 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..34,322.18 rows=39,055 width=32) (actual time=0.037..0.037 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.032 0.032 ↓ 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..30,462.54 rows=45,057 width=32) (actual time=0.032..0.032 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.036 0.036 ↓ 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..22,300.56 rows=43,911 width=32) (actual time=0.036..0.036 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.036 0.036 ↓ 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..15,951.20 rows=42,068 width=32) (actual time=0.036..0.036 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.033 0.033 ↓ 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..15,903.23 rows=43,114 width=32) (actual time=0.033..0.033 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. 142.248 142.248 ↓ 2.1 65,775 1

Index Scan using idx_mv_all_interaction_2018_q4_reg on mv_all_interaction_2018_q4 m_11 (cost=0.43..12,042.99 rows=31,392 width=32) (actual time=0.056..142.248 rows=65,775 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.021 0.021 ↓ 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.021..0.021 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.031 0.031 ↓ 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.031..0.031 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.025 0.025 ↓ 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.025..0.025 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.016 0.016 ↓ 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.016..0.016 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.125 0.455 ↓ 1.6 670 1

Hash (cost=62.88..62.88 rows=424 width=8) (actual time=0.455..0.455 rows=670 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 35kB
58. 0.330 0.330 ↓ 1.6 670 1

Index Only Scan using containment_pkey on containment c_1 (cost=39.56..62.88 rows=424 width=8) (actual time=0.154..0.330 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. 9.584 9.584 ↑ 1.0 1 2,396

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

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

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

  • Index Cond: (id = m.id_geography)
73. 20,938.429 274,820.304 ↑ 20,519.3 2,163 1

Hash Left Join (cost=63,005,667.63..78,371,199.91 rows=44,383,239 width=217) (actual time=253,427.156..274,820.304 rows=2,163 loops=1)

  • Hash Cond: (interactions.id_interaction = icr.id_interaction)
74. 2.493 480.305 ↑ 116.1 2,163 1

Nested Loop Left Join (cost=314.98..571,620.02 rows=251,223 width=209) (actual time=6.577..480.305 rows=2,163 loops=1)

75. 2.007 410.759 ↓ 1.5 2,163 1

Nested Loop Left Join (cost=313.67..34,354.62 rows=1,422 width=201) (actual time=5.931..410.759 rows=2,163 loops=1)

76. 4.625 402.263 ↓ 1.5 2,163 1

Nested Loop Left Join (cost=313.25..29,091.60 rows=1,422 width=201) (actual time=5.892..402.263 rows=2,163 loops=1)

  • Join Filter: ((i.age_at_interaction >= ag.low) AND (i.age_at_interaction <= ag.high))
  • Rows Removed by Join Filter: 21489
  • Filter: (COALESCE(ag.age_group, 'Missing'::text) <> 'Exclude'::text)
  • Rows Removed by Filter: 224
77. 2.567 392.864 ↓ 1.7 2,387 1

Hash Join (cost=313.25..28,700.45 rows=1,422 width=169) (actual time=5.878..392.864 rows=2,387 loops=1)

  • Hash Cond: (interactions.id_facility = f.id)
78. 3.767 386.670 ↓ 1.7 2,387 1

Nested Loop (cost=2.29..28,371.53 rows=1,422 width=161) (actual time=2.209..386.670 rows=2,387 loops=1)

79. 3.567 308.906 ↓ 1.7 2,387 1

Nested Loop (cost=1.72..23,040.26 rows=1,422 width=159) (actual time=2.174..308.906 rows=2,387 loops=1)

80. 3.633 250.438 ↓ 1.7 2,387 1

Nested Loop (cost=1.15..17,697.26 rows=1,422 width=143) (actual time=2.145..250.438 rows=2,387 loops=1)

  • Join Filter: (interactions.id_interaction = r.id)
81. 4.022 220.548 ↓ 1.7 2,387 1

Nested Loop (cost=0.57..12,243.42 rows=1,422 width=60) (actual time=2.110..220.548 rows=2,387 loops=1)

82. 187.882 187.882 ↓ 1.7 2,387 1

CTE Scan on interactions (cost=0.00..28.44 rows=1,422 width=32) (actual time=2.081..187.882 rows=2,387 loops=1)

83. 28.644 28.644 ↑ 1.0 1 2,387

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

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

Index Scan using registration_pkey on registration r (cost=0.57..3.82 rows=1 width=83) (actual time=0.011..0.011 rows=1 loops=2,387)

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

Index Scan using patient_detail_pkey on patient_detail pd (cost=0.57..3.76 rows=1 width=32) (actual time=0.023..0.023 rows=1 loops=2,387)

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

Index Scan using patient_pkey on patient p (cost=0.57..3.75 rows=1 width=18) (actual time=0.031..0.031 rows=1 loops=2,387)

  • Index Cond: (id = pd.id_patient)
87. 1.254 3.627 ↑ 1.0 6,132 1

Hash (cost=234.32..234.32 rows=6,132 width=16) (actual time=3.627..3.627 rows=6,132 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 351kB
88. 2.373 2.373 ↑ 1.0 6,132 1

Seq Scan on facility f (cost=0.00..234.32 rows=6,132 width=16) (actual time=0.022..2.373 rows=6,132 loops=1)

89. 4.774 4.774 ↑ 1.0 10 2,387

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

90. 6.489 6.489 ↑ 1.0 1 2,163

Index Only Scan using geography_pkey on geographies county (cost=0.41..3.70 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=2,163)

  • Index Cond: (id = interactions.id_geo_county)
  • Heap Fetches: 0
91. 6.489 67.053 ↑ 177.0 1 2,163

Nested Loop (cost=1.31..376.05 rows=177 width=16) (actual time=0.026..0.031 rows=1 loops=2,163)

92. 1.613 2.163 ↑ 9.0 3 2,163

HashAggregate (cost=0.61..0.88 rows=27 width=8) (actual time=0.001..0.001 rows=3 loops=2,163)

  • Group Key: race_ethnicity_1.id_ethnicity
93. 0.550 0.550 ↑ 1.3 21 1

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

94. 58.401 58.401 ↓ 0.0 0 6,489

Index Only Scan using uk_interaction_classification on interaction_classification ice (cost=0.70..13.83 rows=7 width=16) (actual time=0.008..0.009 rows=0 loops=6,489)

  • Index Cond: ((id_interaction = interactions.id_interaction) AND (id_classification = race_ethnicity_1.id_ethnicity))
  • Heap Fetches: 2967
95. 10,455.744 253,401.570 ↑ 6.6 60,021,642 1

Hash (cost=56,138,024.74..56,138,024.74 rows=395,064,232 width=16) (actual time=253,401.570..253,401.570 rows=60,021,642 loops=1)

  • Buckets: 4194304 Batches: 128 Memory Usage: 54823kB
96. 84,707.713 242,945.826 ↑ 6.6 60,021,642 1

Hash Semi Join (cost=0.88..56,138,024.74 rows=395,064,232 width=16) (actual time=0.744..242,945.826 rows=60,021,642 loops=1)

  • Hash Cond: (icr.id_classification = race_ethnicity.id_race)
97. 158,238.100 158,238.100 ↑ 2.4 1,216,514,805 1

Seq Scan on interaction_classification icr (cost=0.00..44,214,765.86 rows=2,867,873,686 width=16) (actual time=0.035..158,238.100 rows=1,216,514,805 loops=1)

98. 0.007 0.013 ↑ 1.3 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
99. 0.006 0.006 ↑ 1.3 21 1

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

100. 0.008 0.016 ↑ 1.3 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
101. 0.008 0.008 ↑ 1.3 21 1

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

102.          

SubPlan (forHash Left Join)

103. 0.360 10.728 ↑ 1.0 1 72

Aggregate (cost=240.20..240.21 rows=1 width=32) (actual time=0.149..0.149 rows=1 loops=72)

104. 0.000 10.368 ↑ 23.0 1 72

Unique (cost=239.79..239.91 rows=23 width=18) (actual time=0.144..0.144 rows=1 loops=72)

105.          

Initplan (forUnique)

106. 0.008 4.358 ↑ 1.0 1 1

Result (cost=52.71..52.72 rows=1 width=1) (actual time=4.358..4.358 rows=1 loops=1)

107.          

Initplan (forResult)

108. 0.042 4.312 ↓ 0.0 0 1

Nested Loop (cost=17.57..47.33 rows=1 width=0) (actual time=4.312..4.312 rows=0 loops=1)

109.          

Initplan (forNested Loop)

110. 0.055 0.055 ↑ 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.054..0.055 rows=1 loops=1)

  • Index Cond: (id = 0)
111. 0.125 2.295 ↓ 320.0 640 1

Nested Loop (cost=9.00..38.39 rows=2 width=8) (actual time=0.219..2.295 rows=640 loops=1)

112. 0.042 0.174 ↓ 2.0 2 1

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

113. 0.029 0.029 ↑ 1.0 1 1

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

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

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

115. 0.015 0.048 ↑ 1.0 2 1

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

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

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

  • Index Cond: ((name)::text = ANY ('{Race,Ethnicity}'::text[]))
117. 0.048 0.048 ↑ 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.024..0.024 rows=1 loops=2)

  • Index Cond: ((id_acl_class = ac.id) AND (objectid = c_2.id))
118. 1.996 1.996 ↓ 8.2 320 2

Index Scan using idx_acl_entry_1 on acl_entry ae (cost=0.42..5.15 rows=39 width=16) (actual time=0.039..0.998 rows=320 loops=2)

  • Index Cond: (id_acl_secured_object = aso.id)
  • Filter: (permission > 0)
  • Rows Removed by Filter: 47
119. 1.920 1.920 ↓ 0.0 0 640

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

  • 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
120. 0.004 0.038 ↑ 1.0 1 1

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

  • Join Filter: (u_3.id_app_role = r_4.id)
121. 0.008 0.008 ↑ 1.0 1 1

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

  • Filter: ((permission)::text = 'ROLE_ADMIN'::text)
122. 0.026 0.026 ↑ 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.026..0.026 rows=1 loops=1)

  • Index Cond: (id_app_user = 0)
  • Heap Fetches: 0
123. 1.008 10.152 ↑ 23.0 1 72

Sort (cost=187.08..187.13 rows=23 width=18) (actual time=0.141..0.141 rows=1 loops=72)

  • Sort Key: c_3.category
  • Sort Method: quicksort Memory: 25kB
124. 4.680 9.144 ↑ 23.0 1 72

Result (cost=5.23..186.56 rows=23 width=18) (actual time=0.123..0.127 rows=1 loops=72)

  • One-Time Filter: $22
125. 0.288 4.464 ↑ 23.0 1 72

Nested Loop (cost=5.23..186.56 rows=23 width=18) (actual time=0.059..0.062 rows=1 loops=72)

126. 0.432 2.448 ↑ 2.0 6 72

Nested Loop (cost=4.52..20.86 rows=12 width=26) (actual time=0.031..0.034 rows=6 loops=72)

127. 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)
128. 0.432 0.936 ↑ 2.3 6 72

Bitmap Heap Scan on classification c_3 (cost=4.38..12.56 rows=14 width=34) (actual time=0.012..0.013 rows=6 loops=72)

  • Recheck Cond: (id_classifier = cr.id)
  • Heap Blocks: exact=72
129. 0.504 0.504 ↑ 2.3 6 72

Bitmap Index Scan on classification_id_classifier_key (cost=0.00..4.38 rows=14 width=0) (actual time=0.007..0.007 rows=6 loops=72)

  • Index Cond: (id_classifier = cr.id)
130. 1.728 1.728 ↓ 0.0 0 432

Index Only Scan using uk_interaction_classification on interaction_classification ic (cost=0.70..13.74 rows=7 width=8) (actual time=0.004..0.004 rows=0 loops=432)

  • Index Cond: ((id_interaction = i.id) AND (id_classification = c_3.id))
  • Heap Fetches: 72
131. 0.144 4.824 ↑ 1.0 1 72

Aggregate (cost=240.20..240.21 rows=1 width=32) (actual time=0.067..0.067 rows=1 loops=72)

132. 0.000 4.680 ↑ 23.0 1 72

Unique (cost=239.79..239.91 rows=23 width=18) (actual time=0.065..0.065 rows=1 loops=72)

133.          

Initplan (forUnique)

134. 0.007 2.472 ↑ 1.0 1 1

Result (cost=52.71..52.72 rows=1 width=1) (actual time=2.472..2.472 rows=1 loops=1)

135.          

Initplan (forResult)

136. 0.491 2.443 ↓ 0.0 0 1

Nested Loop (cost=17.57..47.33 rows=1 width=0) (actual time=2.443..2.443 rows=0 loops=1)

137.          

Initplan (forNested Loop)

138. 0.013 0.013 ↑ 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.012..0.013 rows=1 loops=1)

  • Index Cond: (id = 0)
139. 0.121 0.659 ↓ 320.0 640 1

Nested Loop (cost=9.00..38.39 rows=2 width=8) (actual time=0.080..0.659 rows=640 loops=1)

140. 0.018 0.072 ↓ 2.0 2 1

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

141. 0.008 0.008 ↑ 1.0 1 1

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

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

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

143. 0.005 0.023 ↑ 1.0 2 1

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

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

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

  • Index Cond: ((name)::text = ANY ('{Race,Ethnicity}'::text[]))
145. 0.016 0.016 ↑ 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.008..0.008 rows=1 loops=2)

  • Index Cond: ((id_acl_class = ac_1.id) AND (objectid = c_4.id))
146. 0.466 0.466 ↓ 8.2 320 2

Index Scan using idx_acl_entry_1 on acl_entry ae_1 (cost=0.42..5.15 rows=39 width=16) (actual time=0.014..0.233 rows=320 loops=2)

  • Index Cond: (id_acl_secured_object = aso_1.id)
  • Filter: (permission > 0)
  • Rows Removed by Filter: 47
147. 1.280 1.280 ↓ 0.0 0 640

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

  • 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
148. 0.005 0.022 ↑ 1.0 1 1

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

  • Join Filter: (u_4.id_app_role = r_5.id)
149. 0.004 0.004 ↑ 1.0 1 1

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

  • Filter: ((permission)::text = 'ROLE_ADMIN'::text)
150. 0.013 0.013 ↑ 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.013..0.013 rows=1 loops=1)

  • Index Cond: (id_app_user = 0)
  • Heap Fetches: 0
151. 0.216 4.536 ↑ 23.0 1 72

Sort (cost=187.08..187.13 rows=23 width=18) (actual time=0.063..0.063 rows=1 loops=72)

  • Sort Key: c_5.category
  • Sort Method: quicksort Memory: 25kB
152. 2.592 4.320 ↑ 23.0 1 72

Result (cost=5.23..186.56 rows=23 width=18) (actual time=0.055..0.060 rows=1 loops=72)

  • One-Time Filter: $33
153. 0.144 1.728 ↑ 23.0 1 72

Nested Loop (cost=5.23..186.56 rows=23 width=18) (actual time=0.019..0.024 rows=1 loops=72)

154. 0.216 1.152 ↑ 6.0 2 72

Nested Loop (cost=4.52..20.86 rows=12 width=26) (actual time=0.014..0.016 rows=2 loops=72)

155. 0.288 0.288 ↑ 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.004..0.004 rows=1 loops=72)

  • Index Cond: ((name)::text = 'Ethnicity'::text)
156. 0.360 0.648 ↑ 7.0 2 72

Bitmap Heap Scan on classification c_5 (cost=4.38..12.56 rows=14 width=34) (actual time=0.008..0.009 rows=2 loops=72)

  • Recheck Cond: (id_classifier = cr_1.id)
  • Heap Blocks: exact=144
157. 0.288 0.288 ↑ 7.0 2 72

Bitmap Index Scan on classification_id_classifier_key (cost=0.00..4.38 rows=14 width=0) (actual time=0.004..0.004 rows=2 loops=72)

  • Index Cond: (id_classifier = cr_1.id)
158. 0.432 0.432 ↓ 0.0 0 144

Index Only Scan using uk_interaction_classification on interaction_classification ic_1 (cost=0.70..13.74 rows=7 width=8) (actual time=0.003..0.003 rows=0 loops=144)

  • Index Cond: ((id_interaction = i.id) AND (id_classification = c_5.id))
  • Heap Fetches: 72
159. 0.288 2.520 ↑ 1.0 1 72

Aggregate (cost=39.28..39.29 rows=1 width=32) (actual time=0.035..0.035 rows=1 loops=72)

160. 0.216 2.232 ↓ 0.0 0 72

Nested Loop (cost=5.17..39.25 rows=13 width=28) (actual time=0.031..0.031 rows=0 loops=72)

161. 0.144 1.008 ↑ 1.0 7 72

Nested Loop (cost=4.47..20.79 rows=7 width=36) (actual time=0.012..0.014 rows=7 loops=72)

162. 0.432 0.432 ↑ 1.0 1 72

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

  • Index Cond: (id = 76)
163. 0.288 0.432 ↑ 1.0 7 72

Bitmap Heap Scan on classification c_6 (cost=4.33..12.56 rows=7 width=16) (actual time=0.005..0.006 rows=7 loops=72)

  • Recheck Cond: (id_classifier = 76)
  • Heap Blocks: exact=72
164. 0.144 0.144 ↑ 1.0 7 72

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

  • Index Cond: (id_classifier = 76)
165. 1.008 1.008 ↓ 0.0 0 504

Index Only Scan using uk_interaction_classification on interaction_classification ic_2 (cost=0.70..14.21 rows=7 width=8) (actual time=0.002..0.002 rows=0 loops=504)

  • Index Cond: ((id_interaction = r.id) AND (id_classification = c_6.id))
  • Heap Fetches: 1
166. 0.216 1.656 ↑ 1.0 1 72

Aggregate (cost=31.06..31.07 rows=1 width=32) (actual time=0.023..0.023 rows=1 loops=72)

167. 0.144 1.440 ↓ 0.0 0 72

Nested Loop (cost=5.03..31.02 rows=13 width=18) (actual time=0.020..0.020 rows=0 loops=72)

168. 0.144 0.288 ↑ 1.0 7 72

Bitmap Heap Scan on classification c_7 (cost=4.33..12.56 rows=7 width=26) (actual time=0.003..0.004 rows=7 loops=72)

  • Recheck Cond: (id_classifier = 76)
  • Heap Blocks: exact=72
169. 0.144 0.144 ↑ 1.0 7 72

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

  • Index Cond: (id_classifier = 76)
170. 1.008 1.008 ↓ 0.0 0 504

Index Only Scan using uk_interaction_classification on interaction_classification ic_3 (cost=0.70..14.21 rows=7 width=8) (actual time=0.002..0.002 rows=0 loops=504)

  • Index Cond: ((id_interaction = r.id) AND (id_classification = c_7.id))
  • Heap Fetches: 1
171. 1.368 12.888 ↑ 1.0 1 72

Aggregate (cost=158.48..158.49 rows=1 width=32) (actual time=0.179..0.179 rows=1 loops=72)

172. 11.520 11.520 ↑ 84.0 1 72

Index Scan using idx_observation_4 on observation obs (cost=0.57..158.27 rows=84 width=80) (actual time=0.071..0.160 rows=1 loops=72)

  • Index Cond: (id_interaction = r.id)
  • Filter: (id_standardized_code = 8)
  • Rows Removed by Filter: 15
173. 0.360 15.984 ↑ 1.0 1 72

Aggregate (cost=137.92..137.93 rows=1 width=32) (actual time=0.222..0.222 rows=1 loops=72)

174. 0.504 15.624 ↓ 0.0 0 72

Nested Loop (cost=19.00..136.89 rows=69 width=46) (actual time=0.215..0.217 rows=0 loops=72)

175. 4.241 11.664 ↑ 1.5 24 72

Hash Join (cost=18.30..41.89 rows=37 width=54) (actual time=0.103..0.162 rows=24 loops=72)

  • Hash Cond: (c_8.id_classifier = cl_1.id)
176. 7.344 7.344 ↑ 1.0 694 72

Seq Scan on classification c_8 (cost=0.00..14.94 rows=694 width=34) (actual time=0.006..0.102 rows=694 loops=72)

177. 0.026 0.079 ↑ 1.0 3 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
178. 0.024 0.053 ↑ 1.0 3 1

Bitmap Heap Scan on classifier cl_1 (cost=8.44..18.26 rows=3 width=36) (actual time=0.049..0.053 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
179. 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[]))
180. 3.456 3.456 ↓ 0.0 0 1,728

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

  • Index Cond: ((id_interaction = r.id) AND (id_classification = c_8.id))
  • Heap Fetches: 3
181. 0.288 1.224 ↑ 1.0 1 72

Nested Loop (cost=0.56..0.74 rows=1 width=56) (actual time=0.016..0.017 rows=1 loops=72)

182. 0.432 0.432 ↑ 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.006..0.006 rows=1 loops=72)

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

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

  • Index Cond: (id = data_source.id)
184. 0.288 0.720 ↑ 1.0 1 72

Nested Loop (cost=0.14..1.22 rows=1 width=17) (actual time=0.009..0.010 rows=1 loops=72)

  • Join Filter: (gender.id = attribute.id)
  • Rows Removed by Join Filter: 2
185. 0.288 0.288 ↑ 1.0 1 72

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

  • Index Cond: (id = interactions.id_gender)
186. 0.144 0.144 ↑ 1.0 3 72

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

187. 0.720 0.720 ↑ 1.0 1 72

Index Scan using geography_pkey on geographies (cost=0.41..0.51 rows=1 width=26) (actual time=0.010..0.010 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.047 0.072 ↑ 6.5 2 72

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

189. 0.025 0.025 ↑ 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.023..0.025 rows=3 loops=1)