From 5dc9ed65ff179e71b34235d0a96729577cab3bd7 Mon Sep 17 00:00:00 2001 From: Christopher Baines Date: Sat, 4 Jul 2020 08:59:29 +0100 Subject: Add a index to derivation_output_details_sets This helps with queries like: SELECT * FROM derivation_output_details_sets WHERE ARRAY[5563227] <@ derivation_output_details_ids; --- ...n_output_details_sets_derivation_output_details_ids_index.sql | 9 +++++++++ ...n_output_details_sets_derivation_output_details_ids_index.sql | 7 +++++++ sqitch/sqitch.plan | 1 + ...n_output_details_sets_derivation_output_details_ids_index.sql | 7 +++++++ 4 files changed, 24 insertions(+) create mode 100644 sqitch/deploy/derivation_output_details_sets_derivation_output_details_ids_index.sql create mode 100644 sqitch/revert/derivation_output_details_sets_derivation_output_details_ids_index.sql create mode 100644 sqitch/verify/derivation_output_details_sets_derivation_output_details_ids_index.sql diff --git a/sqitch/deploy/derivation_output_details_sets_derivation_output_details_ids_index.sql b/sqitch/deploy/derivation_output_details_sets_derivation_output_details_ids_index.sql new file mode 100644 index 0000000..c755127 --- /dev/null +++ b/sqitch/deploy/derivation_output_details_sets_derivation_output_details_ids_index.sql @@ -0,0 +1,9 @@ +-- Deploy guix-data-service:derivation_output_details_sets_derivation_output_details_ids_index to pg + +BEGIN; + +CREATE INDEX derivation_output_details_sets_gin_idx + ON derivation_output_details_sets + USING GIN (derivation_output_details_ids); + +COMMIT; diff --git a/sqitch/revert/derivation_output_details_sets_derivation_output_details_ids_index.sql b/sqitch/revert/derivation_output_details_sets_derivation_output_details_ids_index.sql new file mode 100644 index 0000000..aef856b --- /dev/null +++ b/sqitch/revert/derivation_output_details_sets_derivation_output_details_ids_index.sql @@ -0,0 +1,7 @@ +-- Revert guix-data-service:derivation_output_details_sets_derivation_output_details_ids_index from pg + +BEGIN; + +-- XXX Add DDLs here. + +COMMIT; diff --git a/sqitch/sqitch.plan b/sqitch/sqitch.plan index c47cb84..8b165c1 100644 --- a/sqitch/sqitch.plan +++ b/sqitch/sqitch.plan @@ -65,3 +65,4 @@ build_status_nullable_timestamp 2020-06-27T12:34:29Z Christopher Baines # Add builds.build_server_build_id build_status_add_unique_index 2020-06-30T17:19:30Z Christopher Baines # Add a unique index to the build_status table builds_remove_build_server_id_derivation_unique_constraint 2020-06-30T20:17:48Z Christopher Baines # Allow multiple builds of the same derivation per build server +derivation_output_details_sets_derivation_output_details_ids_index 2020-07-04T07:56:49Z Christopher Baines # Add GIN index on derivation_output_details_ids diff --git a/sqitch/verify/derivation_output_details_sets_derivation_output_details_ids_index.sql b/sqitch/verify/derivation_output_details_sets_derivation_output_details_ids_index.sql new file mode 100644 index 0000000..977edb2 --- /dev/null +++ b/sqitch/verify/derivation_output_details_sets_derivation_output_details_ids_index.sql @@ -0,0 +1,7 @@ +-- Verify guix-data-service:derivation_output_details_sets_derivation_output_details_ids_index on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; -- cgit v1.2.3