From 762ecaae2c3f244d3002e28b2704b3c01b6c5636 Mon Sep 17 00:00:00 2001 From: miker Date: Sat, 2 Apr 2011 17:06:41 +0000 Subject: [PATCH] Add realistic row estimates to tree-ish functions (default is 1000) so that callers can make better plans in complex queries git-svn-id: svn://svn.open-ils.org/ILS/trunk@19930 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/src/sql/Pg/006.schema.permissions.sql | 16 +++++++------- Open-ILS/src/sql/Pg/020.schema.functions.sql | 20 ++++++++--------- ...0506.schema.tree-ish_function_row_estimates.sql | 25 ++++++++++++++++++++++ 3 files changed, 43 insertions(+), 18 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/0506.schema.tree-ish_function_row_estimates.sql diff --git a/Open-ILS/src/sql/Pg/006.schema.permissions.sql b/Open-ILS/src/sql/Pg/006.schema.permissions.sql index baf2777dc..27d5a428c 100644 --- a/Open-ILS/src/sql/Pg/006.schema.permissions.sql +++ b/Open-ILS/src/sql/Pg/006.schema.permissions.sql @@ -99,7 +99,7 @@ CREATE OR REPLACE FUNCTION permission.grp_ancestors ( INT ) RETURNS SETOF permis THEN 0 ELSE 1 END, a.name; -$$ LANGUAGE SQL STABLE; +$$ LANGUAGE SQL STABLE ROWS 1; CREATE OR REPLACE FUNCTION permission.grp_ancestors_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$ WITH RECURSIVE grp_ancestors_distance(id, distance) AS ( @@ -110,7 +110,7 @@ CREATE OR REPLACE FUNCTION permission.grp_ancestors_distance( INT ) RETURNS TABL WHERE pgt.parent IS NOT NULL ) SELECT * FROM grp_ancestors_distance; -$$ LANGUAGE SQL STABLE; +$$ LANGUAGE SQL STABLE ROWS 1; CREATE OR REPLACE FUNCTION permission.grp_descendants_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$ WITH RECURSIVE grp_descendants_distance(id, distance) AS ( @@ -120,7 +120,7 @@ CREATE OR REPLACE FUNCTION permission.grp_descendants_distance( INT ) RETURNS TA FROM permission.grp_tree pgt JOIN grp_descendants_distance gdd ON (pgt.parent = gdd.id) ) SELECT * FROM grp_descendants_distance; -$$ LANGUAGE SQL STABLE; +$$ LANGUAGE SQL STABLE ROWS 1; CREATE OR REPLACE FUNCTION permission.usr_perms ( INT ) RETURNS SETOF permission.usr_perm_map AS $$ SELECT DISTINCT ON (usr,perm) * @@ -141,7 +141,7 @@ CREATE OR REPLACE FUNCTION permission.usr_perms ( INT ) RETURNS SETOF permission WHERE p.grp IN (SELECT (permission.grp_ancestors(m.grp)).id FROM permission.usr_grp_map m WHERE usr = $1)) ) AS x ORDER BY 2, 3, 1 DESC, 5 DESC ; -$$ LANGUAGE SQL STABLE; +$$ LANGUAGE SQL STABLE ROWS 10; CREATE TABLE permission.usr_work_ou_map ( id SERIAL PRIMARY KEY, @@ -527,7 +527,7 @@ BEGIN RETURN; -- END; -$$ LANGUAGE 'plpgsql'; +$$ LANGUAGE 'plpgsql' ROWS 1; CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_all_nd( @@ -583,7 +583,7 @@ BEGIN RETURN; -- END; -$$ LANGUAGE 'plpgsql'; +$$ LANGUAGE 'plpgsql' ROWS 1; CREATE OR REPLACE FUNCTION permission.usr_has_perm_at( @@ -592,7 +592,7 @@ CREATE OR REPLACE FUNCTION permission.usr_has_perm_at( ) RETURNS SETOF INTEGER AS $$ SELECT DISTINCT * FROM permission.usr_has_perm_at_nd( $1, $2 ); -$$ LANGUAGE 'sql'; +$$ LANGUAGE 'sql' ROWS 1; CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_all( @@ -601,7 +601,7 @@ CREATE OR REPLACE FUNCTION permission.usr_has_perm_at_all( ) RETURNS SETOF INTEGER AS $$ SELECT DISTINCT * FROM permission.usr_has_perm_at_all_nd( $1, $2 ); -$$ LANGUAGE 'sql'; +$$ LANGUAGE 'sql' ROWS 1; COMMIT; diff --git a/Open-ILS/src/sql/Pg/020.schema.functions.sql b/Open-ILS/src/sql/Pg/020.schema.functions.sql index df3ef9049..a9b9cce53 100644 --- a/Open-ILS/src/sql/Pg/020.schema.functions.sql +++ b/Open-ILS/src/sql/Pg/020.schema.functions.sql @@ -204,7 +204,7 @@ CREATE OR REPLACE FUNCTION actor.org_unit_descendants( INT, INT ) RETURNS SETOF JOIN actor.org_unit_type out ON (out.id = ou.ou_type) JOIN anscestor_depth ot ON (ot.parent_ou = ou.id) ) SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id); -$$ LANGUAGE SQL; +$$ LANGUAGE SQL ROWS 1; CREATE OR REPLACE FUNCTION actor.org_unit_descendants( INT ) RETURNS SETOF actor.org_unit AS $$ WITH RECURSIVE descendant_depth AS ( @@ -222,7 +222,7 @@ CREATE OR REPLACE FUNCTION actor.org_unit_descendants( INT ) RETURNS SETOF actor JOIN actor.org_unit_type out ON (out.id = ou.ou_type) JOIN descendant_depth ot ON (ot.id = ou.parent_ou) ) SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id); -$$ LANGUAGE SQL; +$$ LANGUAGE SQL ROWS 1; CREATE OR REPLACE FUNCTION actor.org_unit_descendants_distance( INT ) RETURNS TABLE (id INT, distance INT) AS $$ WITH RECURSIVE org_unit_descendants_distance(id, distance) AS ( @@ -232,7 +232,7 @@ CREATE OR REPLACE FUNCTION actor.org_unit_descendants_distance( INT ) RETURNS TA FROM actor.org_unit ou JOIN org_unit_descendants_distance oudd ON (ou.parent_ou = oudd.id) ) SELECT * FROM org_unit_descendants_distance; -$$ LANGUAGE SQL STABLE; +$$ LANGUAGE SQL STABLE ROWS 1; CREATE OR REPLACE FUNCTION actor.org_unit_ancestors( INT ) RETURNS SETOF actor.org_unit AS $$ WITH RECURSIVE anscestor_depth AS ( @@ -246,7 +246,7 @@ CREATE OR REPLACE FUNCTION actor.org_unit_ancestors( INT ) RETURNS SETOF actor.o FROM actor.org_unit ou JOIN anscestor_depth ot ON (ot.parent_ou = ou.id) ) SELECT ou.* FROM actor.org_unit ou JOIN anscestor_depth USING (id); -$$ LANGUAGE SQL; +$$ LANGUAGE SQL ROWS 1; CREATE OR REPLACE FUNCTION actor.org_unit_ancestor_at_depth ( INT,INT ) RETURNS actor.org_unit AS $$ SELECT a.* @@ -266,7 +266,7 @@ CREATE OR REPLACE FUNCTION actor.org_unit_ancestors_distance( INT ) RETURNS TABL WHERE ou.parent_ou IS NOT NULL ) SELECT * FROM org_unit_ancestors_distance; -$$ LANGUAGE SQL STABLE; +$$ LANGUAGE SQL STABLE ROWS 1; CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor.org_unit AS $$ SELECT * @@ -274,11 +274,11 @@ CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT ) RETURNS SETOF actor. UNION SELECT * FROM actor.org_unit_descendants($1); -$$ LANGUAGE SQL STABLE; +$$ LANGUAGE SQL STABLE ROWS 1; CREATE OR REPLACE FUNCTION actor.org_unit_full_path ( INT, INT ) RETURNS SETOF actor.org_unit AS $$ SELECT * FROM actor.org_unit_full_path((actor.org_unit_ancestor_at_depth($1, $2)).id) -$$ LANGUAGE SQL STABLE; +$$ LANGUAGE SQL STABLE ROWS 1; CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$ SELECT * @@ -286,7 +286,7 @@ CREATE OR REPLACE FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) RETURN UNION SELECT * FROM actor.org_unit_ancestors($2); -$$ LANGUAGE SQL STABLE; +$$ LANGUAGE SQL STABLE ROWS 1; CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS SETOF actor.org_unit AS $$ SELECT * @@ -294,7 +294,7 @@ CREATE OR REPLACE FUNCTION actor.org_unit_common_ancestors ( INT, INT ) RETURNS INTERSECT SELECT * FROM actor.org_unit_ancestors($2); -$$ LANGUAGE SQL STABLE; +$$ LANGUAGE SQL STABLE ROWS 1; CREATE OR REPLACE FUNCTION actor.org_unit_proximity ( INT, INT ) RETURNS INT AS $$ SELECT COUNT(id)::INT FROM ( @@ -320,7 +320,7 @@ BEGIN END LOOP; RETURN; END; -$$ LANGUAGE plpgsql STABLE; +$$ LANGUAGE plpgsql STABLE ROWS 1; COMMENT ON FUNCTION actor.org_unit_ancestor_setting( TEXT, INT) IS $$ /** diff --git a/Open-ILS/src/sql/Pg/upgrade/0506.schema.tree-ish_function_row_estimates.sql b/Open-ILS/src/sql/Pg/upgrade/0506.schema.tree-ish_function_row_estimates.sql new file mode 100644 index 000000000..5cc0dc2f8 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/0506.schema.tree-ish_function_row_estimates.sql @@ -0,0 +1,25 @@ +BEGIN; + +INSERT INTO config.upgrade_log (version) VALUES ('0506'); -- miker + +ALTER FUNCTION actor.org_unit_descendants( INT, INT ) ROWS 1; +ALTER FUNCTION actor.org_unit_descendants( INT ) ROWS 1; +ALTER FUNCTION actor.org_unit_descendants_distance( INT ) ROWS 1; +ALTER FUNCTION actor.org_unit_ancestors( INT ) ROWS 1; +ALTER FUNCTION actor.org_unit_ancestors_distance( INT ) ROWS 1; +ALTER FUNCTION actor.org_unit_full_path ( INT ) ROWS 2; +ALTER FUNCTION actor.org_unit_full_path ( INT, INT ) ROWS 2; +ALTER FUNCTION actor.org_unit_combined_ancestors ( INT, INT ) ROWS 1; +ALTER FUNCTION actor.org_unit_common_ancestors ( INT, INT ) ROWS 1; +ALTER FUNCTION actor.org_unit_ancestor_setting( TEXT, INT ) ROWS 1; +ALTER FUNCTION permission.grp_ancestors ( INT ) ROWS 1; +ALTER FUNCTION permission.grp_ancestors_distance( INT ) ROWS 1; +ALTER FUNCTION permission.grp_descendants_distance( INT ) ROWS 1; +ALTER FUNCTION permission.usr_perms ( INT ) ROWS 10; +ALTER FUNCTION permission.usr_has_perm_at_nd ( INT, TEXT) ROWS 1; +ALTER FUNCTION permission.usr_has_perm_at_all_nd ( INT, TEXT ) ROWS 1; +ALTER FUNCTION permission.usr_has_perm_at ( INT, TEXT ) ROWS 1; +ALTER FUNCTION permission.usr_has_perm_at_all ( INT, TEXT ) ROWS 1; + +COMMIT; + -- 2.11.0