From 9f8444fd8e2bbc339bf77533d29e8332665fcba6 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Thu, 11 Dec 2014 14:17:09 -0500 Subject: [PATCH] LP#1380709 acq.lineitem_detail.fund_debit INDEX Use of the new fund_debit.invoice_entry column includes a query joining fund_debits to lineitem_details. On sufficiently large data sets (tested on 2M rows), the query can take > 1 second without the index. Down to < 1ms with the index. Signed-off-by: Bill Erickson Signed-off-by: Jennifer Pringle Signed-off-by: Kathy Lussier --- Open-ILS/src/sql/Pg/200.schema.acq.sql | 1 + Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fund_debit_invoice_links.sql | 1 + 2 files changed, 2 insertions(+) diff --git a/Open-ILS/src/sql/Pg/200.schema.acq.sql b/Open-ILS/src/sql/Pg/200.schema.acq.sql index 3df7e855cd..309e80f1ca 100644 --- a/Open-ILS/src/sql/Pg/200.schema.acq.sql +++ b/Open-ILS/src/sql/Pg/200.schema.acq.sql @@ -541,6 +541,7 @@ CREATE TABLE acq.lineitem_detail ( ); CREATE INDEX li_detail_li_idx ON acq.lineitem_detail (lineitem); +CREATE INDEX lineitem_detail_fund_debit_idx ON acq.lineitem_detail (fund_debit); CREATE TABLE acq.lineitem_attr_definition ( id BIGSERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fund_debit_invoice_links.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fund_debit_invoice_links.sql index 7575ef9535..48560e4964 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fund_debit_invoice_links.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.fund_debit_invoice_links.sql @@ -8,5 +8,6 @@ ALTER TABLE acq.fund_debit ON DELETE SET NULL; CREATE INDEX fund_debit_invoice_entry_idx ON acq.fund_debit (invoice_entry); +CREATE INDEX lineitem_detail_fund_debit_idx ON acq.lineitem_detail (fund_debit); COMMIT; -- 2.11.0