Subject: Optimize SELECT * in EXISTS

From: Peter Eisentraut <[email-masked]>
Date: Mon, 23 Feb 2026 09:21:15 +0100

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-masked]>
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

---

From: David Rowley <[email-masked]>
Date: Mon, 23 Feb 2026 21:41:22 +1300

> Thoughts?

Don't we already do this in simplify_EXISTS_query()?

David