public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexander Lakhin <[email protected]>
To: Thomas Munro <[email protected]>
Cc: Andres Freund <[email protected]>
Cc: Melanie Plageman <[email protected]>
Cc: David Rowley <[email protected]>
Cc: Heikki Linnakangas <[email protected]>
Cc: Pg Hackers <[email protected]>
Subject: Re: Streaming read-ready sequential scan code
Date: Sat, 18 May 2024 22:00:00 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+hUKGKpw8KBL_V4yhCMAbi6jf5rLyFz-K2MrwuDTHTcUytSVw@mail.gmail.com>
References: <CA+hUKG+wWKJ=mEXj0Xg06zQop6foV6dsirFYf2o7wMzbn_OKeA@mail.gmail.com>
<CA+hUKG+q=sg+ifx5NRfBdX_EBAeoJC1fSuaYeQXLCYbN-7nKig@mail.gmail.com>
<CA+hUKGKXZALJ=6aArUsXRJzBm=qvc4AWp7=iJNXJQqpbRLnD_w@mail.gmail.com>
<CAAKRu_Z4hjfHvqomhjr0McYPYbtw7iW-5-gAyFCbLOfzTCOrbQ@mail.gmail.com>
<CA+hUKGLT9s9KzU_EbYYOwKybP2YNLeoSLQZFWao7ut-Pi=CKjA@mail.gmail.com>
<CAAKRu_b1BfikoH8GGGubDnY4-eXZE4PoVEkxwppMTmiW2_NrWQ@mail.gmail.com>
<CA+hUKG+h8Whpv0YsJqjMVkjYX+80fTVc6oi-V+zxJvykLpLHYQ@mail.gmail.com>
<CAAKRu_aU60EFTLnB86FY9NUT1KqjzjLC2u+DqBtBLWi_ZooFpQ@mail.gmail.com>
<CA+hUKGJDdbmJGCm6CsnkuRKe24jGE1L22eBS3eAG=POXM7cxxA@mail.gmail.com>
<[email protected]>
<[email protected]>
<CA+hUKGLHNNMbGntNyNGpVq+bA=ann=zC7Y+9a_P99dGmgXS9VQ@mail.gmail.com>
<CA+hUKG+D8n8_YhezY6VEKcy3PjZ-=G0oNfye3HWFBbdKDoz=Ww@mail.gmail.com>
<CA+hUKGKpw8KBL_V4yhCMAbi6jf5rLyFz-K2MrwuDTHTcUytSVw@mail.gmail.com>
Hello Thomas,
18.05.2024 07:47, Thomas Munro wrote:
> 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.
Thank you for the information!
Unfortunately, I can't see significant differences in my environment with
parallel_leader_participation=off.
With blocknums[1], timing is changed, but the effect is not persistent.
10 query15 executions in a row, b7b0f3f27:
277.932 ms
281.805 ms
278.335 ms
281.565 ms
284.167 ms
283.171 ms
281.165 ms
281.615 ms
285.394 ms
277.301 ms
b7b0f3f27~1:
159.789 ms
165.407 ms
160.893 ms
159.343 ms
160.936 ms
161.577 ms
161.637 ms
163.421 ms
163.143 ms
167.109 ms
b7b0f3f27 + blocknums[1]:
164.133 ms
280.920 ms
160.748 ms
163.182 ms
161.709 ms
161.998 ms
161.239 ms
276.256 ms
161.601 ms
160.384 ms
I placed PGDATA on tmpfs to rule out any blockdev specifics (increasing
blockdev ra from 256 to 4096 didn't help me with PGDATA on NVME either.)
Best regards,
Alexander
view thread (14+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Streaming read-ready sequential scan code
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox