Followup to bug 1827250, hold shelf issues

[Mike Rylander]

Evergreen version: 3.3+
Postgres version: 9.6

In bug 1827250 I suggested Jason Stephenson look at an alternate SQL formulation to solve the original problem. He did, and deemed it faster, so went with that. Unfortunately, on PG 9.6, we're seeing some data sets that decide on a /very/ poor plan for the wide-hold query with the solution as implemented, but the original suggestion from Jason works fine.

In the face of evidence controverting my thought that giving PG more options for planning is better in this case, I suggest we move to his original.

Branch coming soon.

[Mike Rylander (mrylander)]

Branch available here:

https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/miker/lp-1855329-hold_shelf_speed

[Jason Stephenson (jstephenson)]

While I see similar numbers on Pg 9.6 that I saw on Pg 9.5 with both versions of this fix, the numbers are so close that the difference is negligible. What I see is that some parts of the NOT NULL version are faster than the NULLS LAST version, but the planning step seems to be longer on the NOT NULL version. Again, the difference that I see is about 1ms on average.

I've added my signoff to the branch below. I'm not certain if we want to add release notes for this change or not. Another pair eyes would be useful, particularly from a site that sees a larger difference in the numbers.

https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/user/dyrcona/lp-1855329-hold_shelf_speed-signoff

[Galen Charlton (gmc)]

Pushed to master, rel_3_4, and rel_3_3. Thanks, Mike and Jason!

This has been in production for us since 12/5. For most of our customers, there's no significant difference in query performance, but for at least one of them, it makes a huge difference.

[Galen Charlton (gmc)]

In production since 12/18, rather.