extend_reporter.full_circ_count view unusably slow on large datasets

Bug #1419172 reported by Chris Sharp on 2015-02-06
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Evergreen
Medium
Unassigned
2.7
Medium
Unassigned
2.8
Medium
Unassigned

Bug Description

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) wrote :

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

tags: added: pullrequest
Changed in evergreen:
milestone: none → 2.8.2
milestone: 2.8.2 → 2.next
Changed in evergreen:
milestone: 2.next → 2.9-alpha
status: New → Triaged
importance: Undecided → Medium
Changed in evergreen:
milestone: 2.9-alpha → 2.9-beta
Ben Shum (bshum) wrote :

Pushed to master, and backported to rel_2_8 and rel_2_7.

Changed in evergreen:
status: Triaged → Fix Committed
Changed in evergreen:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers