From 11f098648579930f6eef552866af67b7795dae29 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Fri, 9 Dec 2022 12:41:43 -0500 Subject: [PATCH] LP#1999274: Improve Search Performance on Pg 12+ Use OFFSET as an optimization fence to keep newer PGs from trying to fold the c_attr and b_attr CTEs into the main search query. Signed-off-by: Mike Rylander Signed-off-by: Jason Stephenson Signed-off-by: Jane Sandberg --- .../perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm index fe45ac1594..b11a44c2d3 100644 --- a/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm +++ b/Open-ILS/src/perlmods/lib/OpenILS/Application/Storage/Driver/Pg/QueryParser.pm @@ -1052,7 +1052,7 @@ sub toSQL { # WITH-clause just generates vis test $$flat_plan{with} .= "\n," if $$flat_plan{with}; - $$flat_plan{with} .= "c_attr AS (SELECT (ARRAY_TO_STRING(ARRAY[$vis_test],'&'))::query_int AS vis_test FROM asset.patron_default_visibility_mask() x)"; + $$flat_plan{with} .= "c_attr AS (SELECT (ARRAY_TO_STRING(ARRAY[$vis_test],'&'))::query_int AS vis_test FROM asset.patron_default_visibility_mask() x OFFSET 0)"; $final_c_attr_test = 'EXISTS (SELECT 1 FROM asset.copy_vis_attr_cache WHERE record = m.source AND vis_attr_vector @@ c_attr.vis_test)'; } @@ -1097,7 +1097,7 @@ sub toSQL { # WITH-clause just generates vis test $$flat_plan{with} .= "\n," if $$flat_plan{with}; - $$flat_plan{with} .= "b_attr AS (SELECT ($vis_test)::query_int AS vis_test FROM asset.patron_default_visibility_mask() x)"; + $$flat_plan{with} .= "b_attr AS (SELECT ($vis_test)::query_int AS vis_test FROM asset.patron_default_visibility_mask() x OFFSET 0)"; # These are magic numbers... see: search.calculate_visibility_attribute() UDF $final_b_attr_test = '(b_attr.vis_test IS NULL OR bre.vis_attr_vector @@ b_attr.vis_test)'; -- 2.11.0