Optimize SELECT * in EXISTS
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?
Attachments:
0001-Optimize-SELECT-in-EXISTS.patchtext/plain; charset=UTF-8; name=0001-Optimize-SELECT-in-EXISTS.patchDownload+42-7
On Mon, 23 Feb 2026 at 21:21, Peter Eisentraut <peter@eisentraut.org> wrote:
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.
Thoughts?
Don't we already do this in simplify_EXISTS_query()?
David
On 23.02.26 09:41, David Rowley wrote:
On Mon, 23 Feb 2026 at 21:21, Peter Eisentraut <peter@eisentraut.org> wrote:
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.Thoughts?
Don't we already do this in simplify_EXISTS_query()?
It appears so, but then I think we should update the documentation along
the lines I showed, because I found it misleading about this. New
docs-only patch attached.
Attachments:
v2-0001-doc-Document-SELECT-in-EXISTS-optimizations.patchtext/plain; charset=UTF-8; name=v2-0001-doc-Document-SELECT-in-EXISTS-optimizations.patchDownload+14-4
Peter Eisentraut <peter@eisentraut.org> writes:
It appears so, but then I think we should update the documentation along
the lines I showed, because I found it misleading about this. New
docs-only patch attached.
+1 for being more explicit here, but that specific wording reads a
little awkwardly to me. What do you think of the attached?
(The new code comments are fine, I didn't touch them.)
regards, tom lane
Attachments:
v3-0001-doc-Document-SELECT-in-EXISTS-optimizations.patchtext/x-diff; charset=us-ascii; name=v3-0001-doc-Document-SELECT-in-EXISTS-optimizations.patchDownload+15-3
On 24.02.26 22:07, Tom Lane wrote:
Peter Eisentraut <peter@eisentraut.org> writes:
It appears so, but then I think we should update the documentation along
the lines I showed, because I found it misleading about this. New
docs-only patch attached.+1 for being more explicit here, but that specific wording reads a
little awkwardly to me. What do you think of the attached?
Sounds good, thanks.