Re: BitmapHeapScan streaming read user and prelim refactoring

On Fri, Feb 14, 2025 at 9:32 AM Andres Freund <andres(at)anarazel(dot)de> wrote:
> I think we'll need to add some logic in read stream that only disables advice
> after a longer sequential sequence. Writing logic for that shouldn't be too
> hard, I think? Determining the concrete cutoffs is probably harder, although I
> think even fairly simplistic logic will be "good enough".

(Sorry for taking time to respond, I had to try some bogus things
first before I hit on a principled answer.)

Yeah, it is far too stupid. I think I have figured out the ideal
cutoff: just keep issuing advice for a given sequential run of blocks
until the pread() end of the stream catches up with the *start* of it,
if ever (ie until the kernel sees the actual sequential reads). That
turned out to require only a small tweak and one new variable. It
avoids those stalls on reads of sequential clusters >
io_combine_limit, with no change in behaviour for pure sequential
streams and random streams containing sequential clusters <=
io_combine_limit that I'd previously been fixating on. I've added a
patch for that to the v2 of my read stream improvements series[1] for
experimentation... will post shortly.

I also see a couple of other reasons why streaming BHS can be less
aggressive with I/O than master: the silly arbitrary buffer queue cap,
there's already a patch in the series for that but I have a slightly
better one now and plan to commit it today, and silly heuristics for
look-ahead distance reduction also related to sequential detection,
which I'll explain with a patch on the other thread. All of these are
cases where I was basically a bit too chicken to open the throttle all
the way in early versions. Will post those at [1] too after lunch...
more soon...

[1] https://www.postgresql.org/message-id/flat/CA%2BhUKGK_%3D4CVmMHvsHjOVrK6t4F%3DLBpFzsrr3R%2BaJYN8kcTfWg%40mail.gmail.com