Re: Adding skip scan (including MDAM style range skip scan) to nbtree

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