From d71335c6af0e312b451fdc99ecae42362ba4723d Mon Sep 17 00:00:00 2001 From: Peter Amstutz Date: Tue, 15 Dec 2015 09:56:38 -0500 Subject: [PATCH] Add required indexes to containers and container_requests tables. refs #6429 --- .../20151215134304_fix_containers_index.rb | 17 ++++++++ services/api/db/structure.sql | 39 ++++++++++++++++++- 2 files changed, 55 insertions(+), 1 deletion(-) create mode 100644 services/api/db/migrate/20151215134304_fix_containers_index.rb diff --git a/services/api/db/migrate/20151215134304_fix_containers_index.rb b/services/api/db/migrate/20151215134304_fix_containers_index.rb new file mode 100644 index 0000000000..e6b83cf026 --- /dev/null +++ b/services/api/db/migrate/20151215134304_fix_containers_index.rb @@ -0,0 +1,17 @@ +class FixContainersIndex < ActiveRecord::Migration + def up + execute "CREATE INDEX container_requests_full_text_search_idx ON container_requests USING gin(#{ContainerRequest.full_text_tsvector});" + add_index :container_requests, ["uuid", "owner_uuid", "modified_by_client_uuid", "modified_by_user_uuid", "name", "state", "requesting_container_uuid", "container_uuid", "container_image", "cwd", "output_path"], name: 'container_requests_search_index' + add_index :containers, ["uuid", "owner_uuid", "modified_by_client_uuid", "modified_by_user_uuid", "state", "log", "cwd", "output_path", "output", "container_image"], name: 'containers_search_index' + add_index :container_requests, :owner_uuid + add_index :containers, :owner_uuid + end + + def down + remove_index :container_requests, :name => 'container_requests_full_text_search_idx' + remove_index :container_requests, :name => 'container_requests_search_index' + remove_index :containers, :name => 'containers_search_index' + remove_index :container_requests, :owner_uuid + remove_index :containers, :owner_uuid + end +end diff --git a/services/api/db/structure.sql b/services/api/db/structure.sql index 1ce44cde4c..7600313126 100644 --- a/services/api/db/structure.sql +++ b/services/api/db/structure.sql @@ -1451,6 +1451,27 @@ CREATE INDEX collections_full_text_search_idx ON collections USING gin (to_tsvec CREATE INDEX collections_search_index ON collections USING btree (owner_uuid, modified_by_client_uuid, modified_by_user_uuid, portable_data_hash, uuid, name); +-- +-- Name: container_requests_full_text_search_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX container_requests_full_text_search_idx ON container_requests USING gin (to_tsvector('english'::regconfig, (((((((((((((((((((((((((((((((((((' '::text || (COALESCE(uuid, ''::character varying))::text) || ' '::text) || (COALESCE(owner_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_client_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(modified_by_user_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(name, ''::character varying))::text) || ' '::text) || COALESCE(description, ''::text)) || ' '::text) || COALESCE(properties, ''::text)) || ' '::text) || (COALESCE(state, ''::character varying))::text) || ' '::text) || (COALESCE(requesting_container_uuid, ''::character varying))::text) || ' '::text) || (COALESCE(container_uuid, ''::character varying))::text) || ' '::text) || COALESCE(mounts, ''::text)) || ' '::text) || COALESCE(runtime_constraints, ''::text)) || ' '::text) || (COALESCE(container_image, ''::character varying))::text) || ' '::text) || COALESCE(environment, ''::text)) || ' '::text) || (COALESCE(cwd, ''::character varying))::text) || ' '::text) || COALESCE(command, ''::text)) || ' '::text) || (COALESCE(output_path, ''::character varying))::text) || ' '::text) || COALESCE(filters, ''::text)))); + + +-- +-- Name: container_requests_search_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX container_requests_search_index ON container_requests USING btree (uuid, owner_uuid, modified_by_client_uuid, modified_by_user_uuid, name, state, requesting_container_uuid, container_uuid, container_image, cwd, output_path); + + +-- +-- Name: containers_search_index; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX containers_search_index ON containers USING btree (uuid, owner_uuid, modified_by_client_uuid, modified_by_user_uuid, state, log, cwd, output_path, output, container_image); + + -- -- Name: groups_full_text_search_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -1598,6 +1619,13 @@ CREATE UNIQUE INDEX index_commit_ancestors_on_descendant_and_ancestor ON commit_ CREATE UNIQUE INDEX index_commits_on_repository_name_and_sha1 ON commits USING btree (repository_name, sha1); +-- +-- Name: index_container_requests_on_owner_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX index_container_requests_on_owner_uuid ON container_requests USING btree (owner_uuid); + + -- -- Name: index_container_requests_on_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -1605,6 +1633,13 @@ CREATE UNIQUE INDEX index_commits_on_repository_name_and_sha1 ON commits USING b CREATE UNIQUE INDEX index_container_requests_on_uuid ON container_requests USING btree (uuid); +-- +-- Name: index_containers_on_owner_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: +-- + +CREATE INDEX index_containers_on_owner_uuid ON containers USING btree (owner_uuid); + + -- -- Name: index_containers_on_uuid; Type: INDEX; Schema: public; Owner: -; Tablespace: -- @@ -2528,4 +2563,6 @@ INSERT INTO schema_migrations (version) VALUES ('20150512193020'); INSERT INTO schema_migrations (version) VALUES ('20150526180251'); -INSERT INTO schema_migrations (version) VALUES ('20151202151426'); \ No newline at end of file +INSERT INTO schema_migrations (version) VALUES ('20151202151426'); + +INSERT INTO schema_migrations (version) VALUES ('20151215134304'); \ No newline at end of file -- 2.30.2