public inbox for [email protected]
help / color / mirror / Atom feedFrom: David Rowley <[email protected]>
To: Andy Fan <[email protected]>
Cc: Thomas Munro <[email protected]>
Cc: Andy Fan <[email protected]>
Cc: pgsql-hackers <[email protected]>
Cc: Zhang Mingli <[email protected]>
Cc: [email protected]
Subject: Re: make add_paths_to_append_rel aware of startup cost
Date: Fri, 16 Feb 2024 15:03:21 +1300
Message-ID: <CAApHDvpUjAdvwOoARj9ddsx=-DM9sF_Sh06-pHLx73at5q=TJw@mail.gmail.com> (raw)
In-Reply-To: <CAApHDvogv5MBw6JV82Yb6gAa0mp64HQtX--TSK_wZOn9x2fP4Q@mail.gmail.com>
References: <CAKU4AWrXSkUV=Pt-gRxQT7EbfUeNssprGyNsB=5mJibFZ6S3ww@mail.gmail.com>
<CAApHDvry0nSV62kAOH3iccvfPhGPLN0Q97+=b1RsDPXDz3=CiQ@mail.gmail.com>
<CAKU4AWoksFSs_6K5ZCzz68Oq+3uZb5g6sb8-dXJ6cChgYQm-yw@mail.gmail.com>
<CAApHDvoMGyc+1eb8g5rEMUUMeGWhe2c_f8yvJjUO1kUHZj0h7w@mail.gmail.com>
<CAKU4AWp9aTafjWwCqOauC7jmbuX+tGEWkRrBwXYhW-CFQXmRig@mail.gmail.com>
<CAKU4AWqaTNPrYcb_cMEDDYWZVGfFxuUjr75F5LBZwnUQ0JvVPw@mail.gmail.com>
<CAApHDvqf+YNFr_GbU4aJ5G7ZGJoZJ-R6CcFNaVi3QRpcgnNyAA@mail.gmail.com>
<CAKU4AWqC5dR6DJ+XYiE+W4YiaokbE-myVgdnvqX3wx_Z87hk+w@mail.gmail.com>
<CAApHDvrgSM5mdix-2YLYMJVaz+-vEq5mBPQ4jbDJYpy3=rc7dQ@mail.gmail.com>
<CAKU4AWo0F3vMZp0WMuN9-WtZpA0CCmd+gKqdaix_TqgXYkSwWg@mail.gmail.com>
<CAApHDvrf54zt5Y52nQp99nOYcXQU92=s2_9szwH8A17wCJBg=w@mail.gmail.com>
<CA+hUKGLqC-NobKYfjxNM3Gexv9OJ-Fhvy9bugUcXsZjTqH7W=Q@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAApHDvogv5MBw6JV82Yb6gAa0mp64HQtX--TSK_wZOn9x2fP4Q@mail.gmail.com>
On Fri, 16 Feb 2024 at 01:09, David Rowley <[email protected]> wrote:
>
> On Thu, 15 Feb 2024 at 21:42, Andy Fan <[email protected]> wrote:
> > I found the both plans have the same cost, I can't get the accurate
> > cause of this after some hours research, but it is pretty similar with
> > 7516056c584e3, so I uses a similar strategy to stable it. is it
> > acceptable?
>
> It's pretty hard to say. I can only guess why this test would be
> flapping like this. I see it's happened before on mylodon, so probably
> not a cosmic ray. It's not like add_path() chooses a random path when
> the costs are the same, so I wondered if something similar is going on
> here that was going on that led to f03a9ca4. In particular, see [1].
While it's not conclusive proof, the following demonstrates that
relpages dropping by just 1 page causes the join order to change.
regression=# explain
regression-# select t1.unique1 from tenk1 t1
regression-# inner join tenk2 t2 on t1.tenthous = t2.tenthous
regression-# union all
regression-# (values(1)) limit 1;
QUERY PLAN
--------------------------------------------------------------------------------------
Limit (cost=0.00..150.08 rows=1 width=4)
-> Append (cost=0.00..1500965.01 rows=10001 width=4)
-> Nested Loop (cost=0.00..1500915.00 rows=10000 width=4)
Join Filter: (t1.tenthous = t2.tenthous)
-> Seq Scan on tenk1 t1 (cost=0.00..445.00 rows=10000 width=8)
-> Materialize (cost=0.00..495.00 rows=10000 width=4)
-> Seq Scan on tenk2 t2 (cost=0.00..445.00
rows=10000 width=4)
-> Result (cost=0.00..0.01 rows=1 width=4)
regression=# update pg_class set relpages=relpages - 1 where relname = 'tenk2';
UPDATE 1
regression=# explain
regression-# select t1.unique1 from tenk1 t1
regression-# inner join tenk2 t2 on t1.tenthous = t2.tenthous
regression-# union all
regression-# (values(1)) limit 1;
QUERY PLAN
--------------------------------------------------------------------------------------
Limit (cost=0.00..150.52 rows=1 width=4)
-> Append (cost=0.00..1505315.30 rows=10001 width=4)
-> Nested Loop (cost=0.00..1505265.29 rows=10000 width=4)
Join Filter: (t1.tenthous = t2.tenthous)
-> Seq Scan on tenk2 t2 (cost=0.00..445.29 rows=10029 width=4)
-> Materialize (cost=0.00..495.00 rows=10000 width=8)
-> Seq Scan on tenk1 t1 (cost=0.00..445.00
rows=10000 width=8)
-> Result (cost=0.00..0.01 rows=1 width=4)
I tried this with the proposed changes to the test and the plan did not change.
I've pushed the change now.
David
view thread (2+ messages)
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: make add_paths_to_append_rel aware of startup cost
In-Reply-To: <CAApHDvpUjAdvwOoARj9ddsx=-DM9sF_Sh06-pHLx73at5q=TJw@mail.gmail.com>
* 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