public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexander Lakhin <[email protected]>
To: Thomas Munro <[email protected]>
To: Melanie Plageman <[email protected]>
Cc: Andres Freund <[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: Tue, 27 Aug 2024 16:00:00 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CA+hUKGKHpN7Gg7x_0ALz1e2ZguS1CjrPioDLB0CHjjv7zpiWeg@mail.gmail.com>
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>
<[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>
<CAAKRu_b=foYLPVvxL0YHb5ZSGvasvK2EH1ia9Eg8knk_cVgAtQ@mail.gmail.com>
<CA+hUKGLdvzXDMiLeyBd6u25AwNpPFdWTgER2-_JMU6QPNnxjrw@mail.gmail.com>
<CA+hUKGKHpN7Gg7x_0ALz1e2ZguS1CjrPioDLB0CHjjv7zpiWeg@mail.gmail.com>
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
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