Re: Streaming read-ready sequential scan code

On Sat, May 18, 2024 at 11:30 AM Thomas Munro <thomas(dot)munro(at)gmail(dot)com> wrote:
> Andres happened to have TPC-DS handy, and reproduced that regression
> in q15. We tried some stuff and figured out that it requires
> parallel_leader_participation=on, ie that this looks like some kind of
> parallel fairness and/or timing problem. It seems to be a question of
> which worker finishes up processing matching rows, and the leader gets
> a ~10ms head start but may be a little more greedy with the new
> streaming code. He tried reordering the table contents and then saw
> 17 beat 16. So for q15, initial indications are that this isn't a
> fundamental regression, it's just a test that is sensitive to some
> arbitrary conditions.
>
> I'll try to figure out some more details about that, ie is it being
> too greedy on small-ish tables,

After more debugging, we learned a lot more things...

1. That query produces spectacularly bad estimates, so we finish up
having to increase the number of buckets in a parallel hash join many
times. That is quite interesting, but unrelated to new code.
2. Parallel hash join is quite slow at negotiating an increase in the
number of hash bucket, if all of the input tuples are being filtered
out by quals, because of the choice of where workers check for
PHJ_GROWTH_NEED_MORE_BUCKETS. That could be improved quite easily I
think. I have put that on my todo list 'cause that's also my code,
but it's not a new issue it's just one that is now highlighted...
3. This bit of read_stream.c is exacerbating unfairness in the
underlying scan, so that 1 and 2 come together and produce a nasty
slowdown, which goes away if you change it like so:

- BlockNumber blocknums[16];
+ BlockNumber blocknums[1];

I will follow up after some more study.