Improving EXPLAIN's display of SubPlan nodes
| От | Tom Lane |
|---|---|
| Тема | Improving EXPLAIN's display of SubPlan nodes |
| Дата | |
| Msg-id | 2838538.1705692747@sss.pgh.pa.us обсуждение исходный текст |
| Ответы |
Re: Improving EXPLAIN's display of SubPlan nodes
(Aleksander Alekseev <aleksander@timescale.com>)
|
| Список | pgsql-hackers |
EXPLAIN has always been really poor at displaying SubPlan nodes
in expressions: you don't get much more than "(SubPlan N)".
This is mostly because every time I thought about it, I despaired
of trying to represent all the information in a SubPlan accurately.
However, a recent discussion[1] made me realize that we could do
a lot better just by displaying the SubLinkType and the testexpr
(if relevant). So here's a proposed patch. You can see what
it does by examining the regression test changes.
There's plenty of room to bikeshed about exactly how to display
this stuff, and I'm open to suggestions.
BTW, I was somewhat depressed to discover that we have exactly
zero regression coverage of the ROWCOMPARE_SUBLINK code paths;
not only was EXPLAIN output not covered, but the planner and
executor too. So I added some simple tests for that. Otherwise
I think existing coverage is enough for this.
regards, tom lane
[1] https://www.postgresql.org/message-id/flat/149c5c2f-4267-44e3-a177-d1fd24c53f6d%40universite-paris-saclay.fr
From 30d4c77641876db2ffd81a188bd2e41856a7a99e Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri, 19 Jan 2024 14:16:09 -0500
Subject: [PATCH v1] Improve EXPLAIN's display of SubPlan nodes.
Represent the SubLinkType as best we can, and show the testexpr
where relevant. To aid in interpreting testexprs, add the output
parameter IDs to the subplan name for subplans as well as initplans.
---
.../postgres_fdw/expected/postgres_fdw.out | 12 +-
src/backend/optimizer/plan/subselect.c | 30 ++--
src/backend/utils/adt/ruleutils.c | 45 +++++-
src/test/regress/expected/aggregates.out | 2 +-
src/test/regress/expected/insert_conflict.out | 2 +-
src/test/regress/expected/join.out | 24 ++--
src/test/regress/expected/memoize.out | 2 +-
src/test/regress/expected/rowsecurity.out | 56 ++++----
src/test/regress/expected/select_parallel.out | 22 +--
src/test/regress/expected/subselect.out | 135 ++++++++++++------
src/test/regress/expected/updatable_views.out | 24 ++--
src/test/regress/sql/subselect.sql | 14 ++
12 files changed, 241 insertions(+), 127 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index d83f6ae8cb..2b5e56cae9 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -3264,14 +3264,14 @@ select sum(c1) filter (where (c1 / c1) * random() <= 1) from ft1 group by c2 ord
explain (verbose, costs off)
select sum(c2) filter (where c2 in (select c2 from ft1 where c2 < 5)) from ft1;
- QUERY PLAN
--------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------
Aggregate
- Output: sum(ft1.c2) FILTER (WHERE (hashed SubPlan 1))
+ Output: sum(ft1.c2) FILTER (WHERE (ANY (ft1.c2 = $0) FROM HASHED SubPlan 1 (returns $0)))
-> Foreign Scan on public.ft1
Output: ft1.c2
Remote SQL: SELECT c2 FROM "S 1"."T 1"
- SubPlan 1
+ SubPlan 1 (returns $0)
-> Foreign Scan on public.ft1 ft1_1
Output: ft1_1.c2
Remote SQL: SELECT c2 FROM "S 1"."T 1" WHERE ((c2 < 5))
@@ -11899,8 +11899,8 @@ SELECT a FROM base_tbl WHERE a IN (SELECT a FROM foreign_tbl);
-----------------------------------------------------------------------------
Seq Scan on public.base_tbl
Output: base_tbl.a
- Filter: (SubPlan 1)
- SubPlan 1
+ Filter: (ANY (base_tbl.a = $1) FROM SubPlan 1 (returns $1))
+ SubPlan 1 (returns $1)
-> Result
Output: base_tbl.a
-> Append
diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c
index 3115d79ad9..9ceac6234d 100644
--- a/src/backend/optimizer/plan/subselect.c
+++ b/src/backend/optimizer/plan/subselect.c
@@ -326,6 +326,7 @@ build_subplan(PlannerInfo *root, Plan *plan, PlannerInfo *subroot,
Node *result;
SubPlan *splan;
bool isInitPlan;
+ StringInfoData splanname;
ListCell *lc;
/*
@@ -560,22 +561,31 @@ build_subplan(PlannerInfo *root, Plan *plan, PlannerInfo *subroot,
splan->plan_id);
/* Label the subplan for EXPLAIN purposes */
- splan->plan_name = palloc(32 + 12 * list_length(splan->setParam));
- sprintf(splan->plan_name, "%s %d",
- isInitPlan ? "InitPlan" : "SubPlan",
- splan->plan_id);
+ initStringInfo(&splanname);
+ appendStringInfo(&splanname, "%s %d",
+ isInitPlan ? "InitPlan" : "SubPlan",
+ splan->plan_id);
if (splan->setParam)
{
- char *ptr = splan->plan_name + strlen(splan->plan_name);
-
- ptr += sprintf(ptr, " (returns ");
+ appendStringInfoString(&splanname, " (returns ");
foreach(lc, splan->setParam)
{
- ptr += sprintf(ptr, "$%d%s",
- lfirst_int(lc),
- lnext(splan->setParam, lc) ? "," : ")");
+ appendStringInfo(&splanname, "$%d%s",
+ lfirst_int(lc),
+ lnext(splan->setParam, lc) ? "," : ")");
+ }
+ }
+ else if (splan->paramIds)
+ {
+ appendStringInfoString(&splanname, " (returns ");
+ foreach(lc, splan->paramIds)
+ {
+ appendStringInfo(&splanname, "$%d%s",
+ lfirst_int(lc),
+ lnext(splan->paramIds, lc) ? "," : ")");
}
}
+ splan->plan_name = splanname.data;
/* Lastly, fill in the cost estimates for use later */
cost_subplan(root, splan, plan);
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 0b2a164057..9ee35640ba 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -8869,12 +8869,51 @@ get_rule_expr(Node *node, deparse_context *context,
* We cannot see an already-planned subplan in rule deparsing,
* only while EXPLAINing a query plan. We don't try to
* reconstruct the original SQL, just reference the subplan
- * that appears elsewhere in EXPLAIN's result.
+ * that appears elsewhere in EXPLAIN's result. It does seem
+ * useful to show the subLinkType and testexpr, however, and
+ * we also note whether the subplan will be hashed.
*/
+ switch (subplan->subLinkType)
+ {
+ case EXISTS_SUBLINK:
+ appendStringInfoString(buf, "EXISTS(");
+ Assert(subplan->testexpr == NULL);
+ break;
+ case ALL_SUBLINK:
+ appendStringInfoString(buf, "(ALL ");
+ get_rule_expr(subplan->testexpr, context, showimplicit);
+ appendStringInfoString(buf, " FROM ");
+ break;
+ case ANY_SUBLINK:
+ appendStringInfoString(buf, "(ANY ");
+ get_rule_expr(subplan->testexpr, context, showimplicit);
+ appendStringInfoString(buf, " FROM ");
+ break;
+ case ROWCOMPARE_SUBLINK:
+ appendStringInfoString(buf, "(ROWCOMPARE ");
+ get_rule_expr(subplan->testexpr, context, showimplicit);
+ appendStringInfoString(buf, " FROM ");
+ break;
+ case EXPR_SUBLINK:
+ case MULTIEXPR_SUBLINK:
+ /* No need to decorate these subplan references */
+ appendStringInfoString(buf, "(");
+ Assert(subplan->testexpr == NULL);
+ break;
+ case ARRAY_SUBLINK:
+ appendStringInfoString(buf, "ARRAY(");
+ Assert(subplan->testexpr == NULL);
+ break;
+ case CTE_SUBLINK:
+ /* This case is unreachable within expressions */
+ appendStringInfoString(buf, "CTE(");
+ Assert(subplan->testexpr == NULL);
+ break;
+ }
if (subplan->useHashTable)
- appendStringInfo(buf, "(hashed %s)", subplan->plan_name);
+ appendStringInfo(buf, "HASHED %s)", subplan->plan_name);
else
- appendStringInfo(buf, "(%s)", subplan->plan_name);
+ appendStringInfo(buf, "%s)", subplan->plan_name);
}
break;
diff --git a/src/test/regress/expected/aggregates.out b/src/test/regress/expected/aggregates.out
index f635c5a1af..1e2f3c44f5 100644
--- a/src/test/regress/expected/aggregates.out
+++ b/src/test/regress/expected/aggregates.out
@@ -738,7 +738,7 @@ select array(select sum(x+y) s
QUERY PLAN
-------------------------------------------------------------------
Function Scan on pg_catalog.generate_series x
- Output: (SubPlan 1)
+ Output: ARRAY(SubPlan 1)
Function Call: generate_series(1, 3)
SubPlan 1
-> Sort
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 9e9e3bd00c..9a1e64dbf0 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -50,7 +50,7 @@ explain (costs off) insert into insertconflicttest values(0, 'Crowberry') on con
Insert on insertconflicttest
Conflict Resolution: UPDATE
Conflict Arbiter Indexes: op_index_key, collation_index_key, both_index_key
- Conflict Filter: (SubPlan 1)
+ Conflict Filter: EXISTS(SubPlan 1)
-> Result
SubPlan 1
-> Index Only Scan using both_index_expr_key on insertconflicttest ii
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index a2fad81d7a..451f21698f 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -3028,11 +3028,11 @@ where unique1 in (select unique2 from tenk1 b);
explain (costs off)
select a.* from tenk1 a
where unique1 not in (select unique2 from tenk1 b);
- QUERY PLAN
---------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------------------
Seq Scan on tenk1 a
- Filter: (NOT (hashed SubPlan 1))
- SubPlan 1
+ Filter: (NOT (ANY (unique1 = $0) FROM HASHED SubPlan 1 (returns $0)))
+ SubPlan 1 (returns $0)
-> Index Only Scan using tenk1_unique2 on tenk1 b
(4 rows)
@@ -5278,13 +5278,13 @@ explain (costs off)
select a.unique1, b.unique2
from onek a left join onek b on a.unique1 = b.unique2
where b.unique2 = any (select q1 from int8_tbl c where c.q1 < b.unique1);
- QUERY PLAN
-----------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------
Hash Join
Hash Cond: (b.unique2 = a.unique1)
-> Seq Scan on onek b
- Filter: (SubPlan 1)
- SubPlan 1
+ Filter: (ANY (unique2 = $1) FROM SubPlan 1 (returns $1))
+ SubPlan 1 (returns $1)
-> Seq Scan on int8_tbl c
Filter: (q1 < b.unique1)
-> Hash
@@ -8219,8 +8219,8 @@ lateral (select * from int8_tbl t1,
where q2 = (select greatest(t1.q1,t2.q2))
and (select v.id=0)) offset 0) ss2) ss
where t1.q1 = ss.q2) ss0;
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------
Nested Loop
Output: "*VALUES*".column1, t1.q1, t1.q2, ss2.q1, ss2.q2
-> Seq Scan on public.int8_tbl t1
@@ -8234,8 +8234,8 @@ lateral (select * from int8_tbl t1,
Filter: (t1.q1 = ss2.q2)
-> Seq Scan on public.int8_tbl t2
Output: t2.q1, t2.q2
- Filter: (SubPlan 3)
- SubPlan 3
+ Filter: (ANY (t2.q1 = $5) FROM SubPlan 3 (returns $5))
+ SubPlan 3 (returns $5)
-> Result
Output: t3.q2
One-Time Filter: $4
diff --git a/src/test/regress/expected/memoize.out b/src/test/regress/expected/memoize.out
index f5202430f8..e7f0d20fc9 100644
--- a/src/test/regress/expected/memoize.out
+++ b/src/test/regress/expected/memoize.out
@@ -276,7 +276,7 @@ WHERE unique1 < 3
----------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 t0
Index Cond: (unique1 < 3)
- Filter: (SubPlan 1)
+ Filter: EXISTS(SubPlan 1)
SubPlan 1
-> Nested Loop
-> Index Scan using tenk1_hundred on tenk1 t2
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index 6988128aa4..3f3815a9cf 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -1437,11 +1437,11 @@ NOTICE: f_leak => 03b26944890929ff751653acb2f2af79
(1 row)
EXPLAIN (COSTS OFF) SELECT * FROM only s1 WHERE f_leak(b);
- QUERY PLAN
------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------
Seq Scan on s1
- Filter: ((hashed SubPlan 1) AND f_leak(b))
- SubPlan 1
+ Filter: ((ANY (a = $0) FROM HASHED SubPlan 1 (returns $0)) AND f_leak(b))
+ SubPlan 1 (returns $0)
-> Seq Scan on s2
Filter: (((x % 2) = 0) AND (y ~~ '%2f%'::text))
(5 rows)
@@ -1457,11 +1457,11 @@ NOTICE: f_leak => 03b26944890929ff751653acb2f2af79
(1 row)
EXPLAIN (COSTS OFF) SELECT * FROM s1 WHERE f_leak(b);
- QUERY PLAN
------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------
Seq Scan on s1
- Filter: ((hashed SubPlan 1) AND f_leak(b))
- SubPlan 1
+ Filter: ((ANY (a = $0) FROM HASHED SubPlan 1 (returns $0)) AND f_leak(b))
+ SubPlan 1 (returns $0)
-> Seq Scan on s2
Filter: (((x % 2) = 0) AND (y ~~ '%af%'::text))
(5 rows)
@@ -1473,15 +1473,15 @@ SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
(1 row)
EXPLAIN (COSTS OFF) SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
- QUERY PLAN
--------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------
Seq Scan on s2
Filter: (((x % 2) = 0) AND (y ~~ '%28%'::text))
SubPlan 2
-> Limit
-> Seq Scan on s1
- Filter: (hashed SubPlan 1)
- SubPlan 1
+ Filter: (ANY (a = $1) FROM HASHED SubPlan 1 (returns $1))
+ SubPlan 1 (returns $1)
-> Seq Scan on s2 s2_1
Filter: (((x % 2) = 0) AND (y ~~ '%af%'::text))
(9 rows)
@@ -2687,11 +2687,11 @@ NOTICE: f_leak => bbb
(1 row)
EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------
Seq Scan on z1
- Filter: ((NOT (hashed SubPlan 1)) AND ((a % 2) = 0) AND f_leak(b))
- SubPlan 1
+ Filter: ((NOT (ANY (a = $0) FROM HASHED SubPlan 1 (returns $0))) AND ((a % 2) = 0) AND f_leak(b))
+ SubPlan 1 (returns $0)
-> Seq Scan on z1_blacklist
(4 rows)
@@ -2705,11 +2705,11 @@ NOTICE: f_leak => bbb
(1 row)
EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------
Seq Scan on z1
- Filter: ((NOT (hashed SubPlan 1)) AND ((a % 2) = 0) AND f_leak(b))
- SubPlan 1
+ Filter: ((NOT (ANY (a = $0) FROM HASHED SubPlan 1 (returns $0))) AND ((a % 2) = 0) AND f_leak(b))
+ SubPlan 1 (returns $0)
-> Seq Scan on z1_blacklist
(4 rows)
@@ -2877,11 +2877,11 @@ NOTICE: f_leak => bbb
(1 row)
EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------
Seq Scan on z1
- Filter: ((NOT (hashed SubPlan 1)) AND ((a % 2) = 0) AND f_leak(b))
- SubPlan 1
+ Filter: ((NOT (ANY (a = $0) FROM HASHED SubPlan 1 (returns $0))) AND ((a % 2) = 0) AND f_leak(b))
+ SubPlan 1 (returns $0)
-> Seq Scan on z1_blacklist
(4 rows)
@@ -2903,11 +2903,11 @@ NOTICE: f_leak => aba
(1 row)
EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------
Seq Scan on z1
- Filter: ((NOT (hashed SubPlan 1)) AND ((a % 2) = 1) AND f_leak(b))
- SubPlan 1
+ Filter: ((NOT (ANY (a = $0) FROM HASHED SubPlan 1 (returns $0))) AND ((a % 2) = 1) AND f_leak(b))
+ SubPlan 1 (returns $0)
-> Seq Scan on z1_blacklist
(4 rows)
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index d88353d496..09a90cd78f 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -291,15 +291,15 @@ alter table tenk2 set (parallel_workers = 0);
explain (costs off)
select count(*) from tenk1 where (two, four) not in
(select hundred, thousand from tenk2 where thousand > 100);
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
Finalize Aggregate
-> Gather
Workers Planned: 4
-> Partial Aggregate
-> Parallel Seq Scan on tenk1
- Filter: (NOT (hashed SubPlan 1))
- SubPlan 1
+ Filter: (NOT (ANY ((two = $0) AND (four = $1)) FROM HASHED SubPlan 1 (returns $0,$1)))
+ SubPlan 1 (returns $0,$1)
-> Seq Scan on tenk2
Filter: (thousand > 100)
(9 rows)
@@ -315,11 +315,11 @@ select count(*) from tenk1 where (two, four) not in
explain (costs off)
select * from tenk1 where (unique1 + random())::integer not in
(select ten from tenk2);
- QUERY PLAN
-------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------
Seq Scan on tenk1
- Filter: (NOT (hashed SubPlan 1))
- SubPlan 1
+ Filter: (NOT (ANY ((((unique1)::double precision + random()))::integer = $0) FROM HASHED SubPlan 1 (returns $0)))
+ SubPlan 1 (returns $0)
-> Seq Scan on tenk2
(4 rows)
@@ -1129,10 +1129,10 @@ ORDER BY 1, 2, 3;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT generate_series(1, two), array(select generate_series(1, two))
FROM tenk1 ORDER BY tenthous;
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------
ProjectSet
- Output: generate_series(1, tenk1.two), (SubPlan 1), tenk1.tenthous
+ Output: generate_series(1, tenk1.two), ARRAY(SubPlan 1), tenk1.tenthous
-> Gather Merge
Output: tenk1.two, tenk1.tenthous
Workers Planned: 4
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out
index a3a4d03d10..57cfa10030 100644
--- a/src/test/regress/expected/subselect.out
+++ b/src/test/regress/expected/subselect.out
@@ -202,6 +202,57 @@ SELECT f1 AS "Correlated Field"
3
(5 rows)
+-- Check ROWCOMPARE cases, both correlated and not
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT ROW(1, 2) = (SELECT f1, f2) AS eq FROM SUBSELECT_TBL;
+ QUERY PLAN
+-------------------------------------------------------------------------------
+ Seq Scan on public.subselect_tbl
+ Output: (ROWCOMPARE ((1 = $2) AND (2 = $3)) FROM SubPlan 1 (returns $2,$3))
+ SubPlan 1 (returns $2,$3)
+ -> Result
+ Output: subselect_tbl.f1, subselect_tbl.f2
+(5 rows)
+
+SELECT ROW(1, 2) = (SELECT f1, f2) AS eq FROM SUBSELECT_TBL;
+ eq
+----
+ t
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+(8 rows)
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT ROW(1, 2) = (SELECT 3, 4) AS eq FROM SUBSELECT_TBL;
+ QUERY PLAN
+-----------------------------------
+ Seq Scan on public.subselect_tbl
+ Output: ((1 = $0) AND (2 = $1))
+ InitPlan 1 (returns $0,$1)
+ -> Result
+ Output: 3, 4
+(5 rows)
+
+SELECT ROW(1, 2) = (SELECT 3, 4) AS eq FROM SUBSELECT_TBL;
+ eq
+----
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+ f
+(8 rows)
+
+SELECT ROW(1, 2) = (SELECT f1, f2 FROM SUBSELECT_TBL); -- error
+ERROR: more than one row returned by a subquery used as an expression
-- Subselects without aliases
SELECT count FROM (SELECT COUNT(DISTINCT name) FROM road);
count
@@ -324,11 +375,11 @@ explain (verbose, costs off) select '42' union all select 43;
-- check materialization of an initplan reference (bug #14524)
explain (verbose, costs off)
select 1 = all (select (select 1));
- QUERY PLAN
------------------------------------
+ QUERY PLAN
+------------------------------------------------------
Result
- Output: (SubPlan 2)
- SubPlan 2
+ Output: (ALL (1 = $1) FROM SubPlan 2 (returns $1))
+ SubPlan 2 (returns $1)
-> Materialize
Output: ($0)
InitPlan 1 (returns $0)
@@ -377,7 +428,7 @@ select * from int4_tbl o where exists
QUERY PLAN
--------------------------------------
Seq Scan on int4_tbl o
- Filter: (SubPlan 1)
+ Filter: EXISTS(SubPlan 1)
SubPlan 1
-> Limit
-> Seq Scan on int4_tbl i
@@ -840,11 +891,11 @@ select * from outer_text where (f1, f2) not in (select * from inner_text);
--
explain (verbose, costs off)
select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
- QUERY PLAN
--------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------
Result
- Output: (hashed SubPlan 1)
- SubPlan 1
+ Output: (ANY ('foo'::text = $0) FROM HASHED SubPlan 1 (returns $0))
+ SubPlan 1 (returns $0)
-> Append
-> Result
Output: 'bar'::name
@@ -864,11 +915,11 @@ select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
--
explain (verbose, costs off)
select row(row(row(1))) = any (select row(row(1)));
- QUERY PLAN
--------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------
Result
- Output: (SubPlan 1)
- SubPlan 1
+ Output: (ANY ('("(1)")'::record = $0) FROM SubPlan 1 (returns $0))
+ SubPlan 1 (returns $0)
-> Materialize
Output: '("(1)")'::record
-> Result
@@ -907,11 +958,11 @@ language sql as 'select $1::text = $2';
create operator = (procedure=bogus_int8_text_eq, leftarg=int8, rightarg=text);
explain (costs off)
select * from int8_tbl where q1 in (select c1 from inner_text);
- QUERY PLAN
---------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------
Seq Scan on int8_tbl
- Filter: (hashed SubPlan 1)
- SubPlan 1
+ Filter: (ANY ((q1)::text = $0) FROM HASHED SubPlan 1 (returns $0))
+ SubPlan 1 (returns $0)
-> Seq Scan on inner_text
(4 rows)
@@ -928,11 +979,11 @@ create or replace function bogus_int8_text_eq(int8, text) returns boolean
language sql as 'select $1::text = $2 and $1::text = $2';
explain (costs off)
select * from int8_tbl where q1 in (select c1 from inner_text);
- QUERY PLAN
---------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
Seq Scan on int8_tbl
- Filter: (hashed SubPlan 1)
- SubPlan 1
+ Filter: (ANY (((q1)::text = $0) AND ((q1)::text = $0)) FROM HASHED SubPlan 1 (returns $0))
+ SubPlan 1 (returns $0)
-> Seq Scan on inner_text
(4 rows)
@@ -949,11 +1000,11 @@ create or replace function bogus_int8_text_eq(int8, text) returns boolean
language sql as 'select $2 = $1::text';
explain (costs off)
select * from int8_tbl where q1 in (select c1 from inner_text);
- QUERY PLAN
---------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------
Seq Scan on int8_tbl
- Filter: (SubPlan 1)
- SubPlan 1
+ Filter: (ANY ($0 = (q1)::text) FROM SubPlan 1 (returns $0))
+ SubPlan 1 (returns $0)
-> Materialize
-> Seq Scan on inner_text
(5 rows)
@@ -972,12 +1023,12 @@ rollback; -- to get rid of the bogus operator
explain (costs off)
select count(*) from tenk1 t
where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0);
- QUERY PLAN
---------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------------
Aggregate
-> Seq Scan on tenk1 t
- Filter: ((hashed SubPlan 2) OR (ten < 0))
- SubPlan 2
+ Filter: ((ANY (unique2 = $1) FROM HASHED SubPlan 2 (returns $1)) OR (ten < 0))
+ SubPlan 2 (returns $1)
-> Index Only Scan using tenk1_unique1 on tenk1 k
(5 rows)
@@ -997,7 +1048,7 @@ where (exists(select 1 from tenk1 k where k.unique1 = t.unique2) or ten < 0)
Aggregate
-> Bitmap Heap Scan on tenk1 t
Recheck Cond: (thousand = 1)
- Filter: ((SubPlan 1) OR (ten < 0))
+ Filter: (EXISTS(SubPlan 1) OR (ten < 0))
-> Bitmap Index Scan on tenk1_thous_tenthous
Index Cond: (thousand = 1)
SubPlan 1
@@ -1022,11 +1073,11 @@ analyze exists_tbl;
explain (costs off)
select * from exists_tbl t1
where (exists(select 1 from exists_tbl t2 where t1.c1 = t2.c2) or c3 < 0);
- QUERY PLAN
-------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------------------------
Append
-> Seq Scan on exists_tbl_null t1_1
- Filter: ((SubPlan 1) OR (c3 < 0))
+ Filter: (EXISTS(SubPlan 1) OR (c3 < 0))
SubPlan 1
-> Append
-> Seq Scan on exists_tbl_null t2_1
@@ -1034,8 +1085,8 @@ select * from exists_tbl t1
-> Seq Scan on exists_tbl_def t2_2
Filter: (t1_1.c1 = c2)
-> Seq Scan on exists_tbl_def t1_2
- Filter: ((hashed SubPlan 2) OR (c3 < 0))
- SubPlan 2
+ Filter: ((ANY (c1 = $1) FROM HASHED SubPlan 2 (returns $1)) OR (c3 < 0))
+ SubPlan 2 (returns $1)
-> Append
-> Seq Scan on exists_tbl_null t2_4
-> Seq Scan on exists_tbl_def t2_5
@@ -1143,16 +1194,16 @@ where o.ten = 0;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate
- Output: sum((((hashed SubPlan 1)))::integer)
+ Output: sum((((ANY (i.ten = $1) FROM HASHED SubPlan 1 (returns $1))))::integer)
-> Nested Loop
- Output: ((hashed SubPlan 1))
+ Output: ((ANY (i.ten = $1) FROM HASHED SubPlan 1 (returns $1)))
-> Seq Scan on public.onek o
Output: o.unique1, o.unique2, o.two, o.four, o.ten, o.twenty, o.hundred, o.thousand, o.twothousand,
o.fivethous,o.tenthous, o.odd, o.even, o.stringu1, o.stringu2, o.string4
Filter: (o.ten = 0)
-> Index Scan using onek_unique1 on public.onek i
- Output: (hashed SubPlan 1), random()
+ Output: (ANY (i.ten = $1) FROM HASHED SubPlan 1 (returns $1)), random()
Index Cond: (i.unique1 = o.unique1)
- SubPlan 1
+ SubPlan 1 (returns $1)
-> Seq Scan on public.int4_tbl
Output: int4_tbl.f1
Filter: (int4_tbl.f1 <= o.hundred)
@@ -1319,7 +1370,7 @@ select * from
----------------------------------------
Values Scan on "*VALUES*"
Output: "*VALUES*".column1
- SubPlan 1
+ SubPlan 1 (returns $0)
-> Values Scan on "*VALUES*_1"
Output: "*VALUES*_1".column1
(5 rows)
@@ -1346,12 +1397,12 @@ select * from int4_tbl where
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Semi Join
Output: int4_tbl.f1
- Join Filter: (CASE WHEN (hashed SubPlan 1) THEN int4_tbl.f1 ELSE NULL::integer END = b.ten)
+ Join Filter: (CASE WHEN (ANY (int4_tbl.f1 = $0) FROM HASHED SubPlan 1 (returns $0)) THEN int4_tbl.f1 ELSE
NULL::integerEND = b.ten)
-> Seq Scan on public.int4_tbl
Output: int4_tbl.f1
-> Seq Scan on public.tenk1 b
Output: b.unique1, b.unique2, b.two, b.four, b.ten, b.twenty, b.hundred, b.thousand, b.twothousand,
b.fivethous,b.tenthous, b.odd, b.even, b.stringu1, b.stringu2, b.string4
- SubPlan 1
+ SubPlan 1 (returns $0)
-> Index Only Scan using tenk1_unique1 on public.tenk1 a
Output: a.unique1
(10 rows)
diff --git a/src/test/regress/expected/updatable_views.out b/src/test/regress/expected/updatable_views.out
index 1950e6f281..86f09b6284 100644
--- a/src/test/regress/expected/updatable_views.out
+++ b/src/test/regress/expected/updatable_views.out
@@ -2586,8 +2586,8 @@ SELECT * FROM v1 WHERE a=8;
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
- QUERY PLAN
------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
Update on public.t1
Update on public.t1 t1_1
Update on public.t11 t1_2
@@ -2599,7 +2599,7 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
-> Index Scan using t1_a_idx on public.t1 t1_1
Output: t1_1.tableoid, t1_1.ctid
Index Cond: ((t1_1.a > 5) AND (t1_1.a < 7))
- Filter: ((t1_1.a <> 6) AND (SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
+ Filter: ((t1_1.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
SubPlan 1
-> Append
-> Seq Scan on public.t12 t12_1
@@ -2609,15 +2609,15 @@ UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
-> Index Scan using t11_a_idx on public.t11 t1_2
Output: t1_2.tableoid, t1_2.ctid
Index Cond: ((t1_2.a > 5) AND (t1_2.a < 7))
- Filter: ((t1_2.a <> 6) AND (SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
+ Filter: ((t1_2.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
-> Index Scan using t12_a_idx on public.t12 t1_3
Output: t1_3.tableoid, t1_3.ctid
Index Cond: ((t1_3.a > 5) AND (t1_3.a < 7))
- Filter: ((t1_3.a <> 6) AND (SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
+ Filter: ((t1_3.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
-> Index Scan using t111_a_idx on public.t111 t1_4
Output: t1_4.tableoid, t1_4.ctid
Index Cond: ((t1_4.a > 5) AND (t1_4.a < 7))
- Filter: ((t1_4.a <> 6) AND (SubPlan 1) AND snoop(t1_4.a) AND leakproof(t1_4.a))
+ Filter: ((t1_4.a <> 6) AND EXISTS(SubPlan 1) AND snoop(t1_4.a) AND leakproof(t1_4.a))
(30 rows)
UPDATE v1 SET a=100 WHERE snoop(a) AND leakproof(a) AND a < 7 AND a != 6;
@@ -2633,8 +2633,8 @@ SELECT * FROM t1 WHERE a=100; -- Nothing should have been changed to 100
EXPLAIN (VERBOSE, COSTS OFF)
UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
- QUERY PLAN
------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------
Update on public.t1
Update on public.t1 t1_1
Update on public.t11 t1_2
@@ -2646,7 +2646,7 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
-> Index Scan using t1_a_idx on public.t1 t1_1
Output: t1_1.a, t1_1.tableoid, t1_1.ctid
Index Cond: ((t1_1.a > 5) AND (t1_1.a = 8))
- Filter: ((SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
+ Filter: (EXISTS(SubPlan 1) AND snoop(t1_1.a) AND leakproof(t1_1.a))
SubPlan 1
-> Append
-> Seq Scan on public.t12 t12_1
@@ -2656,15 +2656,15 @@ UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
-> Index Scan using t11_a_idx on public.t11 t1_2
Output: t1_2.a, t1_2.tableoid, t1_2.ctid
Index Cond: ((t1_2.a > 5) AND (t1_2.a = 8))
- Filter: ((SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
+ Filter: (EXISTS(SubPlan 1) AND snoop(t1_2.a) AND leakproof(t1_2.a))
-> Index Scan using t12_a_idx on public.t12 t1_3
Output: t1_3.a, t1_3.tableoid, t1_3.ctid
Index Cond: ((t1_3.a > 5) AND (t1_3.a = 8))
- Filter: ((SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
+ Filter: (EXISTS(SubPlan 1) AND snoop(t1_3.a) AND leakproof(t1_3.a))
-> Index Scan using t111_a_idx on public.t111 t1_4
Output: t1_4.a, t1_4.tableoid, t1_4.ctid
Index Cond: ((t1_4.a > 5) AND (t1_4.a = 8))
- Filter: ((SubPlan 1) AND snoop(t1_4.a) AND leakproof(t1_4.a))
+ Filter: (EXISTS(SubPlan 1) AND snoop(t1_4.a) AND leakproof(t1_4.a))
(30 rows)
UPDATE v1 SET a=a+1 WHERE snoop(a) AND leakproof(a) AND a = 8;
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql
index bd2e39efd4..91f9e15c6a 100644
--- a/src/test/regress/sql/subselect.sql
+++ b/src/test/regress/sql/subselect.sql
@@ -82,6 +82,20 @@ SELECT f1 AS "Correlated Field"
WHERE (f1, f2) IN (SELECT f2, CAST(f3 AS int4) FROM SUBSELECT_TBL
WHERE f3 IS NOT NULL);
+-- Check ROWCOMPARE cases, both correlated and not
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT ROW(1, 2) = (SELECT f1, f2) AS eq FROM SUBSELECT_TBL;
+
+SELECT ROW(1, 2) = (SELECT f1, f2) AS eq FROM SUBSELECT_TBL;
+
+EXPLAIN (VERBOSE, COSTS OFF)
+SELECT ROW(1, 2) = (SELECT 3, 4) AS eq FROM SUBSELECT_TBL;
+
+SELECT ROW(1, 2) = (SELECT 3, 4) AS eq FROM SUBSELECT_TBL;
+
+SELECT ROW(1, 2) = (SELECT f1, f2 FROM SUBSELECT_TBL); -- error
+
-- Subselects without aliases
SELECT count FROM (SELECT COUNT(DISTINCT name) FROM road);
--
2.39.3
В списке pgsql-hackers по дате отправления:
Предыдущее
От: "David G. Johnston"Дата:
Сообщение: Re: PG12 change to DO UPDATE SET column references