public inbox for [email protected]
help / color / mirror / Atom feedFrom: SATYANARAYANA NARLAPURAM <[email protected]>
To: PostgreSQL Hackers <[email protected]>
To: Paul A Jungwirth <[email protected]>
Subject: [PATCH] Allow SJE to recognize GiST-backed temporal primary keys
Date: Tue, 21 Apr 2026 12:18:37 -0700
Message-ID: <CAHg+QDeXwdOzrmb-sSATK4whbyhOgzyCGN+bY=YXU9qOzJaWSg@mail.gmail.com> (raw)
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
Attachments:
[application/octet-stream] 0001-Allow-SJE-to-recognize-GiST-backed-temporal-primary-.patch (4.4K, 3-0001-Allow-SJE-to-recognize-GiST-backed-temporal-primary-.patch)
download | inline diff:
From f7b7d515f87ffcc8034a953648701d5775530fc7 Mon Sep 17 00:00:00 2001
From: satyanarayana narlapuram <[email protected]>
Date: Tue, 21 Apr 2026 16:56:18 +0000
Subject: [PATCH] Allow SJE to recognize GiST-backed temporal primary keys
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.
---
src/backend/optimizer/path/indxpath.c | 11 +++++++++-
src/test/regress/expected/join.out | 30 +++++++++++++++++++++++++++
src/test/regress/sql/join.sql | 21 +++++++++++++++++++
3 files changed, 61 insertions(+), 1 deletion(-)
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index 430e06dc..990ada34 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -4227,8 +4227,17 @@ relation_has_unique_index_for(PlannerInfo *root, RelOptInfo *rel,
* index opfamily, else it is not asserting the right kind of
* equality behavior for this index. We check this first
* since it's probably cheaper than match_index_to_operand().
+ *
+ * For non-btree unique indexes (e.g., GiST-backed temporal
+ * primary keys created with WITHOUT OVERLAPS), the index
+ * opfamily won't appear in mergeopfamilies, which only lists
+ * btree opfamilies. Fall back to checking whether the
+ * clause's equality operator is directly a member of the
+ * index's opfamily.
*/
- if (!list_member_oid(rinfo->mergeopfamilies, ind->opfamily[c]))
+ if (!list_member_oid(rinfo->mergeopfamilies, ind->opfamily[c]) &&
+ !op_in_opfamily(castNode(OpExpr, rinfo->clause)->opno,
+ ind->opfamily[c]))
continue;
/*
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 78bf022f..cc72eb92 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -8202,6 +8202,36 @@ ON sj_t1.id = _t2t3t4.id;
-> Seq Scan on sj_t1
(24 rows)
+-- SJE with GiST-backed temporal primary key (WITHOUT OVERLAPS).
+-- Use int4range for the scalar key since btree_gist may not be available.
+CREATE TABLE sj_temporal (
+ id int4range,
+ valid_at daterange NOT NULL,
+ val text,
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+-- Remove SJ: join covers the full temporal PK
+EXPLAIN (COSTS OFF)
+SELECT t1.val FROM sj_temporal t1
+ JOIN sj_temporal t2 ON t1.id = t2.id AND t1.valid_at = t2.valid_at;
+ QUERY PLAN
+----------------------------
+ Seq Scan on sj_temporal t2
+(1 row)
+
+-- Don't remove SJ: join only covers part of the temporal PK
+EXPLAIN (COSTS OFF)
+SELECT t1.val FROM sj_temporal t1
+ JOIN sj_temporal t2 ON t1.id = t2.id;
+ QUERY PLAN
+----------------------------------------------------------------
+ Nested Loop
+ -> Seq Scan on sj_temporal t1
+ -> Index Only Scan using sj_temporal_pkey on sj_temporal t2
+ Index Cond: (id = t1.id)
+(4 rows)
+
+DROP TABLE sj_temporal;
--
-- Test RowMarks-related code
--
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index fae19113..bbc7c15d 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -3215,6 +3215,27 @@ JOIN (
) _t2t3t4
ON sj_t1.id = _t2t3t4.id;
+-- SJE with GiST-backed temporal primary key (WITHOUT OVERLAPS).
+-- Use int4range for the scalar key since btree_gist may not be available.
+CREATE TABLE sj_temporal (
+ id int4range,
+ valid_at daterange NOT NULL,
+ val text,
+ PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)
+);
+
+-- Remove SJ: join covers the full temporal PK
+EXPLAIN (COSTS OFF)
+SELECT t1.val FROM sj_temporal t1
+ JOIN sj_temporal t2 ON t1.id = t2.id AND t1.valid_at = t2.valid_at;
+
+-- Don't remove SJ: join only covers part of the temporal PK
+EXPLAIN (COSTS OFF)
+SELECT t1.val FROM sj_temporal t1
+ JOIN sj_temporal t2 ON t1.id = t2.id;
+
+DROP TABLE sj_temporal;
+
--
-- Test RowMarks-related code
--
--
2.43.0
view thread (2+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: [PATCH] Allow SJE to recognize GiST-backed temporal primary keys
In-Reply-To: <CAHg+QDeXwdOzrmb-sSATK4whbyhOgzyCGN+bY=YXU9qOzJaWSg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox