Revision 3234
Added by Aaron Marcuse-Kubitza over 12 years ago
| 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
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.