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