Hello, I am not up to date with the last version of patch but I did a regular benchmark with version 11 and typical issue we have at the moment and the result are still very very good. [1]
In term of performance improvement the last proposals could be a real game changer for 2 of our biggest databases. We hope that Postgres 17 will contain those improvements. Kind regards, Benoit [1] - https://gist.github.com/benoittgt/ab72dc4cfedea2a0c6a5ee809d16e04d?permalink_comment_id=4972955#gistcomment-4972955 __________ Benoit Tigeot Le jeu. 7 mars 2024 à 15:36, Peter Geoghegan <p...@bowt.ie> a écrit : > On Tue, Jan 23, 2024 at 3:22 PM Peter Geoghegan <p...@bowt.ie> wrote: > > I could include something less verbose, mentioning a theoretical risk > > to out-of-core amcanorder routines that coevolved with nbtree, > > inherited the same SAOP limitations, and then never got the same set > > of fixes. > > Attached is v11, which now says something like that in the commit > message. Other changes: > > * Fixed buggy sorting of arrays using cross-type ORDER procs, by > recognizing that we need to consistently use same-type ORDER procs for > sorting and merging the arrays during array preprocessing. > > Obviously, when we sort, we compare array elements to other array > elements (all of the same type). This is true independent of whether > the query itself happens to use a cross type operator/ORDER proc, so > we will need to do two separate ORDER proc lookups in cross-type > scenarios. > > * No longer subscript the ORDER proc used for array binary searches > using a scankey subscript. Now there is an additional indirection that > works even in the presence of multiple redundant scan keys that cannot > be detected as such due to a lack of appropriate cross-type support > within an opfamily. > > This was subtly buggy before now. Requires a little more coordination > between array preprocessing and standard/primitive index scan > preprocessing, which isn't ideal but seems unavoidable. > > * Lots of code polishing, especially within _bt_advance_array_keys(). > > While _bt_advance_array_keys() still works in pretty much exactly the > same way as it did back in v10, there are now better comments. > Including something about why its recursive call to itself is > guaranteed to use a low, fixed amount of stack space, verified using > an assertion. That addresses a concern held by Matthias. > > Outlook > ======= > > This patch is approaching being committable now. Current plan is to > commit this within the next few weeks. > > All that really remains now is to research how we might integrate this > work with the recently added continuescanPrechecked/haveFirstMatch > stuff from Alexander Korotkov, if at all. I've put that off until now > because it isn't particularly fundamental to what I'm doing here, and > seems optional. > > I would also like to do more performance validation. Things like the > parallel index scan code could stand to be revisited once again. Plus > I should think about the overhead of array preprocessing when > btrescan() is called many times, from a nested loop join -- I should > have something to say about that concern (raised by Heikki at one > point) before too long. > > -- > Peter Geoghegan >
