From 59b8392d2ba3db67bc6928ae7a3f2b6901aefa72 Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Wed, 16 Dec 2015 11:03:42 -0500 Subject: [PATCH] LP#1526858: speed up queries for shelf-expired holds during checkin This patch adds indexes on action.hold_request and tweaks the definition of the alhr IDL view to more efficiently identify holds that can be canceled if the "Clear Holds Shelf" checkin modifier is in use. This can prevent a problem where such checkins can time out in very large databases. The tweaks were created by Jason Etheridge and have been in production at a large consortium for some time. To test ------- [1] With a suitably large database, set up an item that is on the holds shelf but has passed its shelf-expiration time, then check it in using the "Clear Holds Shelf" modifier. [2] The checkin may fail with a "network failure" message due the query on the alhr timing out. [3] Apply the patch and repeat step 1. This time, the checkin should succeed. Signed-off-by: Galen Charlton Signed-off-by: Jeanette Lundgren Signed-off-by: Kathy Lussier --- Open-ILS/examples/fm_IDL.xml | 2 +- Open-ILS/src/sql/Pg/090.schema.action.sql | 2 ++ .../src/sql/Pg/upgrade/XXXX.schema.indexes_for_alhr_queries.sql | 8 ++++++++ 3 files changed, 11 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.indexes_for_alhr_queries.sql diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 017edba55c..d4c005c3ba 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -5740,7 +5740,7 @@ SELECT usr, - SELECT ahr.* FROM action.hold_request ahr JOIN (SELECT current_copy, MAX(capture_time) AS capture_time FROM action.hold_request WHERE capture_time IS NOT NULL GROUP BY current_copy)x USING (current_copy, capture_time) + SELECT ahr.* FROM action.hold_request ahr JOIN (SELECT current_copy, MAX(capture_time) AS capture_time FROM action.hold_request WHERE capture_time IS NOT NULL AND current_copy IS NOT NULL AND fulfillment_time IS NULL GROUP BY current_copy)x USING (current_copy, capture_time) diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index ca1e7f3894..e9675e22e4 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -430,6 +430,8 @@ CREATE INDEX hold_request_requestor_idx ON action.hold_request ( request CREATE INDEX hold_request_open_idx ON action.hold_request (id) WHERE cancel_time IS NULL AND fulfillment_time IS NULL; CREATE INDEX hold_request_current_copy_before_cap_idx ON action.hold_request (current_copy) WHERE capture_time IS NULL AND cancel_time IS NULL; CREATE UNIQUE INDEX hold_request_capture_protect_idx ON action.hold_request (current_copy) WHERE current_copy IS NOT NULL AND capture_time IS NOT NULL AND cancel_time IS NULL AND fulfillment_time IS NULL; +CREATE INDEX hold_request_copy_capture_time_idx ON action.hold_request (current_copy,capture_time); +CREATE INDEX hold_request_open_captured_shelf_lib_idx ON action.hold_request (current_shelf_lib) WHERE capture_time IS NOT NULL AND fulfillment_time IS NULL AND (pickup_lib <> current_shelf_lib); CREATE TABLE action.hold_request_note ( diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.indexes_for_alhr_queries.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.indexes_for_alhr_queries.sql new file mode 100644 index 0000000000..afc83b8b15 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.indexes_for_alhr_queries.sql @@ -0,0 +1,8 @@ +BEGIN; + +--SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE INDEX hold_request_copy_capture_time_idx ON action.hold_request (current_copy,capture_time); +CREATE INDEX hold_request_open_captured_shelf_lib_idx ON action.hold_request (current_shelf_lib) WHERE capture_time IS NOT NULL AND fulfillment_time IS NULL AND (pickup_lib <> current_shelf_lib); + +COMMIT; -- 2.11.0