Maybe create additional indexes for pedsnet, pcornet

[murphyke]

pedsnet

The fact_relationship.fact_id_1 and fact_relationship.fact_id_2 are not indexed, which might affect some joins to the domain tables.

Queries that need to look at a large portion of the rows will not be sped up, of course, because table scans will be (and should be) preferred.

There are a number of foreign keys in the pedsnet data model pointing at the concept table that do not have indexes, but these may not be used very often.

pcornet

There is a greater variety of foreign keys without indexes in the pcornet data model - impact unknown.

PS

FWIW, here is the output of https://github.com/pgexperts/pgx_scripts/blob/master/indexes/fk_no_index.sql on pedsnet_dcc in production (v2.0). See http://www.databasesoup.com/2014/11/finding-foreign-keys-with-no-indexes.html for motivation.

schema_name	table_name	fk_name	issue	table_mb	writes	table_scans	parent_name	parent_mb	parent_writes	cols_list	indexdef
pedsnet_cdm	measurement	fpk_measurement_concept_s	no index	75634	287745415	24	concept	297	2162881	{measurement_source_concept_id}
pedsnet_cdm	measurement	fpk_measurement_operator	no index	75634	287745415	24	concept	297	2162881	{operator_concept_id}
pedsnet_cdm	measurement	fpk_measurement_type_concept	no index	75634	287745415	24	concept	297	2162881	{measurement_type_concept_id}
pedsnet_cdm	measurement	fpk_measurement_unit	no index	75634	287745415	24	concept	297	2162881	{unit_concept_id}
pedsnet_cdm	measurement	fpk_measurement_value	no index	75634	287745415	24	concept	297	2162881	{value_as_concept_id}
pedsnet_cdm	drug_exposure	fpk_drug_concept_s	no index	32675	88267284	25	concept	297	2162881	{drug_source_concept_id}
pedsnet_cdm	drug_exposure	fpk_drug_dose_unit_concept	no index	32675	88267284	25	concept	297	2162881	{dose_unit_concept_id}
pedsnet_cdm	drug_exposure	fpk_drug_route_concept	no index	32675	88267284	25	concept	297	2162881	{route_concept_id}
pedsnet_cdm	drug_exposure	fpk_drug_type_concept	no index	32675	88267284	25	concept	297	2162881	{drug_type_concept_id}
pedsnet_cdm	observation	fpk_observation_concept_s	no index	22639	132748028	25	concept	297	2162881	{observation_source_concept_id}
pedsnet_cdm	observation	fpk_observation_qualifier	no index	22639	132748028	25	concept	297	2162881	{qualifier_concept_id}
pedsnet_cdm	observation	fpk_observation_type_concept	no index	22639	132748028	25	concept	297	2162881	{observation_type_concept_id}
pedsnet_cdm	observation	fpk_observation_unit	no index	22639	132748028	25	concept	297	2162881	{unit_concept_id}
pedsnet_cdm	observation	fpk_observation_value	no index	22639	132748028	25	concept	297	2162881	{value_as_concept_id}
pedsnet_cdm	procedure_occurrence	fpk_procedure_concept_s	no index	18771	54276701	30	concept	297	2162881	{procedure_source_concept_id}
pedsnet_cdm	procedure_occurrence	fpk_procedure_modifier	no index	18771	54276701	30	concept	297	2162881	{modifier_concept_id}
pedsnet_cdm	procedure_occurrence	fpk_procedure_type_concept	no index	18771	54276701	30	concept	297	2162881	{procedure_type_concept_id}
pedsnet_cdm	condition_occurrence	fpk_condition_concept_s	no index	15294	83937583	37	concept	297	2162881	{condition_source_concept_id}
pedsnet_cdm	condition_occurrence	fpk_condition_type_concept	no index	15294	83937583	37	concept	297	2162881	{condition_type_concept_id}
pedsnet_cdm	visit_occurrence	fpk_visit_concept_s	no index	14940	76452217	33	concept	297	2162881	{visit_source_concept_id}
pedsnet_cdm	visit_occurrence	fpk_visit_type_concept	no index	14940	76452217	33	concept	297	2162881	{visit_type_concept_id}
pcornet_cdm	diagnosis	fk_diagnosis_encounterid	no index	10320	75437911	9	encounter	9419	76452217	{encounterid}
pcornet_cdm	diagnosis	fk_diagnosis_patid	no index	10320	75437911	9	demographic	265	3421157	{patid}
pcornet_cdm	encounter	fk_encounter_patid	no index	9419	76452217	15	demographic	265	3421157	{patid}
pcornet_cdm	vital	fk_vital_encounterid	no index	8029	62251061	15	encounter	9419	76452217	{encounterid}
pcornet_cdm	vital	fk_vital_patid	no index	8029	62251061	15	demographic	265	3421157	{patid}
pcornet_cdm	prescribing	fk_prescribing_encounterid	no index	5270	35126300	7	encounter	9419	76452217	{encounterid}
pcornet_cdm	prescribing	fk_prescribing_patid	no index	5270	35126300	7	demographic	265	3421157	{patid}
pcornet_cdm	procedures_old	fk_procedures_encounterid_old	no index	4683	49028003	4	encounter	9419	76452217	{encounterid}
pcornet_cdm	procedures_old	fk_procedures_patid_old	no index	4683	49028003	4	demographic	265	3421157	{patid}
pcornet_cdm	procedures	fk_procedures_encounterid	no index	4675	133819723	17	encounter	9419	76452217	{encounterid}
pcornet_cdm	procedures	fk_procedures_patid	no index	4675	133819723	17	demographic	265	3421157	{patid}
pcornet_cdm	dispensing	fk_dispensing_patid	no index	1365	15497385	11	demographic	265	3421157	{patid}
pcornet_cdm	dispensing	fk_dispensing_prescribingid	no index	1365	15497385	11	prescribing	5270	35126300	{prescribingid}
pedsnet_cdm	person	fpk_person_ethnicity_concept	no index	719	3422157	154	concept	297	2162881	{ethnicity_concept_id}
pedsnet_cdm	person	fpk_person_ethnicity_concept_s	no index	719	3422157	154	concept	297	2162881	{ethnicity_source_concept_id}
pedsnet_cdm	person	fpk_person_gender_concept	no index	719	3422157	154	concept	297	2162881	{gender_concept_id}
pedsnet_cdm	person	fpk_person_gender_concept_s	no index	719	3422157	154	concept	297	2162881	{gender_source_concept_id}
pedsnet_cdm	person	fpk_person_race_concept	no index	719	3422157	154	concept	297	2162881	{race_concept_id}
pedsnet_cdm	person	fpk_person_race_concept_s	no index	719	3422157	154	concept	297	2162881	{race_source_concept_id}
pcornet_cdm	condition	fk_condition_encounterid	no index	657	6985111	5	encounter	9419	76452217	{encounterid}
pcornet_cdm	condition	fk_condition_patid	no index	657	6985111	5	demographic	265	3421157	{patid}
pedsnet_cdm	observation_period	fpk_observation_period_concept	no index	89	850086	5	concept	297	2162881	{period_type_concept_id}
pedsnet_cdm	provider	fpk_provider_care_site	no index	45	357174	19	care_site	1	4116	{care_site_id}
pedsnet_cdm	provider	fpk_provider_gender	no index	45	357174	19	concept	297	2162881	{gender_concept_id}
pedsnet_cdm	provider	fpk_provider_gender_s	no index	45	357174	19	concept	297	2162881	{gender_source_concept_id}
pedsnet_cdm	provider	fpk_provider_specialty	no index	45	357174	19	concept	297	2162881	{specialty_concept_id}
pedsnet_cdm	provider	fpk_provider_specialty_s	no index	45	357174	19	concept	297	2162881	{specialty_source_concept_id}
pcornet_cdm	enrollment	fk_enrollment_patid	questionable index	42	850086	5	demographic	265	3421157	{patid}	CREATE UNIQUE INDEX xpk_enrollment ON pcornet_cdm.enrollment USING btree (patid, enr_start_date, enr_basis)