From 6f32e8f9b60bbdf8dab21bbb0c07b04f62828c83 Mon Sep 17 00:00:00 2001 From: hubert depesz lubaczewski Date: Thu, 3 Oct 2013 14:26:33 -0400 Subject: [PATCH] LP#1234845: Performance improvement to evergreen.ranked_volumes() database function. For the client I analyzed logs from production Pg instance for ~ 10 days. During this time, the single most time consuming query (summarized time for all instances of the query, with different parameters) was: SELECT * FROM unapi.bre ( ... ) AS "unapi.bre"; I profiled this function, and found that in my test case most of the time (2.04s out of 2.06s, so ~ 99%) was spent in call to unapi.holdings_xml() function. When I profiled this function, I found that most of the time (sorry, don't have the number now with me) was spent in call to evergreen.ranked_volumes() function. At this moment in my research something changed on the server I was testing on, and all subsequent times were ~ 4-5 times lower, but the ratios were more or less the same. Anyway - call to evergreen.ranked_volumes() showed repeatable time (with full caches/buffers) of ~ 380ms. I modified the function by: 1. inlining actor.org_unit_descendants(?, ?) 2. inlining evergreen.rank_ou(?, ?, ?) 3. extracting depth calculation to separate call 4. switched to plpgsql (which gives me ability to use variables) 5. removed evergreen.rank_ou() and evergreen.rank_cp_status() from select clause - these are still in WINDOW definition, but they weren't used in the SELECT, so it's better to remove from there. 6. in passing renamed arguments to avoid name clash (argument depth vs. field depth) 7. in passing changed usage of $* to access parameters to using named parameters, for readability. New function did the same work in ~ 18ms. EDIT: Convert to SQL, keeping all of the improvements from depesz EDIT2: Added Signed-off-by line for depesz, see http://markmail.org/message/rv4vaarwixeswqgu Signed-off-by: Hubert depesz Lubaczewski Signed-off-by: Jason Stephenson Signed-off-by: Mike Rylander Signed-off-by: Kathy Lussier Signed-off-by: Ben Shum --- Open-ILS/src/sql/Pg/990.schema.unapi.sql | 72 +++++++++++--- .../upgrade/XXXX.function.ranked_volumes.sql | 98 +++++++++++++++++++ 2 files changed, 157 insertions(+), 13 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.function.ranked_volumes.sql diff --git a/Open-ILS/src/sql/Pg/990.schema.unapi.sql b/Open-ILS/src/sql/Pg/990.schema.unapi.sql index 2c7c0b7dae..92e7fc8c85 100644 --- a/Open-ILS/src/sql/Pg/990.schema.unapi.sql +++ b/Open-ILS/src/sql/Pg/990.schema.unapi.sql @@ -72,21 +72,57 @@ CREATE OR REPLACE FUNCTION evergreen.ranked_volumes( soffset HSTORE DEFAULT NULL, pref_lib INT DEFAULT NULL, includes TEXT[] DEFAULT NULL::TEXT[] -) RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$ +) RETURNS TABLE(id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$ + WITH RECURSIVE ou_depth AS ( + SELECT COALESCE( + $3, + ( + SELECT depth + FROM actor.org_unit_type aout + INNER JOIN actor.org_unit ou ON ou_type = aout.id + WHERE ou.id = $2 + ) + ) AS depth + ), descendant_depth AS ( + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + JOIN anscestor_depth ad ON (ad.id = ou.id), + ou_depth + WHERE ad.depth = ou_depth.depth + UNION ALL + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + JOIN descendant_depth ot ON (ot.id = ou.parent_ou) + ), anscestor_depth AS ( + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + WHERE ou.id = $2 + UNION ALL + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + JOIN anscestor_depth ot ON (ot.parent_ou = ou.id) + ), descendants as ( + SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id) + ) + SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM ( SELECT acn.id, aou.name, acn.label_sortkey, - evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status), RANK() OVER w FROM asset.call_number acn JOIN asset.copy acp ON (acn.id = acp.call_number) - JOIN actor.org_unit_descendants( $2, COALESCE( - $3, ( - SELECT depth - FROM actor.org_unit_type aout - INNER JOIN actor.org_unit ou ON ou_type = aout.id - WHERE ou.id = $2 - ), $6) - ) AS aou ON (acp.circ_lib = aou.id) + JOIN descendants AS aou ON (acp.circ_lib = aou.id) WHERE acn.record = ANY ($1) AND acn.deleted IS FALSE AND acp.deleted IS FALSE @@ -98,15 +134,25 @@ CREATE OR REPLACE FUNCTION evergreen.ranked_volumes( ) ELSE TRUE END GROUP BY acn.id, acp.status, aou.name, acn.label_sortkey, aou.id WINDOW w AS ( - ORDER BY evergreen.rank_ou(aou.id, $2, $6), evergreen.rank_cp_status(acp.status) + ORDER BY + COALESCE( + CASE WHEN aou.id = $2 THEN -20000 END, + CASE WHEN aou.id = $6 THEN -10000 END, + (SELECT distance - 5000 + FROM actor.org_unit_descendants_distance($6) as x + WHERE x.id = aou.id AND $6 IN ( + SELECT q.id FROM actor.org_unit_descendants($2) as q)), + (SELECT e.distance FROM actor.org_unit_descendants_distance($2) as e WHERE e.id = aou.id), + 1000 + ), + evergreen.rank_cp_status(acp.status) ) ) AS ua GROUP BY ua.id, ua.name, ua.label_sortkey ORDER BY rank, ua.name, ua.label_sortkey LIMIT ($4 -> 'acn')::INT OFFSET ($5 -> 'acn')::INT; -$$ -LANGUAGE SQL STABLE; +$$ LANGUAGE SQL STABLE ROWS 10; CREATE OR REPLACE FUNCTION evergreen.ranked_volumes ( bibid BIGINT, ouid INT, depth INT DEFAULT NULL, slimit HSTORE DEFAULT NULL, soffset HSTORE DEFAULT NULL, pref_lib INT DEFAULT NULL, includes TEXT[] DEFAULT NULL::TEXT[] ) diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.ranked_volumes.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.ranked_volumes.sql new file mode 100644 index 0000000000..99c7594c8c --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.ranked_volumes.sql @@ -0,0 +1,98 @@ +BEGIN; + +CREATE OR REPLACE FUNCTION evergreen.ranked_volumes( + bibid BIGINT[], + ouid INT, + depth INT DEFAULT NULL, + slimit HSTORE DEFAULT NULL, + soffset HSTORE DEFAULT NULL, + pref_lib INT DEFAULT NULL, + includes TEXT[] DEFAULT NULL::TEXT[] +) RETURNS TABLE(id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$ + WITH RECURSIVE ou_depth AS ( + SELECT COALESCE( + $3, + ( + SELECT depth + FROM actor.org_unit_type aout + INNER JOIN actor.org_unit ou ON ou_type = aout.id + WHERE ou.id = $2 + ) + ) AS depth + ), descendant_depth AS ( + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + JOIN anscestor_depth ad ON (ad.id = ou.id), + ou_depth + WHERE ad.depth = ou_depth.depth + UNION ALL + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + JOIN descendant_depth ot ON (ot.id = ou.parent_ou) + ), anscestor_depth AS ( + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + WHERE ou.id = $2 + UNION ALL + SELECT ou.id, + ou.parent_ou, + out.depth + FROM actor.org_unit ou + JOIN actor.org_unit_type out ON (out.id = ou.ou_type) + JOIN anscestor_depth ot ON (ot.parent_ou = ou.id) + ), descendants as ( + SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id) + ) + + SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM ( + SELECT acn.id, aou.name, acn.label_sortkey, + RANK() OVER w + FROM asset.call_number acn + JOIN asset.copy acp ON (acn.id = acp.call_number) + JOIN descendants AS aou ON (acp.circ_lib = aou.id) + WHERE acn.record = ANY ($1) + AND acn.deleted IS FALSE + AND acp.deleted IS FALSE + AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN + EXISTS ( + SELECT 1 + FROM asset.opac_visible_copies + WHERE copy_id = acp.id AND record = acn.record + ) ELSE TRUE END + GROUP BY acn.id, acp.status, aou.name, acn.label_sortkey, aou.id + WINDOW w AS ( + ORDER BY + COALESCE( + CASE WHEN aou.id = $2 THEN -20000 END, + CASE WHEN aou.id = $6 THEN -10000 END, + (SELECT distance - 5000 + FROM actor.org_unit_descendants_distance($6) as x + WHERE x.id = aou.id AND $6 IN ( + SELECT q.id FROM actor.org_unit_descendants($2) as q)), + (SELECT e.distance FROM actor.org_unit_descendants_distance($2) as e WHERE e.id = aou.id), + 1000 + ), + evergreen.rank_cp_status(acp.status) + ) + ) AS ua + GROUP BY ua.id, ua.name, ua.label_sortkey + ORDER BY rank, ua.name, ua.label_sortkey + LIMIT ($4 -> 'acn')::INT + OFFSET ($5 -> 'acn')::INT; +$$ LANGUAGE SQL STABLE ROWS 10; + +CREATE OR REPLACE FUNCTION evergreen.ranked_volumes + ( bibid BIGINT, ouid INT, depth INT DEFAULT NULL, slimit HSTORE DEFAULT NULL, soffset HSTORE DEFAULT NULL, pref_lib INT DEFAULT NULL, includes TEXT[] DEFAULT NULL::TEXT[] ) + RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) + AS $$ SELECT * FROM evergreen.ranked_volumes(ARRAY[$1],$2,$3,$4,$5,$6,$7) $$ LANGUAGE SQL STABLE; + +COMMIT; -- 2.43.2