LP#1419172 Optimize full_circ_count view to avoid seq scans user/csharp/lp1419172_optimize_full_circ_count_view_signoff
authorDan Wells <dbw2@calvin.edu>
Thu, 7 May 2015 20:43:16 +0000 (16:43 -0400)
committerChris Sharp <csharp@georgialibraries.org>
Fri, 8 May 2015 15:12:12 +0000 (11:12 -0400)
commit6b7bdaa308b4369964e00760dd5ae4cbef1b9ca6
tree9f6a9c59d642ef1ca2b57852b67496cea6b008c9
parentd5089b7b4065ed9cc51ffae441a22c99439a5da2
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>
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]