Message-ID: <7e090ca5-1d3d-b4f3-9060-8ec88f444c6c@gmail.com>
Date: Tue, 27 Aug 2024 16:00:00 +0300
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.4.2
Subject: Re: Streaming read-ready sequential scan code
Content-Language: en-US
To: Thomas Munro <thomas.munro@gmail.com>,
 Melanie Plageman <melanieplageman@gmail.com>
Cc: Andres Freund <andres@anarazel.de>, David Rowley <dgrowleyml@gmail.com>,
 Heikki Linnakangas <hlinnaka@iki.fi>,
 Pg Hackers <pgsql-hackers@postgresql.org>
References: 
 <CA+hUKG+wWKJ=mEXj0Xg06zQop6foV6dsirFYf2o7wMzbn_OKeA@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>
 <20240407221229.upgvhyf2gzflcawi@awork3.anarazel.de>
 <63a63690-dd92-c809-0b47-af05459e95d1@gmail.com>
 <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>
 <CAAKRu_b=foYLPVvxL0YHb5ZSGvasvK2EH1ia9Eg8knk_cVgAtQ@mail.gmail.com>
 <CA+hUKGLdvzXDMiLeyBd6u25AwNpPFdWTgER2-_JMU6QPNnxjrw@mail.gmail.com>
 <CA+hUKGKHpN7Gg7x_0ALz1e2ZguS1CjrPioDLB0CHjjv7zpiWeg@mail.gmail.com>
From: Alexander Lakhin <exclusion@gmail.com>
In-Reply-To: 
 <CA+hUKGKHpN7Gg7x_0ALz1e2ZguS1CjrPioDLB0CHjjv7zpiWeg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/7e090ca5-1d3d-b4f3-9060-8ec88f444c6c%40gmail.com>
Precedence: bulk

Hello Thomas,

27.08.2024 09:52, Thomas Munro wrote:
> Here's a really simple way to see the new unfairness at the end of a
> parallel scan:
>
> drop table if exists t;
> create table t (i int);
> insert into t select generate_series(1, 100000);
> alter table t set (parallel_workers = 2);
> set parallel_setup_cost = 0;
> set parallel_leader_participation = off;
> explain (analyze, buffers, verbose) select count(*) from t;
>
> On my machine, unpatched master shows:
>
>                       Worker 0:  actual time=0.036..12.452 rows=51076 loops=1
>                         Buffers: shared hit=226
>                       Worker 1:  actual time=0.037..12.003 rows=48924 loops=1
>                         Buffers: shared hit=217
>
> The attached patch, which I'd like to push, is effectively what
> Alexander tested (blocknums[16] -> blocknums[1]).  There's no point in
> using an array of size 1, so I've turned it into a simple variable and
> deleted the relevant comments.  My machine shows:
>
>                       Worker 0:  actual time=0.038..12.115 rows=49946 loops=1
>                         Buffers: shared hit=221
>                       Worker 1:  actual time=0.038..12.109 rows=50054 loops=1
>                         Buffers: shared hit=222
>
> That difference may not seem huge, but other pre-existing things are
> going pathologically wrong in the reported query that magnify it (see
> my earlier analysis).  It's an interesting problem that will require
> more study (my earlier analysis missed a detail that I'll write about
> separately), but it doesn't seem to be new or have easy fixes, so that
> will have to be for later work.

I've tried your query and could not get sustainable results, unfortunately.
The following script:
rm -rf "$PGDATA"; initdb -D "$PGDATA" >initdb.log 2>&1

pg_ctl -s -l server.log start

cat << EOF | psql | grep 'Parallel Seq Scan' -A10 | grep 'Worker ' -A1
create table t (i int);
insert into t select generate_series(1, 100000);
alter table t set (parallel_workers = 2);
set parallel_setup_cost = 0;
set parallel_leader_participation = off;
explain (analyze, buffers, verbose) select count(*) from t;
EOF

pg_ctl -s stop

gives me unstable numbers on unpatched master:
                      Worker 0:  actual time=0.024..5.814 rows=51076 loops=1
                        Buffers: shared hit=226
                      Worker 1:  actual time=0.023..5.614 rows=48924 loops=1
                        Buffers: shared hit=217
---
                      Worker 0:  actual time=0.027..5.130 rows=36612 loops=1
                        Buffers: shared hit=162
                      Worker 1:  actual time=0.013..5.605 rows=63388 loops=1
                        Buffers: shared hit=281
---
                      Worker 0:  actual time=0.025..5.447 rows=47460 loops=1
                        Buffers: shared hit=210
                      Worker 1:  actual time=0.019..5.688 rows=52540 loops=1
                        Buffers: shared hit=233

and also with the patch applied:
                      Worker 0:  actual time=0.012..4.486 rows=55478 loops=1
                        Buffers: shared hit=246
                      Worker 1:  actual time=0.014..4.430 rows=44522 loops=1
                        Buffers: shared hit=197
---
                      Worker 0:  actual time=0.013..4.269 rows=55822 loops=1
                        Buffers: shared hit=247
                      Worker 1:  actual time=0.017..4.238 rows=44178 loops=1
                        Buffers: shared hit=196
---
                      Worker 0:  actual time=0.014..4.974 rows=50624 loops=1
                        Buffers: shared hit=224
                      Worker 1:  actual time=0.016..4.932 rows=49376 loops=1
                        Buffers: shared hit=219
---
                      Worker 0:  actual time=0.012..5.459 rows=65648 loops=1
                        Buffers: shared hit=291
                      Worker 1:  actual time=0.022..5.109 rows=34352 loops=1
                        Buffers: shared hit=152

Please correct me, if I'm doing something wrong.

Best regards,
Alexander