public inbox for [email protected]
help / color / mirror / Atom feedFrom: Peter Eisentraut <[email protected]>
To: pgsql-hackers <[email protected]>
Subject: Optimize SELECT * in EXISTS
Date: Mon, 23 Feb 2026 09:21:15 +0100
Message-ID: <[email protected]> (raw)
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
Attachments:
[text/plain] 0001-Optimize-SELECT-in-EXISTS.patch (3.7K, ../[email protected]/2-0001-Optimize-SELECT-in-EXISTS.patch)
download | inline diff:
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
view thread (3+ messages) latest in thread
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: [email protected]
Cc: [email protected]
Subject: Re: Optimize SELECT * in EXISTS
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox