LP#1234845: Performance improvement to evergreen.ranked_volumes() database function.
[working/Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / XXXX.function.ranked_volumes.sql
1 BEGIN;
2
3 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes(
4     bibid BIGINT[],
5     ouid INT,
6     depth INT DEFAULT NULL,
7     slimit HSTORE DEFAULT NULL,
8     soffset HSTORE DEFAULT NULL,
9     pref_lib INT DEFAULT NULL,
10     includes TEXT[] DEFAULT NULL::TEXT[]
11 ) RETURNS TABLE(id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT) AS $$
12     WITH RECURSIVE ou_depth AS (
13         SELECT COALESCE(
14             $3,
15             (
16                 SELECT depth
17                 FROM actor.org_unit_type aout
18                     INNER JOIN actor.org_unit ou ON ou_type = aout.id
19                 WHERE ou.id = $2
20             )
21         ) AS depth
22     ), descendant_depth AS (
23         SELECT  ou.id,
24                 ou.parent_ou,
25                 out.depth
26         FROM  actor.org_unit ou
27                 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
28                 JOIN anscestor_depth ad ON (ad.id = ou.id),
29                 ou_depth
30         WHERE ad.depth = ou_depth.depth
31             UNION ALL
32         SELECT  ou.id,
33                 ou.parent_ou,
34                 out.depth
35         FROM  actor.org_unit ou
36                 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
37                 JOIN descendant_depth ot ON (ot.id = ou.parent_ou)
38     ), anscestor_depth AS (
39         SELECT  ou.id,
40                 ou.parent_ou,
41                 out.depth
42         FROM  actor.org_unit ou
43                 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
44         WHERE ou.id = $2
45             UNION ALL
46         SELECT  ou.id,
47                 ou.parent_ou,
48                 out.depth
49         FROM  actor.org_unit ou
50                 JOIN actor.org_unit_type out ON (out.id = ou.ou_type)
51                 JOIN anscestor_depth ot ON (ot.parent_ou = ou.id)
52     ), descendants as (
53         SELECT ou.* FROM actor.org_unit ou JOIN descendant_depth USING (id)
54     )
55
56     SELECT ua.id, ua.name, ua.label_sortkey, MIN(ua.rank) AS rank FROM (
57         SELECT acn.id, aou.name, acn.label_sortkey,
58             RANK() OVER w
59         FROM asset.call_number acn
60             JOIN asset.copy acp ON (acn.id = acp.call_number)
61             JOIN descendants AS aou ON (acp.circ_lib = aou.id)
62         WHERE acn.record = ANY ($1)
63             AND acn.deleted IS FALSE
64             AND acp.deleted IS FALSE
65             AND CASE WHEN ('exclude_invisible_acn' = ANY($7)) THEN
66                 EXISTS (
67                     SELECT 1
68                     FROM asset.opac_visible_copies
69                     WHERE copy_id = acp.id AND record = acn.record
70                 ) ELSE TRUE END
71         GROUP BY acn.id, acp.status, aou.name, acn.label_sortkey, aou.id
72         WINDOW w AS (
73             ORDER BY
74                 COALESCE(
75                     CASE WHEN aou.id = $2 THEN -20000 END,
76                     CASE WHEN aou.id = $6 THEN -10000 END,
77                     (SELECT distance - 5000
78                         FROM actor.org_unit_descendants_distance($6) as x
79                         WHERE x.id = aou.id AND $6 IN (
80                             SELECT q.id FROM actor.org_unit_descendants($2) as q)),
81                     (SELECT e.distance FROM actor.org_unit_descendants_distance($2) as e WHERE e.id = aou.id),
82                     1000
83                 ),
84                 evergreen.rank_cp_status(acp.status)
85         )
86     ) AS ua
87     GROUP BY ua.id, ua.name, ua.label_sortkey
88     ORDER BY rank, ua.name, ua.label_sortkey
89     LIMIT ($4 -> 'acn')::INT
90     OFFSET ($5 -> 'acn')::INT;
91 $$ LANGUAGE SQL STABLE ROWS 10;
92
93 CREATE OR REPLACE FUNCTION evergreen.ranked_volumes
94     ( 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[] )
95     RETURNS TABLE (id BIGINT, name TEXT, label_sortkey TEXT, rank BIGINT)
96     AS $$ SELECT * FROM evergreen.ranked_volumes(ARRAY[$1],$2,$3,$4,$5,$6,$7) $$ LANGUAGE SQL STABLE;
97
98 COMMIT;