Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vuRBz-004vTK-10 for pgsql-hackers@arkaria.postgresql.org; Mon, 23 Feb 2026 08:21:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vuRBx-00CMYC-2N for pgsql-hackers@arkaria.postgresql.org; Mon, 23 Feb 2026 08:21:21 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vuRBw-00CMY4-1s for pgsql-hackers@lists.postgresql.org; Mon, 23 Feb 2026 08:21:21 +0000 Received: from fhigh-a5-smtp.messagingengine.com ([103.168.172.156]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1vuRBt-00000000lhG-0mp8 for pgsql-hackers@postgresql.org; Mon, 23 Feb 2026 08:21:19 +0000 Received: from phl-compute-04.internal (phl-compute-04.internal [10.202.2.44]) by mailfhigh.phl.internal (Postfix) with ESMTP id 06DE91400058 for ; Mon, 23 Feb 2026 03:21:17 -0500 (EST) Received: from phl-frontend-03 ([10.202.2.162]) by phl-compute-04.internal (MEProxy); Mon, 23 Feb 2026 03:21:17 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=eisentraut.org; h=cc:content-type:content-type:date:date:from:from:in-reply-to :message-id:mime-version:reply-to:subject:subject:to:to; s=fm2; t=1771834877; x=1771921277; bh=KVcdLLrpsZJJ+t58J1ntE4YAp7X6HoDh P+74ODBxIUE=; b=cEdFVccbmBnz9rNDzkCdvD/sZ3HEoGp/2YWVeX4q/YdBsvX/ IbvSovwLur56PG5IzVsf3PSKhPhg2UGoTEvGJtWSNe1Rep7yZFobG0bznIA9A+4O GYmFPgcV80Xhpd2fKjvZpjE5xns46eHoG6E7HTlMkHozwvdPhoBlW/lF042v1D3i 8Io6ibSAL/Xe4AIKv5vSIb7oIkPRkmUoHjbpPCKIzFCLCHe2vTbhNHRF25NZ/dCC nX+ILcUiwiag2zK/VY003HOy0ln0nkHjcq5EiF4VAUBY4YNGFmda0FRSsCd3xq3Z eyVWhQHGtOczgp47UaHHyYshM8bZwSsC1q/gXg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:message-id :mime-version:reply-to:subject:subject:to:to:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm3; t=1771834877; x= 1771921277; bh=KVcdLLrpsZJJ+t58J1ntE4YAp7X6HoDhP+74ODBxIUE=; b=V 74hNk9OjDRaEBkSbniK0AT2+NdSz4f/ZgLM5wvjQbcl4BFzmV9fUm5Eeg2NUmgCQ tGvouky4Q4QvvSESzN4x4EuOJpYPEj5xCTgqjL4SaaiT1tD3rtuvuXYodSuh3K7q KuK27ffz6VFJJIRnZCkZNQILKLp7C9nV3QhQhr8PMRZkQi/0TmyXgRTAjZsZI2nS KpfMFwXcrHh7+di97w79Di7YEYtUa8IOyP7T7kJjEHrV8Rtp3NCXbuss6RRzCW2Q djTkI5y5uhxWWzmtYHJqVReH5Cxrgm16EAk+/NZ4C81TjIX3KKElWi5pNjB6whpt AALKdT1cakn3yNkdgmXDA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgeefgedrtddtgddvfeeijeefucetufdoteggodetrf dotffvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfurfetoffkrfgpnffqhgenuceu rghilhhouhhtmecufedttdenucenucfjughrpegtkfffgggfhffuvfesmhdtreertddvje enucfhrhhomheprfgvthgvrhcugfhishgvnhhtrhgruhhtuceophgvthgvrhesvghishgv nhhtrhgruhhtrdhorhhgqeenucggtffrrghtthgvrhhnpeehteekudegtdehtedttefhud ekjefgudeiheeiteffuedtteffkeekgfelvdffleenucevlhhushhtvghrufhiiigvpedt necurfgrrhgrmhepmhgrihhlfhhrohhmpehpvghtvghrsegvihhsvghnthhrrghuthdroh hrghdpnhgspghrtghpthhtohepuddpmhhouggvpehsmhhtphhouhhtpdhrtghpthhtohep phhgshhqlhdqhhgrtghkvghrshesphhoshhtghhrvghsqhhlrdhorhhg X-ME-Proxy: Feedback-ID: ie0a040ee:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA for ; Mon, 23 Feb 2026 03:21:16 -0500 (EST) Content-Type: multipart/mixed; boundary="------------ODrqoldPPG81cugW88pD0AMR" Message-ID: <9b301c70-3909-4f0f-98ca-9e3c4d142f3e@eisentraut.org> Date: Mon, 23 Feb 2026 09:21:15 +0100 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Content-Language: en-US From: Peter Eisentraut Subject: Optimize SELECT * in EXISTS To: pgsql-hackers List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------ODrqoldPPG81cugW88pD0AMR Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit 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? --------------ODrqoldPPG81cugW88pD0AMR Content-Type: text/plain; charset=UTF-8; name="0001-Optimize-SELECT-in-EXISTS.patch" Content-Disposition: attachment; filename="0001-Optimize-SELECT-in-EXISTS.patch" Content-Transfer-Encoding: base64 RnJvbSA1MzkxZjFiOGE4ZjhhN2JiMmZjMGUzYzZmYzM1NWZjMGExNDc4NDlkIE1vbiBTZXAg MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBQZXRlciBFaXNlbnRyYXV0IDxwZXRlckBlaXNlbnRy YXV0Lm9yZz4KRGF0ZTogTW9uLCAyMyBGZWIgMjAyNiAwODo0MzozOSArMDEwMApTdWJqZWN0 OiBbUEFUQ0hdIE9wdGltaXplIFNFTEVDVCAqIGluIEVYSVNUUwoKVHJhbnNmb3JtIGEgU0VM RUNUICogaW5zaWRlIGFuIEVYSVNUUyBpbnRvIGFuIGVtcHR5IHNlbGVjdCBsaXN0LiAgVGhp cwphbGxvd3Mgd3JpdGluZwoKICAgIEVYSVNUUyAoU0VMRUNUICogRlJPTSAuLi4pCgp3aGlj aCBpcyBhIGNvbW1vbiBjb252ZW50aW9uLCB3aXRob3V0IHRoZSBvdmVyaGVhZCBvZiBleHBh bmRpbmcgYWxsCnRoZSBjb2x1bW5zLgoKVGhlIFBvc3RncmVTUUwgZG9jdW1lbnRhdGlvbiBz dWdnZXN0cyB3cml0aW5nIEVYSVNUUyAoU0VMRUNUIDEgRlJPTQouLi4pLCBidXQgbm90IGV2 ZW4gYWxsIG91ciB0ZXN0cyBhbmQgZXhhbXBsZSBjb2RlIHVzZSB0aGF0LCBzbyBpdCdzCnBy b2JhYmx5IG5vdCB1bml2ZXJzYWxseSBrbm93bi4KClRoZSBTUUwgc3RhbmRhcmQgYWxzbyBz cGVjaWZpZXMgdGhpcyBvcHRpbWl6YXRpb24uICAoSXQgc2F5cyB0aGF0ICoKZXhwYW5kcyB0 byBhbiBhcmJpdHJhcnkgbGl0ZXJhbCwgYnV0IHdlIGNhbiB1c2UgYW4gZW1wdHkgc2VsZWN0 IGxpc3QuKQotLS0KIGRvYy9zcmMvc2dtbC9mdW5jL2Z1bmMtc3VicXVlcnkuc2dtbCB8IDIx ICsrKysrKysrKysrKysrKy0tLS0tLQogc3JjL2JhY2tlbmQvcGFyc2VyL3BhcnNlX2V4cHIu YyAgICAgIHwgMjcgKysrKysrKysrKysrKysrKysrKysrKysrKysrCiAyIGZpbGVzIGNoYW5n ZWQsIDQyIGluc2VydGlvbnMoKyksIDYgZGVsZXRpb25zKC0pCgpkaWZmIC0tZ2l0IGEvZG9j L3NyYy9zZ21sL2Z1bmMvZnVuYy1zdWJxdWVyeS5zZ21sIGIvZG9jL3NyYy9zZ21sL2Z1bmMv ZnVuYy1zdWJxdWVyeS5zZ21sCmluZGV4IGE5ZjJiMTJlNDhjLi5kNTFkMzJiYWE5YyAxMDA2 NDQKLS0tIGEvZG9jL3NyYy9zZ21sL2Z1bmMvZnVuYy1zdWJxdWVyeS5zZ21sCisrKyBiL2Rv Yy9zcmMvc2dtbC9mdW5jL2Z1bmMtc3VicXVlcnkuc2dtbApAQCAtNjYsMTIgKzY2LDIxIEBA IDx0aXRsZT48bGl0ZXJhbD5FWElTVFM8L2xpdGVyYWw+PC90aXRsZT4KICAgPC9wYXJhPgog CiAgIDxwYXJhPgotICAgU2luY2UgdGhlIHJlc3VsdCBkZXBlbmRzIG9ubHkgb24gd2hldGhl ciBhbnkgcm93cyBhcmUgcmV0dXJuZWQsCi0gICBhbmQgbm90IG9uIHRoZSBjb250ZW50cyBv ZiB0aG9zZSByb3dzLCB0aGUgb3V0cHV0IGxpc3Qgb2YgdGhlCi0gICBzdWJxdWVyeSBpcyBu b3JtYWxseSB1bmltcG9ydGFudC4gIEEgY29tbW9uIGNvZGluZyBjb252ZW50aW9uIGlzCi0g ICB0byB3cml0ZSBhbGwgPGxpdGVyYWw+RVhJU1RTPC9saXRlcmFsPiB0ZXN0cyBpbiB0aGUg Zm9ybQotICAgPGxpdGVyYWw+RVhJU1RTKFNFTEVDVCAxIFdIRVJFIC4uLik8L2xpdGVyYWw+ LiAgVGhlcmUgYXJlIGV4Y2VwdGlvbnMgdG8KLSAgIHRoaXMgcnVsZSBob3dldmVyLCBzdWNo IGFzIHN1YnF1ZXJpZXMgdGhhdCB1c2UgPHRva2VuPklOVEVSU0VDVDwvdG9rZW4+LgorICAg U2luY2UgdGhlIHJlc3VsdCBkZXBlbmRzIG9ubHkgb24gd2hldGhlciBhbnkgcm93cyBhcmUg cmV0dXJuZWQsIGFuZCBub3Qgb24KKyAgIHRoZSBjb250ZW50cyBvZiB0aG9zZSByb3dzLCB0 aGUgb3V0cHV0IGxpc3Qgb2YgdGhlIHN1YnF1ZXJ5IGlzIG5vcm1hbGx5CisgICB1bmltcG9y dGFudC4gIEEgcG9zc2libGUgY29kaW5nIGNvbnZlbnRpb24gaXMgdG8gd3JpdGUgYWxsCisg ICA8bGl0ZXJhbD5FWElTVFM8L2xpdGVyYWw+IHRlc3RzIGluIHRoZSBmb3JtIDxsaXRlcmFs PkVYSVNUUyhTRUxFQ1QgKiBGUk9NCisgICAuLi4gV0hFUkUgLi4uKTwvbGl0ZXJhbD4uICBU aGUgPGxpdGVyYWw+KjwvbGl0ZXJhbD4gaXMgb3B0aW1pemVkIGF3YXkgaW4KKyAgIHRoaXMg Y2FzZSwgYW5kIG5vIGNvbHVtbnMgYXJlIGFjdHVhbGx5IGZldGNoZWQuICAoVGhpcyBvcHRp bWl6YXRpb24gb25seQorICAgYXBwbGllcyB0byBvdXRwdXQgbGlzdHMgY29uc2lzdGluZyBz b2xlbHkgb2YgYSBzaW5nbGUgYXN0ZXJpc2suKSAgQW5vdGhlcgorICAgY29tbW9uIGNvbnZl bnRpb24gaXMgdG8gd3JpdGUgPGxpdGVyYWw+RVhJU1RTKFNFTEVDVCAxIEZST00gLi4uIFdI RVJFCisgICAuLi4pPC9saXRlcmFsPiBvciBzb21lIG90aGVyIGR1bW15IGNvbnN0YW50LiAg KEFub3RoZXIgYWx0ZXJuYXRpdmUgaXMgdG8KKyAgIG9taXQgdGhlIHNlbGVjdCBsaXN0IGFs dG9nZXRoZXIgKDxsaXRlcmFsPkVYSVNUUyhTRUxFQ1QgRlJPTSAuLi4gV0hFUkUKKyAgIC4u LjwvbGl0ZXJhbD4pLCBidXQgdGhhdCBpcyBhIFBvc3RncmVTUUwgZXh0ZW5zaW9uIGFuZCB3 b3VsZCBtYWtlIHlvdXIgY29kZQorICAgbGVzcyBwb3J0YWJsZSBhbmQgYXJndWFibHkgaGFy ZGVyIHRvIHJlYWQuKSAgVGhlc2UgY29udmVudGlvbnMgYXJlIG5vdAorICAgYWx3YXlzIGFw cGxpY2FibGUsIHN1Y2ggYXMgaW4gc3VicXVlcmllcyB0aGF0IHVzZSA8dG9rZW4+SU5URVJT RUNUPC90b2tlbj4sCisgICB3aGVyZSB0aGUgb3V0cHV0IGxpc3Qgb2YgdGhlIHN1YnF1ZXJ5 IGFmZmVjdHMgdGhlIHJlc3VsdCBvZiB0aGUgc3VicXVlcnkKKyAgIGl0c2VsZi4KICAgPC9w YXJhPgogCiAgIDxwYXJhPgpkaWZmIC0tZ2l0IGEvc3JjL2JhY2tlbmQvcGFyc2VyL3BhcnNl X2V4cHIuYyBiL3NyYy9iYWNrZW5kL3BhcnNlci9wYXJzZV9leHByLmMKaW5kZXggZGNmZTFh Y2M0YzMuLmNiMjIzOWNiMjUwIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9wYXJzZXIvcGFy c2VfZXhwci5jCisrKyBiL3NyYy9iYWNrZW5kL3BhcnNlci9wYXJzZV9leHByLmMKQEAgLTE4 ODgsNiArMTg4OCwzMyBAQCB0cmFuc2Zvcm1TdWJMaW5rKFBhcnNlU3RhdGUgKnBzdGF0ZSwg U3ViTGluayAqc3VibGluaykKIAogCXBzdGF0ZS0+cF9oYXNTdWJMaW5rcyA9IHRydWU7CiAK KwkvKgorCSAqIElmIEVYSVNUUyhTRUxFQ1QgKiAuLi4pLCByZW1vdmUgdGhlIG91dHB1dCBs aXN0LiAgKFNlZSBTUUw6MjAyMyA8cXVlcnkKKwkgKiBzcGVjaWZpY2F0aW9uPiBTUiA3LikK KwkgKi8KKwlpZiAoc3VibGluay0+c3ViTGlua1R5cGUgPT0gRVhJU1RTX1NVQkxJTkspCisJ eworCQlpZiAoSXNBKHN1YmxpbmstPnN1YnNlbGVjdCwgU2VsZWN0U3RtdCkpCisJCXsKKwkJ CVNlbGVjdFN0bXQgKnMgPSBjYXN0Tm9kZShTZWxlY3RTdG10LCBzdWJsaW5rLT5zdWJzZWxl Y3QpOworCisJCQlpZiAobGlzdF9sZW5ndGgocy0+dGFyZ2V0TGlzdCkgPT0gMSkKKwkJCXsK KwkJCQlSZXNUYXJnZXQgICpydCA9IGxpbml0aWFsX25vZGUoUmVzVGFyZ2V0LCBzLT50YXJn ZXRMaXN0KTsKKworCQkJCWlmIChJc0EocnQtPnZhbCwgQ29sdW1uUmVmKSAmJiAhcnQtPm5h bWUgJiYgIXJ0LT5pbmRpcmVjdGlvbikKKwkJCQl7CisJCQkJCUNvbHVtblJlZiAgKmNyID0g Y2FzdE5vZGUoQ29sdW1uUmVmLCBydC0+dmFsKTsKKworCQkJCQlpZiAobGlzdF9sZW5ndGgo Y3ItPmZpZWxkcykgPT0gMSAmJiBJc0EobGluaXRpYWwoY3ItPmZpZWxkcyksIEFfU3Rhcikp CisJCQkJCXsKKwkJCQkJCXMtPnRhcmdldExpc3QgPSBOSUw7CisJCQkJCX0KKwkJCQl9CisJ CQl9CisJCX0KKwl9CisKIAkvKgogCSAqIE9LLCBsZXQncyB0cmFuc2Zvcm0gdGhlIHN1Yi1T RUxFQ1QuCiAJICovCi0tIAoyLjUzLjAKCg== --------------ODrqoldPPG81cugW88pD0AMR--