Performance of Search on PostgreSQL Versions 12+

[Jason Stephenson]

Evergreen Version: Any (tested on Evergreen 3.10, but should affect all extant releases)
OpenSRF Version: N/A
Postgresql Version: 12+
Linux Version: N/A

Changes to allow inlining of common table expressions in PostgreSQL version 12 and later have a deleterious effect on Evergreen search: https://www.postgresql.org/docs/12/queries-with.html

This was first reported by Chris Sharp in IRC on Dec. 5, 2022: http://irc.evergreen-ils.org/evergreen/2022-12-05#i_517059

The issue was later confirmed and worked out to have begun with PostgreSQL version 12.

The cause, and a potential solution, were worked out by Mike Rylander and Jason Stephenson in IRC on Friday, Dec 9, 2022: http://irc.evergreen-ils.org/evergreen/2022-12-09#i_517366

The solution is to prevent two of the common table expressions ("with" queries) used by the Evergreen query parser from being inlined by the optimizer.

I'm setting this confirmed since it was reported by Chris Sharp and verified by myself and others.

[Jason Stephenson (jstephenson)]

I assigned this bug to myself while I am looking through the Evergreen code for other places that might require changes for Pg 12+. I believe that I know what to look for as regards the CTEs.

[Jason Stephenson (jstephenson)]

Checking with PostgreSQL 13, I did not find any other CTEs in the Evergreen code that look like they need modifications for Pg 12+. Most only use their CTEs once, and the one or two that don't effectively have only 1 row.

NB: I very well could have missed something.

[Jason Stephenson (jstephenson)]

I pushed a branch with changes made by Mike Rylander, and added a release note and my signoff.

As mentioned in comment #2 I have not found any other instances of CTEs that require addressing in PostgreSQL 12+.

collab/dyrcona/lp1999274-optimization-fence-for-pg-12-CTEs-in-search

https://git.evergreen-ils.org/?p=working/Evergreen.git;a=shortlog;h=refs/heads/collab/dyrcona/lp1999274-optimization-fence-for-pg-12-CTEs-in-search

[Jason Stephenson (jstephenson)]

I have rebased the branch on master as of commit 39c44d2c0a6f5eab4d93b564b079be1cf75d85f2 and force pushed.

I have tested this change with production data on Pg versions 10, 12, and 15 with no untoward effects. You really notice the improvement with Pg 12 and 15.

[Jane Sandberg (sandbergja)]

Merged to 3.10 and above. Thanks, Mike and Jason!