I read in the SQL standard that SELECT * in EXISTS is not supposed to be
expanded to all columns, but only to an arbitrary literal. This
corresponds to the recommendation in the PostgreSQL documentation to
write EXISTS (SELECT 1 ...) instead. But not even our own tests and
example code use that latter convention consistently, so I think many
users don't know it or observe it either. So implementing that little
optimization for SELECT * seems reasonable.
The attached patch implements the transformation, meaning in EXISTS
(SELECT * FROM ...), the star is replaced by an empty select list
(taking advantage of the support for zero-column tables in PostgreSQL).
There are plenty of tests involving this construct, so I didn't add any
more explicit tests. (But it might be worth adding a test involving
column privileges.)
Thoughts?
From 5391f1b8a8f8a7bb2fc0e3c6fc355fc0a147849d Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <[email protected]>
Date: Mon, 23 Feb 2026 08:43:39 +0100
Subject: [PATCH] Optimize SELECT * in EXISTS
Transform a SELECT * inside an EXISTS into an empty select list. This
allows writing
EXISTS (SELECT * FROM ...)
which is a common convention, without the overhead of expanding all
the columns.
The PostgreSQL documentation suggests writing EXISTS (SELECT 1 FROM
...), but not even all our tests and example code use that, so it's
probably not universally known.
The SQL standard also specifies this optimization. (It says that *
expands to an arbitrary literal, but we can use an empty select list.)
---
doc/src/sgml/func/func-subquery.sgml | 21 +++++++++++++++------
src/backend/parser/parse_expr.c | 27 +++++++++++++++++++++++++++
2 files changed, 42 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/func/func-subquery.sgml
b/doc/src/sgml/func/func-subquery.sgml
index a9f2b12e48c..d51d32baa9c 100644
--- a/doc/src/sgml/func/func-subquery.sgml
+++ b/doc/src/sgml/func/func-subquery.sgml
@@ -66,12 +66,21 @@ <title><literal>EXISTS</literal></title>
</para>
<para>
- Since the result depends only on whether any rows are returned,
- and not on the contents of those rows, the output list of the
- subquery is normally unimportant. A common coding convention is
- to write all <literal>EXISTS</literal> tests in the form
- <literal>EXISTS(SELECT 1 WHERE ...)</literal>. There are exceptions to
- this rule however, such as subqueries that use <token>INTERSECT</token>.
+ Since the result depends only on whether any rows are returned, and not on
+ the contents of those rows, the output list of the subquery is normally
+ unimportant. A possible coding convention is to write all
+ <literal>EXISTS</literal> tests in the form <literal>EXISTS(SELECT * FROM
+ ... WHERE ...)</literal>. The <literal>*</literal> is optimized away in
+ this case, and no columns are actually fetched. (This optimization only
+ applies to output lists consisting solely of a single asterisk.) Another
+ common convention is to write <literal>EXISTS(SELECT 1 FROM ... WHERE
+ ...)</literal> or some other dummy constant. (Another alternative is to
+ omit the select list altogether (<literal>EXISTS(SELECT FROM ... WHERE
+ ...</literal>), but that is a PostgreSQL extension and would make your code
+ less portable and arguably harder to read.) These conventions are not
+ always applicable, such as in subqueries that use <token>INTERSECT</token>,
+ where the output list of the subquery affects the result of the subquery
+ itself.
</para>
<para>
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index dcfe1acc4c3..cb2239cb250 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1888,6 +1888,33 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
pstate->p_hasSubLinks = true;
+ /*
+ * If EXISTS(SELECT * ...), remove the output list. (See SQL:2023
<query
+ * specification> SR 7.)
+ */
+ if (sublink->subLinkType == EXISTS_SUBLINK)
+ {
+ if (IsA(sublink->subselect, SelectStmt))
+ {
+ SelectStmt *s = castNode(SelectStmt,
sublink->subselect);
+
+ if (list_length(s->targetList) == 1)
+ {
+ ResTarget *rt = linitial_node(ResTarget,
s->targetList);
+
+ if (IsA(rt->val, ColumnRef) && !rt->name &&
!rt->indirection)
+ {
+ ColumnRef *cr = castNode(ColumnRef,
rt->val);
+
+ if (list_length(cr->fields) == 1 &&
IsA(linitial(cr->fields), A_Star))
+ {
+ s->targetList = NIL;
+ }
+ }
+ }
+ }
+ }
+
/*
* OK, let's transform the sub-SELECT.
*/
--
2.53.0
