Re: Performance issues with parallelism and LIMIT

On 11/13/25 23:36, Tomas Vondra wrote:
> ...
>
> What I think we should do is much simpler - make the threshold in shm_mq
> dynamic, start with a very low value and gradually ramp up (up to 1/4).
> So we'd have
>
> if (mqh->mqh_consume_pending > threshold)
>
> We might start with
>
> threshold = (mq->mq_ring_size / 1024)
>
> or maybe some fixed value, list
>
> thredhold = 128
>
> And on every signal we'd double it, capping it to 1/4 of mq_ring_size.
>
> threshold = Min(threshold * 2, mq->mq_ring_size / 1024);
>
> This is very similar to other places doing this gradual ramp up, like in
> the prefetching / read_stream, etc. It allows fast termination for low
> LIMIT values, but quickly amortizes the cost for high LIMIT values.
>

I gave this a try today, to see if it can actually solve the regression.
Attached is a WIP patch, and a set of benchmarking scripts. On my ryzen
machine I got this (timings of the queries):

fill dataset | 14 15 16 17 18 patched
-----------------------------------------------------------------
10 random | 64.1 319.3 328.7 340.5 344.3 79.5
sequential | 54.6 323.4 347.5 350.5 399.2 78.3
100 random | 11.8 42.9 42.3 42.3 68.5 18.6
sequential | 10.0 44.3 45.0 44.3 60.6 20.0

Clearly 15 is a significant regression, with timings ~4x higher. And the
patch improves that quite a bit. It's not down all the way back to 14,
there's still ~10ms regression, for some reason.

Also, I didn't measure if this patch causes some other regressions for
other queries. I don't think it does, but maybe there's some weird
corner case affected.

regards

--
Tomas Vondra