LP#1419172 Optimize full_circ_count view to avoid seq scans
authorDan Wells <dbw2@calvin.edu>
Thu, 7 May 2015 20:43:16 +0000 (16:43 -0400)
committerBen Shum <bshum@biblio.org>
Wed, 19 Aug 2015 03:05:39 +0000 (23:05 -0400)
commit54eb62a68268768bdb44b04459376f4c11c2381f
tree47de7f18437358ee78cd78fdf2a175a3a1cb2166
parentc22cd51ceae8683c31ad2fea7b2388a1c9262dfa
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 <dbw2@calvin.edu>
Signed-off-by: Chris Sharp <csharp@georgialibraries.org>
Signed-off-by: Ben Shum <bshum@biblio.org>
Open-ILS/src/sql/Pg/extend-reporter.sql
Open-ILS/src/sql/Pg/upgrade/XXXX.schema.optimize_full_circ_count.sql [new file with mode: 0644]