Task #549

Updated by Aaron Marcuse-Kubitza almost 12 years ago

* include indirectly-cascading fkeys 
 * use instructions for *[[Postgres queries#Adding covering indexes on foreign keys|adding covering indexes on foreign keys]]* 

 h3. Fkeys' covering indexes 

 |*Table*|*Field*|*Covering index*| 
 |\3. *direct fkeys*| 
 |aggregateoccurrence|source_id|aggregateoccurrence_source_id_idx| 
 |commclass|classpublication_id| _unused_| 
 |commconcept|reference_id| _unused_| 
 |commdetermination|commauthority_id| _unused_| 
 |commname|source_id| _unused_| 
 |commstatus|source_id| _unused_| 
 |coordinates|source_id|coordinates_unique| 
 |covermethod|source_id| _unused_| 
 |location|source_id|location_creator| 
 |locationevent|source_id|locationevent_source_id_idx| 
 |method|source_id|method_unique| 
 |party|source_id|party_source_id_idx| 
 |place|source_id|place_unique_within_creator_by_name| 
 |plantobservation|source_id|plantobservation_source_id_idx| 
 |project|source_id|project_source_id_idx| 
 |sourcecontributor|source_id| _unused_| 
 |sourcelist|source_id|sourcelist_unique| 
 |sourcename|matched_source_id|sourcename_matched_source_id_idx| 
 |specimenreplicate|source_id|specimenreplicate_source_id_idx| 
 |stemobservation|source_id|stemobservation_source_id_idx| 
 |taxonconcept|concept_reference_id| _unused_| 
 |taxondetermination|source_id|taxondetermination_source_id_idx| 
 |taxondetermination|reference_id| _unused_| 
 |taxonlabel|source_id|taxonlabel_source_id_idx| 
 |taxonoccurrence|source_id|taxonoccurrence_source_id_idx| 
 |taxonstatus|source_id| _unused_| 
 |\3. *indirect level 1 fkeys*| 
 |plantobservation|aggregateoccurrence_id|| 
 |place|coordinates_id|| 
 |locationevent|location_id|| 
 |locationplace|location_id|| 
 |commclass|locationevent_id|| 
 |disturbanceobs|locationevent_id|| 
 |graphic|locationevent_id|| 
 |locationeventcontributor|locationevent_id|| 
 |locationeventsynonym|primarylocationevent_id|| 
 |locationeventsynonym|synonymlocationevent_id|| 
 |soilsample|locationevent_id|| 
 |stratum|locationevent_id|| 
 |taxonoccurrence|locationevent_id|| 
 |methodtaxonclass|method_id|| 
 |methodtaxonclass|submethod_id|| 
 |stratum|method_id|| 
 |address|organization_id|| 
 |address|party_id|| 
 |classcontributor|party_id|| 
 |commstatus|party_id|| 
 |commusage|party_id|| 
 |locationeventcontributor|party_id|| 
 |locationeventsynonym|party_id|| 
 |locationplace|identifier_id|| 
 |note|party_id|| 
 |partymember|childparty_id|| 
 |partymember|parentparty_id|| 
 |projectcontributor|party_id|| 
 |sourcecontributor|sourceparty_id|| 
 |taxondetermination|party_id|| 
 |taxonoccurrence|collector_id|| 
 |taxonstatus|party_id|| 
 |telephone|party_id|| 
 |locationplace|place_id|| 
 |specimenreplicate|plantobservation_id|| 
 |stemobservation|plantobservation_id|| 
 |projectcontributor|project_id|| 
 |sourcename|sourcelist_id|| 
 |specimenreplicate|institution_id|| 
 |voucher|specimenreplicate_id|| 
 |taxonalt|taxondetermination_id|| 
 |methodtaxonclass|taxonlabel_id|| 
 |taxonalt|taxonlabel_id|| 
 |taxonconcept|taxonlabel_id|| 
 |taxoncorrelation|taxonlabel_id|| 
 |taxonlabel_relationship|ancestor_id|| 
 |taxonlabel_relationship|descendant_id|| 
 |taxonstatus|taxonlabel_id|| 
 |taxonverbatim|taxonlabel_id|| 
 |threatened_taxonlabel|taxonlabel_id|| 
 |trait|taxonoccurrence_id|| 
 |voucher|taxonoccurrence_id|| 

 generated from the messages output by 
 <pre> 
 DROP TABLE source CASCADE; 

 DROP TABLE aggregateoccurrence CASCADE; 
 DROP TABLE coordinates CASCADE; 
 DROP TABLE location CASCADE; 
 DROP TABLE locationevent CASCADE; 
 DROP TABLE method CASCADE; 
 DROP TABLE party CASCADE; 
 DROP TABLE place CASCADE; 
 DROP TABLE plantobservation CASCADE; 
 DROP TABLE project CASCADE; 
 DROP TABLE sourcelist CASCADE; 
 DROP TABLE sourcename CASCADE; 
 DROP TABLE specimenreplicate CASCADE; 
 DROP TABLE stemobservation CASCADE; 
 DROP TABLE taxondetermination CASCADE; 
 DROP TABLE taxonlabel CASCADE; 
 DROP TABLE taxonoccurrence CASCADE; 
 </pre>

Back