extend_reporter.full_circ_count view unusably slow on large datasets

[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.

Dan Wells provided me a fix that provides far better query plans on our system and the reports that were taking 2 hours now return in a matter of seconds: http://pastie.org/9893249.

We are currently running this fix in PINES production and are so far amazed at the difference.

Evergreen 2.7.2
OpenSRF 2.4
PostgreSQL 9.3
Ubuntu 14.04 (database)

[Dan Wells (dbw2)]

Chris and Jason B. reported earlier today to be running this new view for a few months now to good effect, so I have branchified it here:

working/user/dbwells/lp1419172_optimize_full_circ_count_view

http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/dbwells/lp1419172_optimize_full_circ_count_view

[Chris Sharp (chrissharp123)]

Thanks, Dan!

Signoff branch here: http://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/csharp/lp1419172_optimize_full_circ_count_view_signoff

[Ben Shum (bshum)]

Pushed to master, and backported to rel_2_8 and rel_2_7.