Re: Limiting overshoot in nbtree's parallel SAOP index scans

On Thu, 17 Oct 2024 at 00:33, Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
>
> On Wed, Oct 16, 2024 at 5:48 PM Matthias van de Meent
> <boekewurm(at)gmail(dot)com> wrote:
> > In v17 and the master branch you'll note 16 buffer hits for the test
> > query. However, when we use more expensive btree compare operations
> > (e.g. by adding pg_usleep(1) to both btint8cmp and btint4cmp), the
> > buffer access count starts to vary a lot and skyrockets to 30+ on my
> > machine, in some cases reaching >100 buffer hits. After applying my
> > patch, the buffer access count is capped to a much more agreeable
> > 16-18 hits - it still shows signs of overshooting the serial bounds,
> > but the number of buffers we overshoot our target is capped and thus
> > significantly lower.
>
> It's not exactly capped, though. Since in any case you're always prone
> to getting extra leaf page reads at the end of each primitive index
> scan. That's not something that's new to Postgres 17, though.

True, but the SAOP-enabled continued overshoot _is_ new: previously,
each backend would only get up to one additional buffer access for
every SOAP scan entry, while now it's only limited by outer SAOP
bounds and index size.

> Anyway, I'm still not convinced. Your test case requires adding a one
> second delay to each ORDER proc comparison,

microsecond. pg_usleep() takes microseconds as argument, and so
pg_usleep(1) should sleep for >=1 microsecond, but definitely much
less than 1 second.

> and so has an unrealistic adversarial character.

I don't think it's too adversarial to expect some compare operations
for a page to take one microsecond, especially when that compare
operation is related to SAOPs. I think complex comparators like those
for jsonb or collated text can certainly take much more CPU time than
your usual integer compare operation, which can definitely be long
enough for other workers to seize the scan. It's just that I didn't
want to spend a lot of time building a dataset that would expose the
issue when a single and obvious modification can do the same.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech)