Hopeless holds interface could benefit from an index

[Galen Charlton]

The hopeless holds interface runs the "wide holds" query limited to holds whose "hopeless" date is in a user-specified range.

The query to fetch hopeless holds currently can be slow on large databases as there is currently no index on action.hold_request.hopeless_date. Since, relatively speaking, few holds become hopeless, such an index would be rather selective.

Empirical testing suggests that adding the index can decrease the query time from 3-5 seconds to less than a second, thereby making the interface more responsive. Furthermore, there is reason to believe that a fetch of all hopeless holds in a large consortium could occasionally use query plans that are so bad that many gigabytes of PostgreSQL temp files are created.

[Jason Stephenson (jstephenson)]

I've pushed a branch that adds the index: user/dyrcona/lp1972738-add-index

https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/dyrcona/lp1972738-add-index

[Galen Charlton (gmc)]

I'll test and merge. Noting that I intend to also toss in an ANALYZE on ahr in the update script.

[Galen Charlton (gmc)]

Pushed down to rel_3_9, along with the follow-up I mentioned. Thanks, Jason!