]> git.evergreen-ils.org Git - Evergreen.git/commit
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:07:38 +0000 (23:07 -0400)
commitde63ff6ad6960ae26348e4283dc83a89237e71b4
tree97b2dd858e4531ff053b54bd858743cf9f12a87f
parent2fd6c8239bacffdd2d57f64972c86f273b00fbb9
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]