Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1raXKO-00ACyD-1T for pgsql-hackers@arkaria.postgresql.org; Thu, 15 Feb 2024 08:42:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1raXKM-00Bes1-6k for pgsql-hackers@arkaria.postgresql.org; Thu, 15 Feb 2024 08:42:42 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1raXKL-00Bert-E0 for pgsql-hackers@lists.postgresql.org; Thu, 15 Feb 2024 08:42:41 +0000 Received: from m16.mail.163.com ([117.135.210.3]) by makus.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1raXKD-006qn4-FH for pgsql-hackers@lists.postgresql.org; Thu, 15 Feb 2024 08:42:38 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=163.com; s=s110527; h=From:Subject:Date:Message-ID:MIME-Version: Content-Type; bh=CVRqes3w4CTGKCGUq5MDlwfqvylBY9fhD8r7t1EcMmc=; b=aNQh/+NLmNQF6zVeWe13vklYw1hzyYdYyXHfnsQuFojste6kj5HFWLCE4igQ9n dS/vRwPU7j2jm0tHYYKQaCZEfOKU2OWsVO7+Kzb19kzW7h8ZBr680tJixAB8XyoN PLwV5bJHvAqIejUXbrxry0TWRC6KTUCK7TsjYVXF9T0CU= Received: from 8235eee8a2a0 (unknown [140.205.118.19]) by gzga-smtp-mta-g1-4 (Coremail) with SMTP id _____wBHVzhozs1lke96CQ--.33154S3; Thu, 15 Feb 2024 16:42:16 +0800 (CST) References: <87eddeiffk.fsf@163.com> User-agent: mu4e 1.10.7; emacs 29.1 From: Andy Fan To: Andy Fan Cc: Thomas Munro , David Rowley , Andy Fan , pgsql-hackers , Zhang Mingli , pgsql-hackers@lists.postgresql.org Subject: Re: make add_paths_to_append_rel aware of startup cost Date: Thu, 15 Feb 2024 16:38:03 +0800 In-reply-to: <87eddeiffk.fsf@163.com> Message-ID: <87a5o2i0ev.fsf@163.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=-=-=" X-CM-TRANSID:_____wBHVzhozs1lke96CQ--.33154S3 X-Coremail-Antispam: 1Uf129KBjvJXoWxJr47Jw4kKw4kKr4DWF18Grg_yoW8Wr45pa yvvFWaywnFqry8X3y7CF1FyFW5Arn3Ww15uwn5Gw1jyw4fuF1xXF9FkFZ09FW7Xr9agwsa gw1rC3ZIyr45XaDanT9S1TB71UUUUU7qnTZGkaVYY2UrUUUUjbIjqfuFe4nvWSU5nxnvy2 9KBjDUYxBIdaVFxhVjvjDU0xZFpf9x0JU1MKXUUUUU= X-Originating-IP: [140.205.118.19] X-CM-SenderInfo: x2klx3xlid0iqsrtqiywtou0bp/xtbBzw+FU2V4H0E6RwAAso List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=-=-= Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Andy Fan writes: > Thomas Munro writes: > >> On Thu, Oct 5, 2023 at 9:07=E2=80=AFPM David Rowley wrote: >>> Thanks. Pushed. >> >> FYI somehow this plan from a8a968a8212e flipped in this run: >> >> =3D=3D=3D dumping /home/bf/bf-build/mylodon/HEAD/pgsql.build/testrun/rec= overy/027_stream_regress/data/regression.diffs >> =3D=3D=3D >> 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_r= egress/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_stre= am_regress/data/results/union.out >> 2024-02-14 00:06:17.075584839 +0000 >> @@ -1447,9 +1447,9 @@ >> -> Append >> -> Nested Loop >> Join Filter: (t1.tenthous =3D 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=3Dmylodon&dt=3D2= 024-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?=20 --=20 Best Regards Andy Fan --=-=-= Content-Type: text/x-diff Content-Disposition: attachment; filename=v1-0001-Attempt-to-stabilize-new-unionall-limit-regressio.patch From 01c2b5ae76a4493f33b894afdd4a8d3ce31e1a3e Mon Sep 17 00:00:00 2001 From: "yizhi.fzh" 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 --=-=-=--