public inbox for [email protected]
help / color / mirror / Atom feed[PATCH] Allow SJE to recognize GiST-backed temporal primary keys
2+ messages / 2 participants
[nested] [flat]
* [PATCH] Allow SJE to recognize GiST-backed temporal primary keys
@ 2026-04-21 19:18 SATYANARAYANA NARLAPURAM <[email protected]>
2026-04-22 01:54 ` Re: [PATCH] Allow SJE to recognize GiST-backed temporal primary keys David Rowley <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: SATYANARAYANA NARLAPURAM @ 2026-04-21 19:18 UTC (permalink / raw)
To: PostgreSQL Hackers <[email protected]>; Paul A Jungwirth <[email protected]>
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [PATCH] Allow SJE to recognize GiST-backed temporal primary keys
2026-04-21 19:18 [PATCH] Allow SJE to recognize GiST-backed temporal primary keys SATYANARAYANA NARLAPURAM <[email protected]>
@ 2026-04-22 01:54 ` David Rowley <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: David Rowley @ 2026-04-22 01:54 UTC (permalink / raw)
To: SATYANARAYANA NARLAPURAM <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>; Paul A Jungwirth <[email protected]>
On Wed, 22 Apr 2026 at 07:18, SATYANARAYANA NARLAPURAM
<[email protected]> wrote:
> 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.
Please add to the 20-1 CF [1].
David
[1] https://commitfest.postgresql.org/59/
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-04-22 01:54 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-04-21 19:18 [PATCH] Allow SJE to recognize GiST-backed temporal primary keys SATYANARAYANA NARLAPURAM <[email protected]>
2026-04-22 01:54 ` David Rowley <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox