From 5e36baf3a8fc4754693e6813df6be1ef0108aa16 Mon Sep 17 00:00:00 2001 From: Galen Charlton Date: Wed, 8 Feb 2012 10:01:24 -0500 Subject: [PATCH] LP#928896 improve row estimate for action.usr_visible_circ_copies() Fixes problem where use of this function by the tagging circs in search results feature can result in sequential scans of asset.call_number. Thanks to Mike Rylander for the suggestion. Signed-off-by: Galen Charlton Signed-off-by: Bill Erickson --- Open-ILS/src/sql/Pg/090.schema.action.sql | 2 +- ...XX.schema.usr_visible_circ_copies_row_estimates.sql | 10 ++++++++++ 2 files changed, 11 insertions(+), 1 deletion(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.usr_visible_circ_copies_row_estimates.sql diff --git a/Open-ILS/src/sql/Pg/090.schema.action.sql b/Open-ILS/src/sql/Pg/090.schema.action.sql index 715c64aec7..1af6e89ead 100644 --- a/Open-ILS/src/sql/Pg/090.schema.action.sql +++ b/Open-ILS/src/sql/Pg/090.schema.action.sql @@ -699,7 +699,7 @@ $func$ LANGUAGE PLPGSQL; CREATE OR REPLACE FUNCTION action.usr_visible_circ_copies( INTEGER ) RETURNS SETOF BIGINT AS $$ SELECT DISTINCT(target_copy) FROM action.usr_visible_circs($1) -$$ LANGUAGE SQL; +$$ LANGUAGE SQL ROWS 10; CREATE OR REPLACE FUNCTION action.usr_visible_holds (usr_id INT) RETURNS SETOF action.hold_request AS $func$ DECLARE diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.usr_visible_circ_copies_row_estimates.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.usr_visible_circ_copies_row_estimates.sql new file mode 100644 index 0000000000..a191fb498f --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.usr_visible_circ_copies_row_estimates.sql @@ -0,0 +1,10 @@ +BEGIN; + +-- set expected row count to low value to avoid problem +-- where use of this function by the circ tagging feature +-- results in full scans of asset.call_number +CREATE OR REPLACE FUNCTION action.usr_visible_circ_copies( INTEGER ) RETURNS SETOF BIGINT AS $$ + SELECT DISTINCT(target_copy) FROM action.usr_visible_circs($1) +$$ LANGUAGE SQL ROWS 10; + +COMMIT; -- 2.43.2