From 652ec745a14f3c337f89647d14739aa96f1726b3 Mon Sep 17 00:00:00 2001 From: Ben Shum Date: Wed, 13 Mar 2013 22:50:35 -0400 Subject: [PATCH] Stamping upgrade for new general indexes Note: Changed the commands in the upgrade to CREATE INDEX CONCURRENTLY to be in all caps to satisfy my own small sense of consistency. Signed-off-by: Ben Shum --- Open-ILS/src/sql/Pg/002.schema.config.sql | 2 +- ... => 0782.schema.general-indexing-needs.sql} | 18 +++++++++--------- 2 files changed, 10 insertions(+), 10 deletions(-) rename Open-ILS/src/sql/Pg/upgrade/{XXXX.schema.general-indexing-needs.sql => 0782.schema.general-indexing-needs.sql} (74%) diff --git a/Open-ILS/src/sql/Pg/002.schema.config.sql b/Open-ILS/src/sql/Pg/002.schema.config.sql index e615146881..c1feea7af6 100644 --- a/Open-ILS/src/sql/Pg/002.schema.config.sql +++ b/Open-ILS/src/sql/Pg/002.schema.config.sql @@ -91,7 +91,7 @@ CREATE TRIGGER no_overlapping_deps BEFORE INSERT OR UPDATE ON config.db_patch_dependencies FOR EACH ROW EXECUTE PROCEDURE evergreen.array_overlap_check ('deprecates'); -INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0781', :eg_version); -- berick/senator +INSERT INTO config.upgrade_log (version, applied_to) VALUES ('0782', :eg_version); -- miker/bshum CREATE TABLE config.bib_source ( id SERIAL PRIMARY KEY, diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.general-indexing-needs.sql b/Open-ILS/src/sql/Pg/upgrade/0782.schema.general-indexing-needs.sql similarity index 74% rename from Open-ILS/src/sql/Pg/upgrade/XXXX.schema.general-indexing-needs.sql rename to Open-ILS/src/sql/Pg/upgrade/0782.schema.general-indexing-needs.sql index 51d9897dfe..2ec811f28f 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.general-indexing-needs.sql +++ b/Open-ILS/src/sql/Pg/upgrade/0782.schema.general-indexing-needs.sql @@ -1,49 +1,49 @@ -- No transaction needed. This can be run on a live, production server. -SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); +SELECT evergreen.upgrade_deps_block_check('0782', :eg_version); -- On a heavily used system, user activity lookup is painful. This is used -- on the patron display in the staff client. -- -- Measured speed increase: ~2s -> .01s -create index concurrently usr_activity_usr_idx on actor.usr_activity (usr); +CREATE INDEX CONCURRENTLY usr_activity_usr_idx on actor.usr_activity (usr); -- Finding open holds, often as a subquery within larger hold-related logic, -- can be sped up with the following. -- -- Measured speed increase: ~3s -> .02s -create index concurrently hold_request_open_idx on action.hold_request (id) where cancel_time IS NULL AND fulfillment_time IS NULL; +CREATE INDEX CONCURRENTLY hold_request_open_idx on action.hold_request (id) where cancel_time IS NULL AND fulfillment_time IS NULL; -- Hold queue position is a particularly difficult thing to calculate -- efficiently. Recent changes in the query structure now allow some -- optimization via indexing. These do that. -- -- Measured speed increase: ~6s -> ~0.4s -create index concurrently cp_available_by_circ_lib_idx on asset.copy (circ_lib) where status IN (0,7); -create index concurrently hold_request_current_copy_before_cap_idx on action.hold_request (current_copy) where capture_time IS NULL AND cancel_time IS NULL; +CREATE INDEX CONCURRENTLY cp_available_by_circ_lib_idx on asset.copy (circ_lib) where status IN (0,7); +CREATE INDEX CONCURRENTLY hold_request_current_copy_before_cap_idx on action.hold_request (current_copy) where capture_time IS NULL AND cancel_time IS NULL; -- After heavy use, fetching EDI messages becomes time consuming. The following -- index addresses that for large-data environments. -- -- Measured speed increase: ~3s -> .1s -create index concurrently edi_message_account_status_idx on acq.edi_message (account,status); +CREATE INDEX CONCURRENTLY edi_message_account_status_idx on acq.edi_message (account,status); -- After heavy use, fetching POs becomes time consuming. The following -- index addresses that for large-data environments. -- -- Measured speed increase: ~1.5s -> .1s -create index concurrently edi_message_po_idx on acq.edi_message (purchase_order); +CREATE INDEX CONCURRENTLY edi_message_po_idx on acq.edi_message (purchase_order); -- Related to EDI messages, fetching of certain A/T events benefit from specific -- indexing. This index is more general than necessary for the observed query -- but ends up speeding several other (already relatively fast) queries. -- -- Measured speed increase: ~2s -> .06s -create index concurrently atev_def_state on action_trigger.event (event_def,state); +CREATE INDEX CONCURRENTLY atev_def_state on action_trigger.event (event_def,state); -- Retrieval of hold transit by hold id (for transit completion or cancelation) -- is slow in some query formulations. -- -- Measured speed increase: ~.5s -> .1s -create index concurrently hold_transit_copy_hold_idx on action.hold_transit_copy (hold); +CREATE INDEX CONCURRENTLY hold_transit_copy_hold_idx on action.hold_transit_copy (hold); -- 2.43.2