Project

General

Profile

« Previous | Next » 

Revision 3234

Added by Aaron Marcuse-Kubitza over 12 years ago

schemas/vegbien.sql: locationevent: Added covering lookup indexes on the unique constraints to enable fast merge joins in column-based import. Removed no longer needed individual-column lookup indexes because the constraint-covering lookup indexes now handle lookups. This also avoids index bloat.

View differences:

schemas/vegbien.my.sql
4162 4162 

  		




  4163
  4163
  
    

  		




  4164
  4164
  
    
--


  		




  4165
  
  
    
-- Name: locationevent_authorlocationcode; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  4166
  
  
    
--


  		




  4167
  
  
    

  		




  4168
  
  
    

  		




  4169
  
  
    

  		




  4170
  
  
    

  		




  4171
  
  
    
--


  		




  4172
  
  
    
-- Name: locationevent_datasource_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  4173
  
  
    
--


  		




  4174
  
  
    

  		




  4175
  
  
    

  		




  4176
  
  
    

  		




  4177
  
  
    

  		




  4178
  
  
    
--


  		




  4179
  4165
  
    
-- Name: locationevent_location_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  4180
  4166
  
    
--


  		




  4181
  4167
  
    

  		




  ......




  4183
  4169
  
    

  		




  4184
  4170
  
    

  		




  4185
  4171
  
    
--


  		




  4186
  
  
    
-- Name: locationevent_obsstartdate; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  
  4172
  
    
-- Name: locationevent_previousobs_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  4187
  4173
  
    
--


  		




  4188
  4174
  
    

  		




  
  4175
  
    
CREATE INDEX locationevent_previousobs_id_x ON locationevent  (previous_id);


  		




  4189
  4176
  
    

  		




  4190
  4177
  
    

  		




  4191
  
  
    

  		




  4192
  4178
  
    
--


  		




  4193
  
  
    
-- Name: locationevent_parent_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  
  4179
  
    
-- Name: locationevent_project_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  4194
  4180
  
    
--


  		




  4195
  4181
  
    

  		




  
  4182
  
    
CREATE INDEX locationevent_project_id ON locationevent  (project_id);


  		




  4196
  4183
  
    

  		




  4197
  4184
  
    

  		




  4198
  
  
    

  		




  4199
  4185
  
    
--


  		




  4200
  
  
    
-- Name: locationevent_previousobs_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  
  4186
  
    
-- Name: locationevent_soiltaxon_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  4201
  4187
  
    
--


  		




  4202
  4188
  
    

  		




  4203
  
  
    
CREATE INDEX locationevent_previousobs_id_x ON locationevent  (previous_id);


  		




  
  4189
  
    
CREATE INDEX locationevent_soiltaxon_id_x ON locationevent  (soiltaxon_id);


  		




  4204
  4190
  
    

  		




  4205
  4191
  
    

  		




  4206
  4192
  
    
--


  		




  4207
  
  
    
-- Name: locationevent_project_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  
  4193
  
    
-- Name: locationevent_unique_accessioncode; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  4208
  4194
  
    
--


  		




  4209
  4195
  
    

  		




  4210
  4196
  
    

  		




  4211
  4197
  
    

  		




  4212
  4198
  
    

  		




  4213
  4199
  
    
--


  		




  4214
  
  
    
-- Name: locationevent_soiltaxon_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  
  4200
  
    
-- Name: locationevent_unique_parent_authorcode_lookup; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  4215
  4201
  
    
--


  		




  4216
  4202
  
    

  		




  4217
  
  
    
CREATE INDEX locationevent_soiltaxon_id_x ON locationevent  (soiltaxon_id);


  		




  4218
  4203
  
    

  		




  4219
  4204
  
    

  		




  
  4205
  
    

  		




  4220
  4206
  
    
--


  		




  4221
  
  
    
-- Name: locationevent_sourceaccessioncode; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  
  4207
  
    
-- Name: locationevent_unique_parent_location_lookup; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  4222
  4208
  
    
--


  		




  4223
  4209
  
    

  		




  4224
  4210
  
    

  		




  4225
  4211
  
    

  		




  4226
  4212
  
    

  		




  4227
  4213
  
    
--


  		




  4228
  
  
    
-- Name: locationevent_unique_accessioncode; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  
  4214
  
    
-- Name: locationevent_unique_project_authorcode; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  4229
  4215
  
    
--


  		




  4230
  4216
  
    

  		




  4231
  4217
  
    

  		




  4232
  4218
  
    

  		




  4233
  4219
  
    

  		




  4234
  4220
  
    
--


  		




  4235
  
  
    
-- Name: locationevent_unique_project_authorcode; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  
  4221
  
    
-- Name: locationevent_unique_project_authorcode_lookup; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  4236
  4222
  
    
--


  		




  4237
  4223
  
    

  		




  4238
  4224
  
    

  		












  

    
      schemas/vegbien.sql
    
  





  4694
  4694
  
    

  		




  4695
  4695
  
    

  		




  4696
  4696
  
    
--


  		




  4697
  
  
    
-- Name: locationevent_authorlocationcode; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  4698
  
  
    
--


  		




  4699
  
  
    

  		




  4700
  
  
    
CREATE INDEX locationevent_authorlocationcode ON locationevent USING btree ((COALESCE(authorlocationcode, '\\N'::text)));


  		




  4701
  
  
    

  		




  4702
  
  
    

  		




  4703
  
  
    
--


  		




  4704
  
  
    
-- Name: locationevent_datasource_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  4705
  
  
    
--


  		




  4706
  
  
    

  		




  4707
  
  
    
CREATE INDEX locationevent_datasource_id ON locationevent USING btree ((COALESCE(datasource_id, 2147483647)));


  		




  4708
  
  
    

  		




  4709
  
  
    

  		




  4710
  
  
    
--


  		




  4711
  4697
  
    
-- Name: locationevent_location_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  4712
  4698
  
    
--


  		




  4713
  4699
  
    

  		




  ......




  4715
  4701
  
    

  		




  4716
  4702
  
    

  		




  4717
  4703
  
    
--


  		




  4718
  
  
    
-- Name: locationevent_obsstartdate; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  
  4704
  
    
-- Name: locationevent_previousobs_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  4719
  4705
  
    
--


  		




  4720
  4706
  
    

  		




  4721
  
  
    
CREATE INDEX locationevent_obsstartdate ON locationevent USING btree ((COALESCE(obsstartdate, 'infinity'::timestamp with time zone)));


  		




  
  4707
  
    
CREATE INDEX locationevent_previousobs_id_x ON locationevent USING btree (previous_id);


  		




  4722
  4708
  
    

  		




  4723
  4709
  
    

  		




  4724
  4710
  
    
--


  		




  4725
  
  
    
-- Name: locationevent_parent_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  
  4711
  
    
-- Name: locationevent_project_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  4726
  4712
  
    
--


  		




  4727
  4713
  
    

  		




  4728
  
  
    
CREATE INDEX locationevent_parent_id ON locationevent USING btree ((COALESCE(parent_id, 2147483647)));


  		




  
  4714
  
    
CREATE INDEX locationevent_project_id ON locationevent USING btree (project_id);


  		




  4729
  4715
  
    

  		




  4730
  4716
  
    

  		




  4731
  4717
  
    
--


  		




  4732
  
  
    
-- Name: locationevent_previousobs_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  
  4718
  
    
-- Name: locationevent_soiltaxon_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  4733
  4719
  
    
--


  		




  4734
  4720
  
    

  		




  4735
  
  
    
CREATE INDEX locationevent_previousobs_id_x ON locationevent USING btree (previous_id);


  		




  
  4721
  
    
CREATE INDEX locationevent_soiltaxon_id_x ON locationevent USING btree (soiltaxon_id);


  		




  4736
  4722
  
    

  		




  4737
  4723
  
    

  		




  4738
  4724
  
    
--


  		




  4739
  
  
    
-- Name: locationevent_project_id; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  
  4725
  
    
-- Name: locationevent_unique_accessioncode; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  4740
  4726
  
    
--


  		




  4741
  4727
  
    

  		




  4742
  
  
    
CREATE INDEX locationevent_project_id ON locationevent USING btree ((COALESCE(project_id, 2147483647)));


  		




  
  4728
  
    
CREATE UNIQUE INDEX locationevent_unique_accessioncode ON locationevent USING btree (location_id, (COALESCE(datasource_id, 2147483647)), (COALESCE(sourceaccessioncode, '\\N'::text)), (COALESCE(obsstartdate, 'infinity'::timestamp with time zone))) WHERE (parent_id IS NULL);


  		




  4743
  4729
  
    

  		




  4744
  4730
  
    

  		




  4745
  4731
  
    
--


  		




  4746
  
  
    
-- Name: locationevent_soiltaxon_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  
  4732
  
    
-- Name: locationevent_unique_parent_authorcode_lookup; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  4747
  4733
  
    
--


  		




  4748
  4734
  
    

  		




  4749
  
  
    
CREATE INDEX locationevent_soiltaxon_id_x ON locationevent USING btree (soiltaxon_id);


  		




  
  4735
  
    
CREATE INDEX locationevent_unique_parent_authorcode_lookup ON locationevent USING btree ((COALESCE(parent_id, 2147483647)), (COALESCE(authorlocationcode, '\\N'::text)));


  		




  4750
  4736
  
    

  		




  4751
  4737
  
    

  		




  4752
  4738
  
    
--


  		




  4753
  
  
    
-- Name: locationevent_sourceaccessioncode; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  
  4739
  
    
-- Name: locationevent_unique_parent_location_lookup; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  4754
  4740
  
    
--


  		




  4755
  4741
  
    

  		




  4756
  
  
    
CREATE INDEX locationevent_sourceaccessioncode ON locationevent USING btree ((COALESCE(sourceaccessioncode, '\\N'::text)));


  		




  
  4742
  
    
CREATE INDEX locationevent_unique_parent_location_lookup ON locationevent USING btree ((COALESCE(parent_id, 2147483647)), (COALESCE(location_id, 2147483647)));


  		




  4757
  4743
  
    

  		




  4758
  4744
  
    

  		




  4759
  4745
  
    
--


  		




  4760
  
  
    
-- Name: locationevent_unique_accessioncode; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  
  4746
  
    
-- Name: locationevent_unique_project_authorcode; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  4761
  4747
  
    
--


  		




  4762
  4748
  
    

  		




  4763
  
  
    
CREATE UNIQUE INDEX locationevent_unique_accessioncode ON locationevent USING btree (location_id, (COALESCE(datasource_id, 2147483647)), (COALESCE(sourceaccessioncode, '\\N'::text)), (COALESCE(obsstartdate, 'infinity'::timestamp with time zone))) WHERE (parent_id IS NULL);


  		




  
  4749
  
    
CREATE UNIQUE INDEX locationevent_unique_project_authorcode ON locationevent USING btree (project_id, authorlocationcode, obsstartdate) WHERE (parent_id IS NULL);


  		




  4764
  4750
  
    

  		




  4765
  4751
  
    

  		




  4766
  4752
  
    
--


  		




  4767
  
  
    
-- Name: locationevent_unique_project_authorcode; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  
  4753
  
    
-- Name: locationevent_unique_project_authorcode_lookup; Type: INDEX; Schema: public; Owner: -; Tablespace: 


  		




  4768
  4754
  
    
--


  		




  4769
  4755
  
    

  		




  4770
  
  
    
CREATE UNIQUE INDEX locationevent_unique_project_authorcode ON locationevent USING btree (project_id, authorlocationcode, obsstartdate) WHERE (parent_id IS NULL);


  		




  
  4756
  
    
CREATE INDEX locationevent_unique_project_authorcode_lookup ON locationevent USING btree ((COALESCE(project_id, 2147483647)), (COALESCE(authorlocationcode, '\\N'::text)), (COALESCE(obsstartdate, 'infinity'::timestamp with time zone)));


  		




  4771
  4757
  
    

  		




  4772
  4758
  
    

  		




  4773
  4759
  
    
--


  		












Also available in:  Unified diff