MIME-Version: 1.0
From: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Date: Tue, 21 Apr 2026 12:18:37 -0700
Message-ID: 
 <CAHg+QDeXwdOzrmb-sSATK4whbyhOgzyCGN+bY=YXU9qOzJaWSg@mail.gmail.com>
Subject: [PATCH] Allow SJE to recognize GiST-backed temporal primary keys
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
	Paul A Jungwirth <pj@illuminatedcomputing.com>
Content-Type: multipart/mixed; boundary="000000000000efab7d064ffd4802"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHg%2BQDeXwdOzrmb-sSATK4whbyhOgzyCGN%2BbY%3DYXU9qOzJaWSg%40mail.gmail.com>
Precedence: bulk

--000000000000efab7d064ffd4802
Content-Type: multipart/alternative; boundary="000000000000efab7c064ffd4800"

--000000000000efab7c064ffd4800
Content-Type: text/plain; charset="UTF-8"

Hi Hackers,

relation_has_unique_index_for() checks whether join clause equality
operators belong to the index's opfamily via mergeopfamilies.  Since
mergeopfamilies only lists btree opfamilies, this check always fails
for GiST-backed unique indexes such as those created by PRIMARY KEY
with WITHOUT OVERLAPS, preventing self-join elimination.

Fix by falling back to op_in_opfamily() when the mergeopfamilies check
fails.  The clause is already known to be a mergejoinable equality, so
confirming the operator is registered in the index's opfamily is
sufficient to prove that the index's uniqueness guarantee applies.

Attached a patch to fix this and added corresponding tests.

Thanks,
Satya

--000000000000efab7c064ffd4800
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi Hackers,<div><br></div><div>relation_has_unique_index_f=
or() checks whether join clause equality<br>operators belong to the index&#=
39;s opfamily via mergeopfamilies.=C2=A0 Since<br>mergeopfamilies only list=
s btree opfamilies, this check always fails<br>for GiST-backed unique index=
es such as those created by PRIMARY KEY<br>with WITHOUT OVERLAPS, preventin=
g self-join elimination.<br><br>Fix by falling back to op_in_opfamily() whe=
n the mergeopfamilies check<br>fails.=C2=A0 The clause is already known to =
be a mergejoinable equality, so<br>confirming the operator is registered in=
 the index&#39;s opfamily is<br>sufficient to prove that the index&#39;s un=
iqueness guarantee applies.<br></div><div><br></div><div>Attached=C2=A0a pa=
tch to fix this and added corresponding tests.</div><div><br></div><div>Tha=
nks,</div><div>Satya</div></div>

--000000000000efab7c064ffd4800--

--000000000000efab7d064ffd4802
Content-Type: application/octet-stream;
	name="0001-Allow-SJE-to-recognize-GiST-backed-temporal-primary-.patch"
Content-Disposition: attachment;
	filename="0001-Allow-SJE-to-recognize-GiST-backed-temporal-primary-.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mo90apf90>
X-Attachment-Id: f_mo90apf90

RnJvbSBmN2I3ZDUxNWY4N2ZmY2M4MDM0YTk1MzY0ODcwMWQ1Nzc1NTMwZmM3IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBzYXR5YW5hcmF5YW5hIG5hcmxhcHVyYW0gPHNhdHlhbmFybGFw
dXJhbUBnbWFpbC5jb20+CkRhdGU6IFR1ZSwgMjEgQXByIDIwMjYgMTY6NTY6MTggKzAwMDAKU3Vi
amVjdDogW1BBVENIXSBBbGxvdyBTSkUgdG8gcmVjb2duaXplIEdpU1QtYmFja2VkIHRlbXBvcmFs
IHByaW1hcnkga2V5cwoKcmVsYXRpb25faGFzX3VuaXF1ZV9pbmRleF9mb3IoKSBjaGVja3Mgd2hl
dGhlciBqb2luIGNsYXVzZSBlcXVhbGl0eQpvcGVyYXRvcnMgYmVsb25nIHRvIHRoZSBpbmRleCdz
IG9wZmFtaWx5IHZpYSBtZXJnZW9wZmFtaWxpZXMuICBTaW5jZQptZXJnZW9wZmFtaWxpZXMgb25s
eSBsaXN0cyBidHJlZSBvcGZhbWlsaWVzLCB0aGlzIGNoZWNrIGFsd2F5cyBmYWlscwpmb3IgR2lT
VC1iYWNrZWQgdW5pcXVlIGluZGV4ZXMgc3VjaCBhcyB0aG9zZSBjcmVhdGVkIGJ5IFBSSU1BUlkg
S0VZCndpdGggV0lUSE9VVCBPVkVSTEFQUywgcHJldmVudGluZyBzZWxmLWpvaW4gZWxpbWluYXRp
b24uCgpGaXggYnkgZmFsbGluZyBiYWNrIHRvIG9wX2luX29wZmFtaWx5KCkgd2hlbiB0aGUgbWVy
Z2VvcGZhbWlsaWVzIGNoZWNrCmZhaWxzLiAgVGhlIGNsYXVzZSBpcyBhbHJlYWR5IGtub3duIHRv
IGJlIGEgbWVyZ2Vqb2luYWJsZSBlcXVhbGl0eSwgc28KY29uZmlybWluZyB0aGUgb3BlcmF0b3Ig
aXMgcmVnaXN0ZXJlZCBpbiB0aGUgaW5kZXgncyBvcGZhbWlseSBpcwpzdWZmaWNpZW50IHRvIHBy
b3ZlIHRoYXQgdGhlIGluZGV4J3MgdW5pcXVlbmVzcyBndWFyYW50ZWUgYXBwbGllcy4KLS0tCiBz
cmMvYmFja2VuZC9vcHRpbWl6ZXIvcGF0aC9pbmR4cGF0aC5jIHwgMTEgKysrKysrKysrLQogc3Jj
L3Rlc3QvcmVncmVzcy9leHBlY3RlZC9qb2luLm91dCAgICB8IDMwICsrKysrKysrKysrKysrKysr
KysrKysrKysrKwogc3JjL3Rlc3QvcmVncmVzcy9zcWwvam9pbi5zcWwgICAgICAgICB8IDIxICsr
KysrKysrKysrKysrKysrKysKIDMgZmlsZXMgY2hhbmdlZCwgNjEgaW5zZXJ0aW9ucygrKSwgMSBk
ZWxldGlvbigtKQoKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL29wdGltaXplci9wYXRoL2luZHhw
YXRoLmMgYi9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGF0aC9pbmR4cGF0aC5jCmluZGV4IDQzMGUw
NmRjLi45OTBhZGEzNCAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BhdGgvaW5k
eHBhdGguYworKysgYi9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGF0aC9pbmR4cGF0aC5jCkBAIC00
MjI3LDggKzQyMjcsMTcgQEAgcmVsYXRpb25faGFzX3VuaXF1ZV9pbmRleF9mb3IoUGxhbm5lcklu
Zm8gKnJvb3QsIFJlbE9wdEluZm8gKnJlbCwKIAkJCQkgKiBpbmRleCBvcGZhbWlseSwgZWxzZSBp
dCBpcyBub3QgYXNzZXJ0aW5nIHRoZSByaWdodCBraW5kIG9mCiAJCQkJICogZXF1YWxpdHkgYmVo
YXZpb3IgZm9yIHRoaXMgaW5kZXguICBXZSBjaGVjayB0aGlzIGZpcnN0CiAJCQkJICogc2luY2Ug
aXQncyBwcm9iYWJseSBjaGVhcGVyIHRoYW4gbWF0Y2hfaW5kZXhfdG9fb3BlcmFuZCgpLgorCQkJ
CSAqCisJCQkJICogRm9yIG5vbi1idHJlZSB1bmlxdWUgaW5kZXhlcyAoZS5nLiwgR2lTVC1iYWNr
ZWQgdGVtcG9yYWwKKwkJCQkgKiBwcmltYXJ5IGtleXMgY3JlYXRlZCB3aXRoIFdJVEhPVVQgT1ZF
UkxBUFMpLCB0aGUgaW5kZXgKKwkJCQkgKiBvcGZhbWlseSB3b24ndCBhcHBlYXIgaW4gbWVyZ2Vv
cGZhbWlsaWVzLCB3aGljaCBvbmx5IGxpc3RzCisJCQkJICogYnRyZWUgb3BmYW1pbGllcy4gIEZh
bGwgYmFjayB0byBjaGVja2luZyB3aGV0aGVyIHRoZQorCQkJCSAqIGNsYXVzZSdzIGVxdWFsaXR5
IG9wZXJhdG9yIGlzIGRpcmVjdGx5IGEgbWVtYmVyIG9mIHRoZQorCQkJCSAqIGluZGV4J3Mgb3Bm
YW1pbHkuCiAJCQkJICovCi0JCQkJaWYgKCFsaXN0X21lbWJlcl9vaWQocmluZm8tPm1lcmdlb3Bm
YW1pbGllcywgaW5kLT5vcGZhbWlseVtjXSkpCisJCQkJaWYgKCFsaXN0X21lbWJlcl9vaWQocmlu
Zm8tPm1lcmdlb3BmYW1pbGllcywgaW5kLT5vcGZhbWlseVtjXSkgJiYKKwkJCQkJIW9wX2luX29w
ZmFtaWx5KGNhc3ROb2RlKE9wRXhwciwgcmluZm8tPmNsYXVzZSktPm9wbm8sCisJCQkJCQkJCQlp
bmQtPm9wZmFtaWx5W2NdKSkKIAkJCQkJY29udGludWU7CiAKIAkJCQkvKgpkaWZmIC0tZ2l0IGEv
c3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9qb2luLm91dCBiL3NyYy90ZXN0L3JlZ3Jlc3MvZXhw
ZWN0ZWQvam9pbi5vdXQKaW5kZXggNzhiZjAyMmYuLmNjNzJlYjkyIDEwMDY0NAotLS0gYS9zcmMv
dGVzdC9yZWdyZXNzL2V4cGVjdGVkL2pvaW4ub3V0CisrKyBiL3NyYy90ZXN0L3JlZ3Jlc3MvZXhw
ZWN0ZWQvam9pbi5vdXQKQEAgLTgyMDIsNiArODIwMiwzNiBAQCBPTiBzal90MS5pZCA9IF90MnQz
dDQuaWQ7CiAgICAtPiAgU2VxIFNjYW4gb24gc2pfdDEKICgyNCByb3dzKQogCistLSBTSkUgd2l0
aCBHaVNULWJhY2tlZCB0ZW1wb3JhbCBwcmltYXJ5IGtleSAoV0lUSE9VVCBPVkVSTEFQUykuCist
LSBVc2UgaW50NHJhbmdlIGZvciB0aGUgc2NhbGFyIGtleSBzaW5jZSBidHJlZV9naXN0IG1heSBu
b3QgYmUgYXZhaWxhYmxlLgorQ1JFQVRFIFRBQkxFIHNqX3RlbXBvcmFsICgKKyAgICBpZCBpbnQ0
cmFuZ2UsCisgICAgdmFsaWRfYXQgZGF0ZXJhbmdlIE5PVCBOVUxMLAorICAgIHZhbCB0ZXh0LAor
ICAgIFBSSU1BUlkgS0VZIChpZCwgdmFsaWRfYXQgV0lUSE9VVCBPVkVSTEFQUykKKyk7CistLSBS
ZW1vdmUgU0o6IGpvaW4gY292ZXJzIHRoZSBmdWxsIHRlbXBvcmFsIFBLCitFWFBMQUlOIChDT1NU
UyBPRkYpCitTRUxFQ1QgdDEudmFsIEZST00gc2pfdGVtcG9yYWwgdDEKKyAgICBKT0lOIHNqX3Rl
bXBvcmFsIHQyIE9OIHQxLmlkID0gdDIuaWQgQU5EIHQxLnZhbGlkX2F0ID0gdDIudmFsaWRfYXQ7
CisgICAgICAgICBRVUVSWSBQTEFOICAgICAgICAgCistLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tCisgU2VxIFNjYW4gb24gc2pfdGVtcG9yYWwgdDIKKygxIHJvdykKKworLS0gRG9uJ3QgcmVt
b3ZlIFNKOiBqb2luIG9ubHkgY292ZXJzIHBhcnQgb2YgdGhlIHRlbXBvcmFsIFBLCitFWFBMQUlO
IChDT1NUUyBPRkYpCitTRUxFQ1QgdDEudmFsIEZST00gc2pfdGVtcG9yYWwgdDEKKyAgICBKT0lO
IHNqX3RlbXBvcmFsIHQyIE9OIHQxLmlkID0gdDIuaWQ7CisgICAgICAgICAgICAgICAgICAgICAg
ICAgICBRVUVSWSBQTEFOICAgICAgICAgICAgICAgICAgICAgICAgICAgCistLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCisgTmVz
dGVkIExvb3AKKyAgIC0+ICBTZXEgU2NhbiBvbiBzal90ZW1wb3JhbCB0MQorICAgLT4gIEluZGV4
IE9ubHkgU2NhbiB1c2luZyBzal90ZW1wb3JhbF9wa2V5IG9uIHNqX3RlbXBvcmFsIHQyCisgICAg
ICAgICBJbmRleCBDb25kOiAoaWQgPSB0MS5pZCkKKyg0IHJvd3MpCisKK0RST1AgVEFCTEUgc2pf
dGVtcG9yYWw7CiAtLQogLS0gVGVzdCBSb3dNYXJrcy1yZWxhdGVkIGNvZGUKIC0tCmRpZmYgLS1n
aXQgYS9zcmMvdGVzdC9yZWdyZXNzL3NxbC9qb2luLnNxbCBiL3NyYy90ZXN0L3JlZ3Jlc3Mvc3Fs
L2pvaW4uc3FsCmluZGV4IGZhZTE5MTEzLi5iYmM3YzE1ZCAxMDA2NDQKLS0tIGEvc3JjL3Rlc3Qv
cmVncmVzcy9zcWwvam9pbi5zcWwKKysrIGIvc3JjL3Rlc3QvcmVncmVzcy9zcWwvam9pbi5zcWwK
QEAgLTMyMTUsNiArMzIxNSwyNyBAQCBKT0lOICgKIAkpIF90MnQzdDQKIE9OIHNqX3QxLmlkID0g
X3QydDN0NC5pZDsKIAorLS0gU0pFIHdpdGggR2lTVC1iYWNrZWQgdGVtcG9yYWwgcHJpbWFyeSBr
ZXkgKFdJVEhPVVQgT1ZFUkxBUFMpLgorLS0gVXNlIGludDRyYW5nZSBmb3IgdGhlIHNjYWxhciBr
ZXkgc2luY2UgYnRyZWVfZ2lzdCBtYXkgbm90IGJlIGF2YWlsYWJsZS4KK0NSRUFURSBUQUJMRSBz
al90ZW1wb3JhbCAoCisgICAgaWQgaW50NHJhbmdlLAorICAgIHZhbGlkX2F0IGRhdGVyYW5nZSBO
T1QgTlVMTCwKKyAgICB2YWwgdGV4dCwKKyAgICBQUklNQVJZIEtFWSAoaWQsIHZhbGlkX2F0IFdJ
VEhPVVQgT1ZFUkxBUFMpCispOworCistLSBSZW1vdmUgU0o6IGpvaW4gY292ZXJzIHRoZSBmdWxs
IHRlbXBvcmFsIFBLCitFWFBMQUlOIChDT1NUUyBPRkYpCitTRUxFQ1QgdDEudmFsIEZST00gc2pf
dGVtcG9yYWwgdDEKKyAgICBKT0lOIHNqX3RlbXBvcmFsIHQyIE9OIHQxLmlkID0gdDIuaWQgQU5E
IHQxLnZhbGlkX2F0ID0gdDIudmFsaWRfYXQ7CisKKy0tIERvbid0IHJlbW92ZSBTSjogam9pbiBv
bmx5IGNvdmVycyBwYXJ0IG9mIHRoZSB0ZW1wb3JhbCBQSworRVhQTEFJTiAoQ09TVFMgT0ZGKQor
U0VMRUNUIHQxLnZhbCBGUk9NIHNqX3RlbXBvcmFsIHQxCisgICAgSk9JTiBzal90ZW1wb3JhbCB0
MiBPTiB0MS5pZCA9IHQyLmlkOworCitEUk9QIFRBQkxFIHNqX3RlbXBvcmFsOworCiAtLQogLS0g
VGVzdCBSb3dNYXJrcy1yZWxhdGVkIGNvZGUKIC0tCi0tIAoyLjQzLjAKCg==
--000000000000efab7d064ffd4802--