From: Tom Clegg Date: Mon, 20 Aug 2018 13:17:26 +0000 (-0400) Subject: 13924: Add index for PDH lookups. X-Git-Tag: 1.2.0~19^2~1 X-Git-Url: https://git.arvados.org/arvados.git/commitdiff_plain/662a70957f0652acd6579efc635eed8f708ab48c 13924: Add index for PDH lookups. Commit 2ee4ee2bb666835c41019b475dd2e286604bdd35 changed PDH lookup queries to sort on trash_at, which can be slow without an index. Arvados-DCO-1.1-Signed-off-by: Tom Clegg --- diff --git a/services/api/db/migrate/20180820130357_add_pdh_and_trash_index_to_collections.rb b/services/api/db/migrate/20180820130357_add_pdh_and_trash_index_to_collections.rb new file mode 100644 index 0000000000..8d1cdf39b3 --- /dev/null +++ b/services/api/db/migrate/20180820130357_add_pdh_and_trash_index_to_collections.rb @@ -0,0 +1,9 @@ +# Copyright (C) The Arvados Authors. All rights reserved. +# +# SPDX-License-Identifier: AGPL-3.0 + +class AddPdhAndTrashIndexToCollections < ActiveRecord::Migration + def change + add_index :collections, [:portable_data_hash, :trash_at] + end +end diff --git a/services/api/db/structure.sql b/services/api/db/structure.sql index 12158e51b4..d2187514a3 100644 --- a/services/api/db/structure.sql +++ b/services/api/db/structure.sql @@ -1829,6 +1829,13 @@ CREATE UNIQUE INDEX index_collections_on_owner_uuid_and_name ON public.collectio CREATE INDEX index_collections_on_portable_data_hash ON public.collections USING btree (portable_data_hash); +-- +-- Name: index_collections_on_portable_data_hash_and_trash_at; Type: INDEX; Schema: public; Owner: - +-- + +CREATE INDEX index_collections_on_portable_data_hash_and_trash_at ON public.collections USING btree (portable_data_hash, trash_at); + + -- -- Name: index_collections_on_trash_at; Type: INDEX; Schema: public; Owner: - -- @@ -3121,3 +3128,5 @@ INSERT INTO schema_migrations (version) VALUES ('20180608123145'); INSERT INTO schema_migrations (version) VALUES ('20180806133039'); +INSERT INTO schema_migrations (version) VALUES ('20180820130357'); +