-
Notifications
You must be signed in to change notification settings - Fork 4
/
Copy path050_omop_cdm_postgresql_indexes.sql
191 lines (131 loc) · 8.14 KB
/
050_omop_cdm_postgresql_indexes.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
/*postgresql OMOP CDM Indices
There are no unique indices created because it is assumed that the primary key constraints have been run prior to
implementing indices.
*/
set search_path = demo_cdm;
/************************
Standardized clinical data
************************/
CREATE INDEX idx_person_id ON person (person_id ASC);
CLUSTER person USING idx_person_id ;
CREATE INDEX idx_gender ON person (gender_concept_id ASC);
CREATE INDEX idx_observation_period_id_1 ON observation_period (person_id ASC);
CLUSTER observation_period USING idx_observation_period_id_1 ;
CREATE INDEX idx_visit_person_id_1 ON visit_occurrence (person_id ASC);
CLUSTER visit_occurrence USING idx_visit_person_id_1 ;
CREATE INDEX idx_visit_concept_id_1 ON visit_occurrence (visit_concept_id ASC);
CREATE INDEX idx_visit_det_person_id_1 ON visit_detail (person_id ASC);
CLUSTER visit_detail USING idx_visit_det_person_id_1 ;
CREATE INDEX idx_visit_det_concept_id_1 ON visit_detail (visit_detail_concept_id ASC);
CREATE INDEX idx_visit_det_occ_id ON visit_detail (visit_occurrence_id ASC);
CREATE INDEX idx_condition_person_id_1 ON condition_occurrence (person_id ASC);
CLUSTER condition_occurrence USING idx_condition_person_id_1 ;
CREATE INDEX idx_condition_concept_id_1 ON condition_occurrence (condition_concept_id ASC);
CREATE INDEX idx_condition_visit_id_1 ON condition_occurrence (visit_occurrence_id ASC);
CREATE INDEX idx_drug_person_id_1 ON drug_exposure (person_id ASC);
CLUSTER drug_exposure USING idx_drug_person_id_1 ;
CREATE INDEX idx_drug_concept_id_1 ON drug_exposure (drug_concept_id ASC);
CREATE INDEX idx_drug_visit_id_1 ON drug_exposure (visit_occurrence_id ASC);
CREATE INDEX idx_procedure_person_id_1 ON procedure_occurrence (person_id ASC);
CLUSTER procedure_occurrence USING idx_procedure_person_id_1 ;
CREATE INDEX idx_procedure_concept_id_1 ON procedure_occurrence (procedure_concept_id ASC);
CREATE INDEX idx_procedure_visit_id_1 ON procedure_occurrence (visit_occurrence_id ASC);
CREATE INDEX idx_device_person_id_1 ON device_exposure (person_id ASC);
CLUSTER device_exposure USING idx_device_person_id_1 ;
CREATE INDEX idx_device_concept_id_1 ON device_exposure (device_concept_id ASC);
CREATE INDEX idx_device_visit_id_1 ON device_exposure (visit_occurrence_id ASC);
CREATE INDEX idx_measurement_person_id_1 ON measurement (person_id ASC);
CLUSTER measurement USING idx_measurement_person_id_1 ;
CREATE INDEX idx_measurement_concept_id_1 ON measurement (measurement_concept_id ASC);
CREATE INDEX idx_measurement_visit_id_1 ON measurement (visit_occurrence_id ASC);
CREATE INDEX idx_observation_person_id_1 ON observation (person_id ASC);
CLUSTER observation USING idx_observation_person_id_1 ;
CREATE INDEX idx_observation_concept_id_1 ON observation (observation_concept_id ASC);
CREATE INDEX idx_observation_visit_id_1 ON observation (visit_occurrence_id ASC);
CREATE INDEX idx_death_person_id_1 ON death (person_id ASC);
CLUSTER death USING idx_death_person_id_1 ;
CREATE INDEX idx_note_person_id_1 ON note (person_id ASC);
CLUSTER note USING idx_note_person_id_1 ;
CREATE INDEX idx_note_concept_id_1 ON note (note_type_concept_id ASC);
CREATE INDEX idx_note_visit_id_1 ON note (visit_occurrence_id ASC);
CREATE INDEX idx_note_nlp_note_id_1 ON note_nlp (note_id ASC);
CLUSTER note_nlp USING idx_note_nlp_note_id_1 ;
CREATE INDEX idx_note_nlp_concept_id_1 ON note_nlp (note_nlp_concept_id ASC);
CREATE INDEX idx_specimen_person_id_1 ON specimen (person_id ASC);
CLUSTER specimen USING idx_specimen_person_id_1 ;
CREATE INDEX idx_specimen_concept_id_1 ON specimen (specimen_concept_id ASC);
CREATE INDEX idx_fact_relationship_id1 ON fact_relationship (domain_concept_id_1 ASC);
CREATE INDEX idx_fact_relationship_id2 ON fact_relationship (domain_concept_id_2 ASC);
CREATE INDEX idx_fact_relationship_id3 ON fact_relationship (relationship_concept_id ASC);
/************************
Standardized health system data
************************/
CREATE INDEX idx_location_id_1 ON location (location_id ASC);
CLUSTER location USING idx_location_id_1 ;
CREATE INDEX idx_care_site_id_1 ON care_site (care_site_id ASC);
CLUSTER care_site USING idx_care_site_id_1 ;
CREATE INDEX idx_provider_id_1 ON provider (provider_id ASC);
CLUSTER provider USING idx_provider_id_1 ;
/************************
Standardized health economics
************************/
CREATE INDEX idx_period_person_id_1 ON payer_plan_period (person_id ASC);
CLUSTER payer_plan_period USING idx_period_person_id_1 ;
CREATE INDEX idx_cost_event_id ON cost (cost_event_id ASC);
/************************
Standardized derived elements
************************/
CREATE INDEX idx_drug_era_person_id_1 ON drug_era (person_id ASC);
CLUSTER drug_era USING idx_drug_era_person_id_1 ;
CREATE INDEX idx_drug_era_concept_id_1 ON drug_era (drug_concept_id ASC);
CREATE INDEX idx_dose_era_person_id_1 ON dose_era (person_id ASC);
CLUSTER dose_era USING idx_dose_era_person_id_1 ;
CREATE INDEX idx_dose_era_concept_id_1 ON dose_era (drug_concept_id ASC);
CREATE INDEX idx_condition_era_person_id_1 ON condition_era (person_id ASC);
CLUSTER condition_era USING idx_condition_era_person_id_1 ;
CREATE INDEX idx_condition_era_concept_id_1 ON condition_era (condition_concept_id ASC);
/**************************
Standardized meta-data
***************************/
CREATE INDEX idx_metadata_concept_id_1 ON metadata (metadata_concept_id ASC);
CLUSTER metadata USING idx_metadata_concept_id_1 ;
/**************************
Standardized vocabularies
***************************/
CREATE INDEX idx_concept_concept_id ON concept (concept_id ASC);
CLUSTER concept USING idx_concept_concept_id ;
CREATE INDEX idx_concept_code ON concept (concept_code ASC);
CREATE INDEX idx_concept_vocabluary_id ON concept (vocabulary_id ASC);
CREATE INDEX idx_concept_domain_id ON concept (domain_id ASC);
CREATE INDEX idx_concept_class_id ON concept (concept_class_id ASC);
CREATE INDEX idx_vocabulary_vocabulary_id ON vocabulary (vocabulary_id ASC);
CLUSTER vocabulary USING idx_vocabulary_vocabulary_id ;
CREATE INDEX idx_domain_domain_id ON domain (domain_id ASC);
CLUSTER domain USING idx_domain_domain_id ;
CREATE INDEX idx_concept_class_class_id ON concept_class (concept_class_id ASC);
CLUSTER concept_class USING idx_concept_class_class_id ;
CREATE INDEX idx_concept_relationship_id_1 ON concept_relationship (concept_id_1 ASC);
CLUSTER concept_relationship USING idx_concept_relationship_id_1 ;
CREATE INDEX idx_concept_relationship_id_2 ON concept_relationship (concept_id_2 ASC);
CREATE INDEX idx_concept_relationship_id_3 ON concept_relationship (relationship_id ASC);
CREATE INDEX idx_relationship_rel_id ON relationship (relationship_id ASC);
CLUSTER relationship USING idx_relationship_rel_id ;
CREATE INDEX idx_concept_synonym_id ON concept_synonym (concept_id ASC);
CLUSTER concept_synonym USING idx_concept_synonym_id ;
CREATE INDEX idx_concept_ancestor_id_1 ON concept_ancestor (ancestor_concept_id ASC);
CLUSTER concept_ancestor USING idx_concept_ancestor_id_1 ;
CREATE INDEX idx_concept_ancestor_id_2 ON concept_ancestor (descendant_concept_id ASC);
CREATE INDEX idx_source_to_concept_map_3 ON source_to_concept_map (target_concept_id ASC);
CLUSTER source_to_concept_map USING idx_source_to_concept_map_3 ;
CREATE INDEX idx_source_to_concept_map_1 ON source_to_concept_map (source_vocabulary_id ASC);
CREATE INDEX idx_source_to_concept_map_2 ON source_to_concept_map (target_vocabulary_id ASC);
CREATE INDEX idx_source_to_concept_map_c ON source_to_concept_map (source_code ASC);
CREATE INDEX idx_drug_strength_id_1 ON drug_strength (drug_concept_id ASC);
CLUSTER drug_strength USING idx_drug_strength_id_1 ;
CREATE INDEX idx_drug_strength_id_2 ON drug_strength (ingredient_concept_id ASC);
--Additional v6.0 indices
--CREATE CLUSTERED INDEX idx_survey_person_id_1 ON survey_conduct (person_id ASC);
--CREATE CLUSTERED INDEX idx_episode_person_id_1 ON episode (person_id ASC);
--CREATE INDEX idx_episode_concept_id_1 ON episode (episode_concept_id ASC);
--CREATE CLUSTERED INDEX idx_episode_event_id_1 ON episode_event (episode_id ASC);
--CREATE INDEX idx_ee_field_concept_id_1 ON episode_event (event_field_concept_id ASC);