From ff3fd28f623f35ffd54af55d9c87604ace76cfb4 Mon Sep 17 00:00:00 2001 From: Josh Stompro Date: Tue, 22 Nov 2022 12:09:46 -0600 Subject: [PATCH] LP#1971745 - speed up open-ils.storage.action.live_holds.wide_hash Postgresql 10 and EG 3.9.0 The query to grab holds on the hold shelf was taking 35-50 seconds on a test system to return 466 records. Explain analyze showed that the left join on asset.call_number was causing a sequential scan and reading all asset.call_number rows. Letting PG know that the two conditions of the join are mutually exclusive seems to let PG know that an index scan is faster. Signed-off-by: Josh Stompro Signed-off-by: Jason Stephenson --- .../src/perlmods/lib/OpenILS/Application/Storage/Publisher/action.pm | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/action.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/action.pm index 9a6c302904..80ff370cb8 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/action.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Publisher/action.pm @@ -2396,7 +2396,7 @@ SELECT h.id, h.request_time, h.capture_time, h.fulfillment_time, h.checkin_time SELECT *, (ROW_NUMBER() OVER (ORDER BY name) + 1000000) AS fallback_position FROM asset.copy_location ) acpl_ordered ON (acpl_ordered.id = cp.location) - LEFT JOIN asset.call_number cn ON (cn.id = cp.call_number OR (h.hold_type = 'V' AND cn.id = h.target)) + LEFT JOIN asset.call_number cn ON ((cn.id = cp.call_number AND h.hold_type != 'V' ) OR (h.hold_type = 'V' AND cn.id = h.target)) LEFT JOIN asset.call_number_prefix acnp ON (cn.prefix = acnp.id) LEFT JOIN asset.call_number_suffix acns ON (cn.suffix = acns.id) LEFT JOIN LATERAL (SELECT * FROM action.hold_transit_copy WHERE h.id = hold ORDER BY id DESC LIMIT 1) tr ON TRUE -- 2.11.0