From 8dd930394884260246f0cd0ae4f8deb5eaa49b57 Mon Sep 17 00:00:00 2001 From: dbs Date: Wed, 15 Dec 2010 20:22:21 +0000 Subject: [PATCH] Address the call number browsing performance problem raised in LP 690242 The ORDER BY clause currently generated by call number browsing does not have a sufficient index to use to assist the sorting of the returned rows, and consequently does a sequential scan of the asset.call_number table. Which, as you can imagine, is not fast for a system with more than a few thousand call numbers. This adds an index specifically to enable the query to go back to an index scan instead of a sequential scan. We can investigate whether other indexes should be removed to enable efficient data loading once we've squashed the sequential scan problem. git-svn-id: svn://svn.open-ils.org/ILS/trunk@19003 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- Open-ILS/src/sql/Pg/040.schema.asset.sql | 1 + Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql | 3 +++ ....schema.asset-call-number-add-sortkey-compound-index.sql | 13 +++++++++++++ 4 files changed, 18 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0471.schema.asset-call-number-add-sortkey-compound-index.sql diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index 7ec0e2071..b9c8b590a 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -70,7 +70,7 @@ CREATE TABLE config.upgrade_log ( install_date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW() ); -INSERT INTO config.upgrade_log (version) VALUES ('0470'); -- berick +INSERT INTO config.upgrade_log (version) VALUES ('0471'); -- dbs CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/040.schema.asset.sql b/Open-ILS/src/sql/Pg/040.schema.asset.sql index c453619f3..982e267e1 100644 --- a/Open-ILS/src/sql/Pg/040.schema.asset.sql +++ b/Open-ILS/src/sql/Pg/040.schema.asset.sql @@ -297,6 +297,7 @@ CREATE INDEX asset_call_number_dewey_idx ON asset.call_number (public.call_numbe CREATE INDEX asset_call_number_upper_label_id_owning_lib_idx ON asset.call_number (oils_text_as_bytea(label),id,owning_lib); CREATE INDEX asset_call_number_label_sortkey ON asset.call_number(oils_text_as_bytea(label_sortkey)); CREATE UNIQUE INDEX asset_call_number_label_once_per_lib ON asset.call_number (record, owning_lib, label) WHERE deleted = FALSE OR deleted IS FALSE; +CREATE INDEX asset_call_number_label_sortkey_browse ON asset.call_number(oils_text_as_bytea(label_sortkey), oils_text_as_bytea(label), id, owning_lib) WHERE deleted IS FALSE OR deleted = FALSE; CREATE RULE protect_cn_delete AS ON DELETE TO asset.call_number DO INSTEAD UPDATE asset.call_number SET deleted = TRUE WHERE OLD.id = asset.call_number.id; CREATE TRIGGER asset_label_sortkey_trigger BEFORE UPDATE OR INSERT ON asset.call_number diff --git a/Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql b/Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql index ed2f5883a..ad5a7ac3a 100644 --- a/Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql +++ b/Open-ILS/src/sql/Pg/1.6.1-2.0-upgrade-db.sql @@ -19066,6 +19066,9 @@ INSERT INTO action_trigger.event_definition ( -- Speed up item-age browse axis (new books feed) CREATE INDEX cp_create_date ON asset.copy (create_date); +-- Speed up call number browsing +CREATE INDEX asset_call_number_label_sortkey_browse ON asset.call_number(oils_text_as_bytea(label_sortkey), oils_text_as_bytea(label), id, owning_lib) WHERE deleted IS FALSE OR deleted = FALSE; + \qecho Upgrade script completed. \qecho But wait, there's more: please run reingest-1.6-2.0.pl \qecho in order to create an SQL script to run to partially reindex diff --git a/Open-ILS/src/sql/Pg/upgrade/0471.schema.asset-call-number-add-sortkey-compound-index.sql b/Open-ILS/src/sql/Pg/upgrade/0471.schema.asset-call-number-add-sortkey-compound-index.sql new file mode 100644 index 000000000..4449494c6 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0471.schema.asset-call-number-add-sortkey-compound-index.sql @@ -0,0 +1,13 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0471'); -- dbs + +-- Vanquish a sequential scan in call number browsing using the simplest +-- possible approach; yes, there is duplication with asset.asset_call_number_label_once_per_lib +-- and asset.asset_call_number_label_sortkey, but let's fix the first problem +-- and worry about data loading time later if that turns out to be a real +-- problem. + +CREATE INDEX asset_call_number_label_sortkey_browse ON asset.call_number(oils_text_as_bytea(label_sortkey), oils_text_as_bytea(label), id, owning_lib) WHERE deleted IS FALSE OR deleted = FALSE; + +COMMIT; -- 2.11.0