Horton

Re: Performance issues with parallelism and LIMIT

Tomas Vondra <tomas@vondra.me>

View thread
From: Tomas Vondra <tomas@vondra.me>
To: David Geier <geidav.pg@gmail.com>, Tomas Vondra <tomas.vondra@enterprisedb.com>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: Robert Haas <robertmhaas@gmail.com>, dilipbalaut@gmail.com
Date: 2025-11-14T16:00:08Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. shm_mq: Update mq_bytes_written less often.

Attachments

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