From a67bc88d44ce930674b133d3e0c111e5cb614f8f 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 499a85ee31..66f4c60082 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -5765,7 +5765,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 74b2ca4fa1..c79793a321 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