public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andy Fan <[email protected]>
To: Andy Fan <[email protected]>
Cc: Thomas Munro <[email protected]>
Cc: David Rowley <[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: Thu, 15 Feb 2024 16:38:03 +0800
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
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]>
Andy Fan <[email protected]> writes:
> Thomas Munro <[email protected]> writes:
>
>> On Thu, Oct 5, 2023 at 9:07 PM David Rowley <[email protected]> wrote:
>>> Thanks. Pushed.
>>
>> FYI somehow this plan from a8a968a8212e flipped in this run:
>>
>> === dumping /home/bf/bf-build/mylodon/HEAD/pgsql.build/testrun/recovery/027_stream_regress/data/regression.diffs
>> ===
>> diff -U3 /home/bf/bf-build/mylodon/HEAD/pgsql/src/test/regress/expected/union.out
>> /home/bf/bf-build/mylodon/HEAD/pgsql.build/testrun/recovery/027_stream_regress/data/results/union.out
>> --- /home/bf/bf-build/mylodon/HEAD/pgsql/src/test/regress/expected/union.out
>> 2024-01-15 00:31:13.947555940 +0000
>> +++ /home/bf/bf-build/mylodon/HEAD/pgsql.build/testrun/recovery/027_stream_regress/data/results/union.out
>> 2024-02-14 00:06:17.075584839 +0000
>> @@ -1447,9 +1447,9 @@
>> -> Append
>> -> Nested Loop
>> Join Filter: (t1.tenthous = t2.tenthous)
>> - -> Seq Scan on tenk1 t1
>> + -> Seq Scan on tenk2 t2
>> -> Materialize
>> - -> Seq Scan on tenk2 t2
>> + -> Seq Scan on tenk1 t1
>> -> Result
>> (8 rows)
>>
>> https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=mylodon&dt=2024-02-14%2000%3A01%3A03
>
> Thanks for this information! I will take a look at this.
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?
--
Best Regards
Andy Fan
Attachments:
[text/x-diff] v1-0001-Attempt-to-stabilize-new-unionall-limit-regressio.patch (2.7K, ../[email protected]/2-v1-0001-Attempt-to-stabilize-new-unionall-limit-regressio.patch)
download | inline diff:
From 01c2b5ae76a4493f33b894afdd4a8d3ce31e1a3e Mon Sep 17 00:00:00 2001
From: "yizhi.fzh" <[email protected]>
Date: Thu, 15 Feb 2024 16:29:30 +0800
Subject: [PATCH v1 1/1] Attempt to stabilize new unionall + limit regression
test
This test was recently added in a8a968a8212e, and some times It appears
to be unstable in regards to the join order presumably due to the
relations at either side of the join being equal in side. Here we add a
qual to make one of them smaller so the planner is more likely to choose
to material the smaller of the two.
Reported-by: Thomas Munro
Author: Andy Fan
Discussion: https://postgr.es/m/CA%2BhUKGLqC-NobKYfjxNM3Gexv9OJ-Fhvy9bugUcXsZjTqH7W%3DQ%40mail.gmail.com#88e6420b59a863403b9b67a0128fdacc
---
src/test/regress/expected/union.out | 11 ++++++-----
src/test/regress/sql/union.sql | 4 ++--
2 files changed, 8 insertions(+), 7 deletions(-)
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index 73e320bad4..0e527b65b3 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -1438,8 +1438,8 @@ where (x = 0) or (q1 >= q2 and q1 <= q2);
-- Ensure we get a Nested Loop join between tenk1 and tenk2
explain (costs off)
select t1.unique1 from tenk1 t1
-inner join tenk2 t2 on t1.tenthous = t2.tenthous
- union all
+inner join tenk2 t2 on t1.tenthous = t2.tenthous and t1.ten > 5
+union all
(values(1)) limit 1;
QUERY PLAN
--------------------------------------------------------
@@ -1447,11 +1447,12 @@ inner join tenk2 t2 on t1.tenthous = t2.tenthous
-> Append
-> Nested Loop
Join Filter: (t1.tenthous = t2.tenthous)
- -> Seq Scan on tenk1 t1
+ -> Seq Scan on tenk2 t2
-> Materialize
- -> Seq Scan on tenk2 t2
+ -> Seq Scan on tenk1 t1
+ Filter: (ten > 5)
-> Result
-(8 rows)
+(9 rows)
-- Ensure there is no problem if cheapest_startup_path is NULL
explain (costs off)
diff --git a/src/test/regress/sql/union.sql b/src/test/regress/sql/union.sql
index 6c509ac80c..13ba9f21ad 100644
--- a/src/test/regress/sql/union.sql
+++ b/src/test/regress/sql/union.sql
@@ -548,8 +548,8 @@ where (x = 0) or (q1 >= q2 and q1 <= q2);
-- Ensure we get a Nested Loop join between tenk1 and tenk2
explain (costs off)
select t1.unique1 from tenk1 t1
-inner join tenk2 t2 on t1.tenthous = t2.tenthous
- union all
+inner join tenk2 t2 on t1.tenthous = t2.tenthous and t1.ten > 5
+union all
(values(1)) limit 1;
-- Ensure there is no problem if cheapest_startup_path is NULL
--
2.34.1
view thread (18+ 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: make add_paths_to_append_rel aware of startup cost
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