From 6112711f6f4f99bff3ef696b43b22888f6514fe9 Mon Sep 17 00:00:00 2001 From: Jeff Davis Date: Thu, 30 Jul 2015 13:12:35 -0700 Subject: [PATCH] LP#1479953: Add indexes to vqbr foreign key references Deleting a bib queue can be quite slow if it contains many queued records. This is because each queued record needs to be deleted, and this in turn affects many tables that contain unindexed foreign key references to vandelay.queued_bib_record. Indexing those foreign keys can speed up bib queue deletion substantially. Signed-off-by: Jeff Davis Signed-off-by: Kathy Lussier --- Open-ILS/src/sql/Pg/012.schema.vandelay.sql | 2 ++ Open-ILS/src/sql/Pg/200.schema.acq.sql | 1 + Open-ILS/src/sql/Pg/201.acq.audit-functions.sql | 1 + Open-ILS/src/sql/Pg/upgrade/XXXX.index.queued_record.sql | 15 +++++++++++++++ 4 files changed, 19 insertions(+) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.index.queued_record.sql diff --git a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql index b86eda1a9b..25e1a6dc8d 100644 --- a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql +++ b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql @@ -150,6 +150,7 @@ CREATE TABLE vandelay.bib_match ( quality INT NOT NULL DEFAULT 1, match_score INT NOT NULL DEFAULT 0 ); +CREATE INDEX bib_match_queued_record ON vandelay.bib_match (queued_record); CREATE TABLE vandelay.import_item ( id BIGSERIAL PRIMARY KEY, @@ -180,6 +181,7 @@ CREATE TABLE vandelay.import_item ( opac_visible BOOL, internal_id BIGINT -- queue_type == 'acq' ? acq.lineitem_detail.id : asset.copy.id ); +CREATE INDEX import_item_record ON vandelay.import_item (record); CREATE TABLE vandelay.import_bib_trash_group( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql index 91204a48ed..9c9f9e5163 100644 --- a/Open-ILS/src/sql/Pg/200.schema.acq.sql +++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql @@ -489,6 +489,7 @@ CREATE INDEX li_pl_idx ON acq.lineitem (picklist); CREATE INDEX li_creator_idx ON acq.lineitem ( creator ); CREATE INDEX li_editor_idx ON acq.lineitem ( editor ); CREATE INDEX li_selector_idx ON acq.lineitem ( selector ); +CREATE INDEX li_queued_record ON acq.lineitem ( queued_record ); CREATE TABLE acq.lineitem_alert_text ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/201.acq.audit-functions.sql b/Open-ILS/src/sql/Pg/201.acq.audit-functions.sql index 51f3507ea4..383e29fa4d 100644 --- a/Open-ILS/src/sql/Pg/201.acq.audit-functions.sql +++ b/Open-ILS/src/sql/Pg/201.acq.audit-functions.sql @@ -103,5 +103,6 @@ CREATE INDEX acq_po_hist_id_idx ON acq.acq_purchase_order_history( id SELECT acq.create_acq_auditor ( 'acq', 'lineitem' ); CREATE INDEX acq_lineitem_hist_id_idx ON acq.acq_lineitem_history( id ); +CREATE INDEX acq_lineitem_history_queued_record ON acq.acq_lineitem_history (queued_record); COMMIT; diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.index.queued_record.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.index.queued_record.sql new file mode 100644 index 0000000000..4ca13c74cb --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.index.queued_record.sql @@ -0,0 +1,15 @@ +BEGIN; + +/* LP#1479953: Adding indexes to foreign key references to + * vandelay.queued_bib_record will speed up deletions of vqbr records (thereby + * speeding up vandelay.bib_queue deletions). + */ + +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE INDEX acq_lineitem_history_queued_record ON acq.acq_lineitem_history (queued_record); +CREATE INDEX li_queued_record ON acq.lineitem (queued_record); +CREATE INDEX bib_match_queued_record ON vandelay.bib_match (queued_record); +CREATE INDEX import_item_record ON vandelay.import_item (record); + +COMMIT; -- 2.11.0