pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
To: Alexander Korotkov <aekorotkov@gmail.com>
To: Alexander Lakhin <exclusion@gmail.com>
Cc: Gregory Stark (as CFM) <stark.cfm@gmail.com>
Cc: Michał Kłeczek <michal@kleczek.org>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Richard Guo <guofenglinux@gmail.com>
Subject: Re: Removing unneeded self joins
Date: Mon, 19 Feb 2024 12:24:09 +0700
Message-ID: <5063f378-ca3c-405e-b1e8-1927ea9052e8@postgrespro.ru> (raw )
In-Reply-To: <CAPpHfdtB0A1Rz8k5yPMCM6ngLt2JM4NSaG8rF0=wT-txdcEf3g@mail.gmail.com >
References: <64486b0b-0404-e39e-322d-0801154901f3@postgrespro.ru >
<1bd80083-a13e-4e9f-a7ce-73c3faab521c@postgrespro.ru >
<CAPpHfdsz8E085YF2VoJe2+h4YAShsh7p--t3kQfQTntiCy63Xg@mail.gmail.com >
<922623af-872e-4cdc-9238-129b18bee42d@postgrespro.ru >
<CAPpHfduoAHTp6O7Q8PDe_w=30v0n_06pGe5M65iR4DYcfaYnRw@mail.gmail.com >
<107682e0-137a-d0fb-2733-7fc6e9562690@gmail.com >
<CAPpHfduLxYm4biJrTbjBxTAW6vkxBswuQ2B=gXU+c37QJd6+Ow@mail.gmail.com >
<a89f480f-8143-0965-f22d-0a892777f501@gmail.com >
<0c5f461f-e26f-4fd9-9700-91b0e9c38fd6@postgrespro.ru >
<336c748b-4ffe-5915-3d3c-ece6d19e021c@gmail.com >
<4788f781-31bd-9796-d7d6-588a751c8787@gmail.com >
<CAPpHfduYKqbWWT=YuBJyoExrwsJSgjUuJN1Gsr2YZMG=nh_ctQ@mail.gmail.com >
<CAPpHfdvu+wyazT+ujJbJ5rKZrHQoG+FCVeWENA4pyf_A1W1WAA@mail.gmail.com >
<56ee4520-e9d1-d519-54fe-c8bff880ce9b@gmail.com >
<CAPpHfdsJ=MZNFw0L2T_k-shOk+4dPNx9j5RZzicy3NW+yiiQVg@mail.gmail.com >
<CAPpHfdtB0A1Rz8k5yPMCM6ngLt2JM4NSaG8rF0=wT-txdcEf3g@mail.gmail.com >
On 18/2/2024 23:18, Alexander Korotkov wrote:
> On Sun, Feb 18, 2024 at 5:04 PM Alexander Korotkov <aekorotkov@gmail.com> wrote:
>> On Sun, Feb 18, 2024 at 3:00 PM Alexander Lakhin <exclusion@gmail.com> wrote:
>>> 09.01.2024 01:09, Alexander Korotkov wrote:
>>>
>>> Fixed in 30b4955a46.
>>>
>>>
>>> Please look at the following query which fails with an error since
>>> d3d55ce57:
>>>
>>> create table t (i int primary key);
>>>
>>> select t3.i from t t1
>>> join t t2 on t1.i = t2.i,
>>> lateral (select t1.i limit 1) t3;
>>>
>>> ERROR: non-LATERAL parameter required by subquery
>>
>> Thank you for spotting. I'm looking at this.
>
> Attached is a draft patch fixing this query. Could you, please, recheck?
I reviewed this patch. Why do you check only the target list? I guess
these links can be everywhere. See the patch in the attachment with the
elaborated test and slightly changed code.
--
regards,
Andrei Lepikhov
Postgres Professional
Attachments:
0001-Replace-relids-in-lateral-subquery-target-list-du-v2.patch (text/plain, 5.2K) download
From 7f94a3c96fd410522b87e570240cdb96b300dd31 Mon Sep 17 00:00:00 2001
From: "Andrey V. Lepikhov" <a.lepikhov@postgrespro.ru>
Date: Mon, 19 Feb 2024 12:17:55 +0700
Subject: [PATCH] Replace relids in lateral subquery target list during SJE
---
src/backend/optimizer/plan/analyzejoins.c | 29 ++++++++++++++-
src/test/regress/expected/join.out | 44 +++++++++++++++++++++++
src/test/regress/sql/join.sql | 12 +++++++
3 files changed, 84 insertions(+), 1 deletion(-)
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index e494acd51a..072298f66c 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -395,7 +395,34 @@ remove_rel_from_query(PlannerInfo *root, RelOptInfo *rel,
}
/* Update lateral references. */
- replace_varno((Node *) otherrel->lateral_vars, relid, subst);
+ if (root->hasLateralRTEs)
+ {
+ RangeTblEntry *rte = root->simple_rte_array[rti];
+ ReplaceVarnoContext ctx = {.from = relid,.to = subst};
+
+ if (rte->lateral)
+ {
+ replace_varno((Node *) otherrel->lateral_vars, relid, subst);
+
+ /*
+ * Although we pass root->parse through cleanup procedure,
+ * but parse->rtable and rte contains refs to different copies
+ * of the subquery.
+ */
+ if (otherrel->rtekind == RTE_SUBQUERY)
+ query_tree_walker(rte->subquery, replace_varno_walker, &ctx,
+ QTW_EXAMINE_SORTGROUP);
+#ifdef USE_ASSERT_CHECKING
+ /* Just check possibly hidden non-replaced relids */
+ Assert(!bms_is_member(relid, pull_varnos(root, (Node *) rte->tablesample)));
+ Assert(!bms_is_member(relid, pull_varnos(root, (Node *) rte->functions)));
+ Assert(!bms_is_member(relid, pull_varnos(root, (Node *) rte->tablefunc)));
+ Assert(!bms_is_member(relid, pull_varnos(root, (Node *) rte->values_lists)));
+#endif
+ }
+ }
+
+
}
/*
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 0c2cba8921..d560a4a6b9 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -6349,6 +6349,50 @@ on true;
-> Seq Scan on int8_tbl y
(7 rows)
+-- Test processing target lists in lateral subqueries
+explain (verbose, costs off)
+SELECT t3.a FROM sj t1, sj t2,
+LATERAL (SELECT t1.a WHERE t1.a <> 1
+ GROUP BY (t1.a) HAVING t1.a > 0 ORDER BY t1.a LIMIT 1) t3,
+LATERAL (SELECT t1.a,t3.a WHERE t1.a <> t3.a+t2.a
+ GROUP BY (t3.a) HAVING t1.a > t3.a*t3.a+t2.a/t1.a LIMIT 2) t4,
+LATERAL (SELECT * FROM sj TABLESAMPLE bernoulli(t1.a/t2.a)
+ REPEATABLE (t1.a+t2.a)) t5,
+LATERAL generate_series(1, t1.a + t2.a) AS t6
+WHERE t1.a = t2.a;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Output: (t2.a)
+ -> Nested Loop
+ Output: t2.a, (t2.a)
+ -> Nested Loop
+ Output: t2.a, (t2.a)
+ -> Nested Loop
+ Output: t2.a, (t2.a)
+ -> Seq Scan on public.sj t2
+ Output: t2.a, t2.b, t2.c
+ Filter: (t2.a IS NOT NULL)
+ -> Limit
+ Output: (t2.a)
+ -> Group
+ Output: t2.a
+ -> Result
+ One-Time Filter: ((t2.a <> 1) AND (t2.a > 0))
+ -> Limit
+ Output: NULL::integer, ((t2.a))
+ -> Group
+ Output: NULL::integer, (t2.a)
+ -> Result
+ One-Time Filter: ((t2.a <> ((t2.a) + t2.a)) AND (t2.a > (((t2.a) * (t2.a)) + (t2.a / t2.a))))
+ -> Sample Scan on public.sj
+ Output: sj.a, sj.b, sj.c
+ Sampling: bernoulli ((t2.a / t2.a)) REPEATABLE ((t2.a + t2.a))
+ -> Function Scan on pg_catalog.generate_series t6
+ Output: t6.t6
+ Function Call: generate_series(1, (t2.a + t2.a))
+(29 rows)
+
-- Check updating of Lateral links from top-level query to the removing relation
explain (COSTS OFF)
SELECT * FROM pg_am am WHERE am.amname IN (
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 257f727a2b..9341f80bbc 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -2406,6 +2406,18 @@ left join (select coalesce(y.q1, 1) from int8_tbl y
on true) z
on true;
+-- Test processing target lists in lateral subqueries
+explain (verbose, costs off)
+SELECT t3.a FROM sj t1, sj t2,
+LATERAL (SELECT t1.a WHERE t1.a <> 1
+ GROUP BY (t1.a) HAVING t1.a > 0 ORDER BY t1.a LIMIT 1) t3,
+LATERAL (SELECT t1.a,t3.a WHERE t1.a <> t3.a+t2.a
+ GROUP BY (t3.a) HAVING t1.a > t3.a*t3.a+t2.a/t1.a LIMIT 2) t4,
+LATERAL (SELECT * FROM sj TABLESAMPLE bernoulli(t1.a/t2.a)
+ REPEATABLE (t1.a+t2.a)) t5,
+LATERAL generate_series(1, t1.a + t2.a) AS t6
+WHERE t1.a = t2.a;
+
-- Check updating of Lateral links from top-level query to the removing relation
explain (COSTS OFF)
SELECT * FROM pg_am am WHERE am.amname IN (
--
2.43.0
view thread (247+ messages) latest in thread
Message-ID: <5063f378-ca3c-405e-b1e8-1927ea9052e8@postgrespro.ru>
Permalink: ../5063f378-ca3c-405e-b1e8-1927ea9052e8@postgrespro.ru/
Also on: postgresql.org/message-id/5063f378-ca3c-405e-b1e8-1927ea9052e8@postgrespro.ru
copy link · copy postgr.es · copy git am
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: pgsql-hackers@postgresql.org
Cc: a.lepikhov@postgrespro.ru, aekorotkov@gmail.com, exclusion@gmail.com, stark.cfm@gmail.com, michal@kleczek.org, pgsql-hackers@lists.postgresql.org, guofenglinux@gmail.com
Subject: Re: Removing unneeded self joins
In-Reply-To: <5063f378-ca3c-405e-b1e8-1927ea9052e8@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox