Optimize SELECT * in EXISTS

From: Peter Eisentraut <peter(at)eisentraut(dot)org>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Optimize SELECT * in EXISTS
Date: 2026-02-23 08:21:15
Message-ID: 9b301c70-3909-4f0f-98ca-9e3c4d142f3e@eisentraut.org
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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?

Attachment Content-Type Size
0001-Optimize-SELECT-in-EXISTS.patch text/plain 3.7 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Rowley 2026-02-23 08:41:22 Re: Optimize SELECT * in EXISTS
Previous Message Bertrand Drouvot 2026-02-23 08:14:45 Re: Flush some statistics within running transactions