From 28fdfa811d89e26e96d2743a4ebfe78ea36b99e8 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Fri, 30 Sep 2016 10:56:51 -0400 Subject: [PATCH] JBAS-1604 Vandelay queue delete speedup indexes Add indexes to make deleting vandelay queue data faster, specifically when vandelay records link to import items and acq lineitems. Signed-off-by: Bill Erickson --- KCLS/sql/schema/deploy/vand-delete-speed-indexes.sql | 9 +++++++++ KCLS/sql/schema/revert/vand-delete-speed-indexes.sql | 8 ++++++++ KCLS/sql/schema/sqitch.plan | 1 + KCLS/sql/schema/verify/vand-delete-speed-indexes.sql | 7 +++++++ 4 files changed, 25 insertions(+) create mode 100644 KCLS/sql/schema/deploy/vand-delete-speed-indexes.sql create mode 100644 KCLS/sql/schema/revert/vand-delete-speed-indexes.sql create mode 100644 KCLS/sql/schema/verify/vand-delete-speed-indexes.sql diff --git a/KCLS/sql/schema/deploy/vand-delete-speed-indexes.sql b/KCLS/sql/schema/deploy/vand-delete-speed-indexes.sql new file mode 100644 index 0000000000..ca0c86904d --- /dev/null +++ b/KCLS/sql/schema/deploy/vand-delete-speed-indexes.sql @@ -0,0 +1,9 @@ +-- Deploy kcls-evergreen:vand-delete-speed-indexes to pg +-- requires: self-reg-net-access-level + +BEGIN; + +create index lineitem_queued_record_idx on acq.lineitem (queued_record); +create index import_item_record_idx on vandelay.import_item (record); + +COMMIT; diff --git a/KCLS/sql/schema/revert/vand-delete-speed-indexes.sql b/KCLS/sql/schema/revert/vand-delete-speed-indexes.sql new file mode 100644 index 0000000000..3fc3c81e06 --- /dev/null +++ b/KCLS/sql/schema/revert/vand-delete-speed-indexes.sql @@ -0,0 +1,8 @@ +-- Revert kcls-evergreen:vand-delete-speed-indexes from pg + +BEGIN; + +DROP INDEX IF EXISTS acq.lineitem_queued_record_idx; +DROP INDEX IF EXISTS vandelay.import_item_record_idx; + +COMMIT; diff --git a/KCLS/sql/schema/sqitch.plan b/KCLS/sql/schema/sqitch.plan index 796d979628..fb2f7675dd 100644 --- a/KCLS/sql/schema/sqitch.plan +++ b/KCLS/sql/schema/sqitch.plan @@ -35,3 +35,4 @@ teacher-group [student-groups] 2016-09-16T17:43:41Z Bill Erickson # Support for reporting on new browse headings copy-status-alerts [purge-user-activity] 2016-05-25T14:10:24Z Bill Erickson # Reduce copy checkin alerts by status self-reg-net-access-level [purge-user-activity] 2016-05-25T14:59:56Z Bill Erickson # Staged users get net_access_level +vand-delete-speed-indexes [self-reg-net-access-level] 2016-09-30T14:52:21Z Bill Erickson # Indexes to speed up vandelay queue deleting diff --git a/KCLS/sql/schema/verify/vand-delete-speed-indexes.sql b/KCLS/sql/schema/verify/vand-delete-speed-indexes.sql new file mode 100644 index 0000000000..f8e50e61c1 --- /dev/null +++ b/KCLS/sql/schema/verify/vand-delete-speed-indexes.sql @@ -0,0 +1,7 @@ +-- Verify kcls-evergreen:vand-delete-speed-indexes on pg + +BEGIN; + +-- XXX Add verifications here. + +ROLLBACK; -- 2.11.0