From 3e912c3a1868e8036e36703cdc0337cf399826be Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Tue, 29 Mar 2016 11:03:33 -0400 Subject: [PATCH] LP#1549505: optimize population filtering for copy-oriented badges Signed-off-by: Mike Rylander --- .../Pg/upgrade/XXXX.schema.statisitcal-ratings.sql | 97 ++++++++++++---------- 1 file changed, 51 insertions(+), 46 deletions(-) diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.statisitcal-ratings.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.statisitcal-ratings.sql index df5c8a3cbc..2839b45775 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.statisitcal-ratings.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.statisitcal-ratings.sql @@ -404,7 +404,7 @@ END; $f$ LANGUAGE PLPGSQL; -CREATE OR REPLACE FUNCTION rating.recalculate_badge_score ( badge_id INT ) RETURNS VOID AS $f$ +CREATE OR REPLACE FUNCTION rating.recalculate_badge_score ( badge_id INT, setup_only BOOL DEFAULT FALSE ) RETURNS VOID AS $f$ DECLARE badge_row rating.badge%ROWTYPE; param rating.popularity_parameter%ROWTYPE; @@ -445,6 +445,10 @@ BEGIN CREATE INDEX precalc_filter_bib_list_idx ON precalc_filter_bib_list (id); + IF setup_only THEN + RETURN; + END IF; + -- If it's a fixed-rating badge, just do it ... IF badge_row.fixed_rating IS NOT NULL THEN DELETE FROM rating.record_badge_score WHERE badge = badge_id; @@ -625,21 +629,21 @@ BEGIN PERFORM rating.precalc_bibs_by_copy(badge_id); - SET LOCAL client_min_messages = error; - DROP TABLE IF EXISTS precalc_bib_list; - CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS + DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN ( SELECT id FROM precalc_filter_bib_list INTERSECT - SELECT id FROM precalc_bibs_by_copy_list; + SELECT id FROM precalc_bibs_by_copy_list + ); + + ANALYZE precalc_copy_filter_bib_list; RETURN QUERY - SELECT f.id AS bib, + SELECT rhrr.bib_record AS bib, COUNT(DISTINCT h.id)::NUMERIC AS holds FROM action.hold_request h JOIN reporter.hold_request_record rhrr ON (rhrr.id = h.id) - JOIN precalc_bib_list f ON (f.id = rhrr.bib_record) - JOIN precalc_copy_filter_bib_list cf ON (f.id = cf.id) - JOIN action.hold_copy_map m ON (m.target_copy = cf.copy AND m.hold = h.id) + JOIN action.hold_copy_map m ON (m.hold = h.id) + JOIN precalc_copy_filter_bib_list cf ON (rhrr.bib_record = cf.id AND m.target_copy = cf.copy) WHERE h.fulfillment_time IS NULL AND h.request_lib = ANY (badge.orgs) GROUP BY 1; @@ -665,12 +669,13 @@ BEGIN PERFORM rating.precalc_bibs_by_copy(badge_id); - SET LOCAL client_min_messages = error; - DROP TABLE IF EXISTS precalc_bib_list; - CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS + DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN ( SELECT id FROM precalc_filter_bib_list INTERSECT - SELECT id FROM precalc_bibs_by_copy_list; + SELECT id FROM precalc_bibs_by_copy_list + ); + + ANALYZE precalc_copy_filter_bib_list; iint := EXTRACT(EPOCH FROM badge.importance_interval); IF badge.importance_age IS NOT NULL THEN @@ -688,10 +693,9 @@ BEGIN (1 + EXTRACT(EPOCH FROM AGE(c.xact_start)) / iint)::INT AS cage, COUNT(c.id)::INT AS circs FROM action.circulation c + JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy) JOIN asset.copy cp ON (cp.id = c.target_copy) JOIN asset.call_number cn ON (cn.id = cp.call_number) - JOIN precalc_bib_list f ON (f.id = cn.record) - JOIN precalc_copy_filter_bib_list cf ON (f.id = cf.id AND cp.id = cf.copy) WHERE c.xact_start >= NOW() - badge.horizon_age AND cn.owning_lib = ANY (badge.orgs) AND c.phone_renewal IS FALSE -- we don't count renewals @@ -713,24 +717,25 @@ BEGIN PERFORM rating.precalc_bibs_by_copy(badge_id); - SET LOCAL client_min_messages = error; - DROP TABLE IF EXISTS precalc_bib_list; - CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS + DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN ( SELECT id FROM precalc_filter_bib_list INTERSECT - SELECT id FROM precalc_bibs_by_copy_list; + SELECT id FROM precalc_bibs_by_copy_list + ); + + ANALYZE precalc_copy_filter_bib_list; RETURN QUERY - SELECT f.id AS bib, + SELECT cn.record AS bib, COUNT(c.id)::NUMERIC AS circs FROM action.circulation c + JOIN precalc_copy_filter_bib_list cf ON (c.target_copy = cf.copy) JOIN asset.copy cp ON (cp.id = c.target_copy) JOIN asset.call_number cn ON (cn.id = cp.call_number) - JOIN precalc_bib_list f ON (f.id = cn.record) - JOIN precalc_copy_filter_bib_list cf ON (f.id = cf.id AND cp.id = cf.copy) WHERE c.checkin_time IS NULL AND cn.owning_lib = ANY (badge.orgs) GROUP BY 1; + END; $f$ LANGUAGE PLPGSQL STRICT; @@ -744,12 +749,13 @@ BEGIN PERFORM rating.precalc_bibs_by_copy(badge_id); - SET LOCAL client_min_messages = error; - DROP TABLE IF EXISTS precalc_bib_list; - CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS + DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN ( SELECT id FROM precalc_filter_bib_list INTERSECT - SELECT id FROM precalc_bibs_by_copy_list; + SELECT id FROM precalc_bibs_by_copy_list + ); + + ANALYZE precalc_copy_filter_bib_list; RETURN QUERY SELECT bib, @@ -758,9 +764,8 @@ BEGIN (cp.status = 1)::INT AS checked_out, 1 AS total FROM asset.copy cp + JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy) JOIN asset.call_number cn ON (cn.id = cp.call_number) - JOIN precalc_bib_list f ON (f.id = cn.record) - JOIN precalc_copy_filter_bib_list c ON (f.id = c.id AND cp.id = c.copy) WHERE cn.owning_lib = ANY (badge.orgs) ) x GROUP BY 1; @@ -777,20 +782,20 @@ BEGIN PERFORM rating.precalc_bibs_by_copy(badge_id); - SET LOCAL client_min_messages = error; - DROP TABLE IF EXISTS precalc_bib_list; - CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS + DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN ( SELECT id FROM precalc_filter_bib_list INTERSECT - SELECT id FROM precalc_bibs_by_copy_list; + SELECT id FROM precalc_bibs_by_copy_list + ); + + ANALYZE precalc_copy_filter_bib_list; RETURN QUERY SELECT cn.record AS bib, COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC FROM asset.copy cp + JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy) JOIN asset.call_number cn ON (cn.id = cp.call_number) - JOIN precalc_bib_list f ON (f.id = cn.record) - JOIN precalc_copy_filter_bib_list c ON (f.id = c.id AND cp.id = c.copy) JOIN action.hold_copy_map m ON (m.target_copy = cp.id) WHERE cn.owning_lib = ANY (badge.orgs) GROUP BY 1; @@ -807,22 +812,22 @@ BEGIN PERFORM rating.precalc_bibs_by_copy(badge_id); - SET LOCAL client_min_messages = error; - DROP TABLE IF EXISTS precalc_bib_list; - CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS + DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN ( SELECT id FROM precalc_filter_bib_list INTERSECT - SELECT id FROM precalc_bibs_by_copy_list; + SELECT id FROM precalc_bibs_by_copy_list + ); + + ANALYZE precalc_copy_filter_bib_list; RETURN QUERY SELECT cn.record AS bib, COUNT(DISTINCT m.hold)::NUMERIC / COUNT(DISTINCT cp.id)::NUMERIC FROM asset.copy cp + JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy) JOIN asset.copy_location cl ON (cl.id = cp.location) JOIN config.copy_status cs ON (cs.id = cp.status) JOIN asset.call_number cn ON (cn.id = cp.call_number) - JOIN precalc_bib_list f ON (f.id = cn.record) - JOIN precalc_copy_filter_bib_list c ON (f.id = c.id AND cp.id = c.copy) JOIN action.hold_copy_map m ON (m.target_copy = cp.id) WHERE cn.owning_lib = ANY (badge.orgs) AND cp.holdable IS TRUE @@ -896,12 +901,13 @@ BEGIN PERFORM rating.precalc_bibs_by_copy(badge_id); - SET LOCAL client_min_messages = error; - DROP TABLE IF EXISTS precalc_bib_list; - CREATE TEMP TABLE precalc_bib_list ON COMMIT DROP AS + DELETE FROM precalc_copy_filter_bib_list WHERE id NOT IN ( SELECT id FROM precalc_filter_bib_list INTERSECT - SELECT id FROM precalc_bibs_by_copy_list; + SELECT id FROM precalc_bibs_by_copy_list + ); + + ANALYZE precalc_copy_filter_bib_list; RETURN QUERY SELECT bib, @@ -919,9 +925,8 @@ BEGIN ) )::NUMERIC AS circ_time FROM asset.copy cp + JOIN precalc_copy_filter_bib_list c ON (cp.id = c.copy) JOIN asset.call_number cn ON (cn.id = cp.call_number) - JOIN precalc_bib_list f ON (f.id = cn.record) - JOIN precalc_copy_filter_bib_list c ON (f.id = c.id AND cp.id = c.copy) LEFT JOIN action.all_circulation circ ON ( circ.target_copy = cp.id AND stop_fines NOT IN ( -- 2.11.0