On 5/9/25 15:59, Peter Geoghegan wrote:
> On Fri, May 9, 2025 at 9:42 AM Peter Geoghegan <pg@bowt.ie> wrote:
>> I'm rather puzzled as to why this happens, then. I expect that nbtree
>> preprocessing will be able to use its usual single index column/index
>> key fast path here -- the "We can short-circuit most of the work if
>> there's just one key" path in _bt_preprocess_keys (and I expect that
>> _bt_num_array_keys() quickly determines that no skip arrays should be
>> added, preventing array preprocessing from ever really starting).
>
> You've been testing commit 92fe23d9 ("Add nbtree skip scan
> optimization") here, but I think you should test commit 8a510275
> ("Further optimize nbtree search scan key comparisons") instead. The
> former commit's commit message says that there big regressions, that
> the latter commit should go on to fix. Note that these two commits
> were pushed together, as a unit. All of my performance validation work
> was for the patch series as a whole, not for any individual commit.
>
> I don't actually think that this kind of scan would have been affected
> by those known regressions -- since they don't use array keys. But it
> is definitely true that the queries that you're looking at very much
> rely on the optimization from commit 8a510275 (or its predecessor
> optimization, the "pstate.prechecked" optimization). As I said, my
> performance validation didn't target individual commits.
>
I initially compared 17 to current master, but after discovering the
regression I bisected to the actual commit. That's how I ended up with
92fe23d93aa. This is how it looks for current master (bc35adee8d7):
mode #c 3ba2cdaa454 92fe23d93aa bc35adee8d7
-------------------------------------------------------------
simple 1 2617 1832 1899
4 8332 6260 6143
32 11603 7110 7193
-------------------------------------------------------------
prepared 1 11113 3646 3655
4 25379 11375 11342
32 37319 14097 13911
There's almost no difference between bc35adee8d7 and 92fe23d93aa.
regards
--
Tomas Vondra
