From 54eb62a68268768bdb44b04459376f4c11c2381f Mon Sep 17 00:00:00 2001 From: Dan Wells Date: Thu, 7 May 2015 16:43:16 -0400 Subject: [PATCH] LP#1419172 Optimize full_circ_count view to avoid seq scans As reported by Chris Sharp: "The reporter.classic_item_list view was modified in bug 1208572 to use extend_reporter.full_circ_count to provide the use count for each item. Unfortunately, this change was found to be the cause of consistently long-running (2+ hours) reports queries in PINES as its query plan on our PostgreSQL 9.3 server was resulting in sequential scans of both the circulation and aged_circulation tables." This commit restructures the view to avoid JOINs, and is in production at PINES with noted improvement. Signed-off-by: Dan Wells Signed-off-by: Chris Sharp Signed-off-by: Ben Shum --- Open-ILS/src/sql/Pg/extend-reporter.sql | 11 +++++------ .../upgrade/XXXX.schema.optimize_full_circ_count.sql | 12 ++++++++++++ 2 files changed, 17 insertions(+), 6 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.optimize_full_circ_count.sql diff --git a/Open-ILS/src/sql/Pg/extend-reporter.sql b/Open-ILS/src/sql/Pg/extend-reporter.sql index cb7eec54a9..852b537e4b 100644 --- a/Open-ILS/src/sql/Pg/extend-reporter.sql +++ b/Open-ILS/src/sql/Pg/extend-reporter.sql @@ -26,12 +26,11 @@ CREATE TABLE extend_reporter.legacy_circ_count ( ); CREATE OR REPLACE VIEW extend_reporter.full_circ_count AS - SELECT cp.id, COALESCE(c.circ_count, 0::bigint) + COALESCE(count(DISTINCT circ.id), 0::bigint) + COALESCE(count(DISTINCT acirc.id), 0::bigint) AS circ_count - FROM asset."copy" cp - LEFT JOIN extend_reporter.legacy_circ_count c USING (id) - LEFT JOIN "action".circulation circ ON circ.target_copy = cp.id - LEFT JOIN "action".aged_circulation acirc ON acirc.target_copy = cp.id - GROUP BY cp.id, c.circ_count; + SELECT cp.id, + COALESCE((SELECT circ_count FROM extend_reporter.legacy_circ_count WHERE id = cp.id), 0) + + (SELECT COUNT(*) FROM action.circulation WHERE target_copy = cp.id) + + (SELECT COUNT(*) FROM action.aged_circulation WHERE target_copy = cp.id) AS circ_count + FROM asset.copy cp; CREATE OR REPLACE VIEW extend_reporter.global_bibs_by_holding_update AS SELECT DISTINCT ON (id) id, holding_update, update_type diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.optimize_full_circ_count.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.optimize_full_circ_count.sql new file mode 100644 index 0000000000..6440dfad37 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.optimize_full_circ_count.sql @@ -0,0 +1,12 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE OR REPLACE VIEW extend_reporter.full_circ_count AS + SELECT cp.id, + COALESCE((SELECT circ_count FROM extend_reporter.legacy_circ_count WHERE id = cp.id), 0) + + (SELECT COUNT(*) FROM action.circulation WHERE target_copy = cp.id) + + (SELECT COUNT(*) FROM action.aged_circulation WHERE target_copy = cp.id) AS circ_count + FROM asset.copy cp; + +COMMIT; -- 2.43.2