[Sergei Glukhov <s.glukhov@postgrespro.ru>]

Hi!

Partial hash index is not used if qual is an implied qual
since this qual is not added to indrestrictinfo and we cannot
get the keys needed to make hash index scan possible.
Suggested fix is to add implied qual for the indexes
which requires the presence of a key to scan the index.

How to repeat:

CREATE TABLE hash_partial(x) AS
        SELECT x as y from generate_series(1, 1000) as x;
ANALYZE hash_partial;
CREATE INDEX partial_idx ON hash_partial USING hash(x) WHERE x = 1;
EXPLAIN (COSTS OFF) SELECT x FROM hash_partial WHERE x = 1;
...
          QUERY PLAN
--------------------------
  Seq Scan on hash_partial
    Filter: (x = 1)
  ...

  Regards,
  Sergei Glukhov

Attachments:

partial_hash_index_with_implied_qual.patch (text/x-patch, 3.0K) download
commit 89e8f5db08b7cf045b743b7d52b0b3754afff405
Author: Sergei Glukhov <s.glukhov@postgrespro.ru>
Date:   Mon Nov 24 11:12:46 2025 +0400

    Partial hash index is not used for implied quals.
    
    Partial hash index is not used if qual is an implied qual
    since this qual is not added to indrestrictinfo and we cannot
    get the keys needed to make hash index scan possible.
    Suggested fix is to add implied qual to indrestrictinfo
    for the indexes that require a key to scan the index.

diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index c62e3f87724..0445bbf8775 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -4059,8 +4059,10 @@ check_index_predicates(PlannerInfo *root, RelOptInfo *rel)
 
 			/* predicate_implied_by() assumes first arg is immutable */
 			if (contain_mutable_functions((Node *) rinfo->clause) ||
-				!predicate_implied_by(list_make1(rinfo->clause),
-									  index->indpred, false))
+				(index->amoptionalkey &&
+				 !predicate_implied_by(list_make1(rinfo->clause),
+									   index->indpred, false)) ||
+				!index->amoptionalkey)
 				index->indrestrictinfo = lappend(index->indrestrictinfo, rinfo);
 		}
 	}
diff --git a/src/test/regress/expected/hash_index.out b/src/test/regress/expected/hash_index.out
index 0d4bdb2adef..5213742b199 100644
--- a/src/test/regress/expected/hash_index.out
+++ b/src/test/regress/expected/hash_index.out
@@ -333,3 +333,22 @@ CREATE INDEX hash_f8_index2 ON hash_f8_heap USING hash (random float8_ops)
 	WITH (fillfactor=101);
 ERROR:  value 101 out of bounds for option "fillfactor"
 DETAIL:  Valid values are between "10" and "100".
+-- Partial hash index with an implied qual.
+CREATE TABLE hash_partial(x) AS
+	SELECT x as y from generate_series(1, 1000) as x;
+ANALYZE hash_partial;
+CREATE INDEX partial_idx ON hash_partial USING hash(x) WHERE x = 1;
+EXPLAIN (COSTS OFF) SELECT x FROM hash_partial WHERE x = 1;
+                  QUERY PLAN                  
+----------------------------------------------
+ Index Scan using partial_idx on hash_partial
+   Index Cond: (x = 1)
+(2 rows)
+
+SELECT x FROM hash_partial WHERE x = 1;
+ x 
+---
+ 1
+(1 row)
+
+DROP TABLE hash_partial;
diff --git a/src/test/regress/sql/hash_index.sql b/src/test/regress/sql/hash_index.sql
index 219da829816..f4f1d195219 100644
--- a/src/test/regress/sql/hash_index.sql
+++ b/src/test/regress/sql/hash_index.sql
@@ -321,3 +321,13 @@ CREATE INDEX hash_f8_index2 ON hash_f8_heap USING hash (random float8_ops)
 	WITH (fillfactor=9);
 CREATE INDEX hash_f8_index2 ON hash_f8_heap USING hash (random float8_ops)
 	WITH (fillfactor=101);
+
+-- Partial hash index with an implied qual.
+CREATE TABLE hash_partial(x) AS
+	SELECT x as y from generate_series(1, 1000) as x;
+ANALYZE hash_partial;
+CREATE INDEX partial_idx ON hash_partial USING hash(x) WHERE x = 1;
+EXPLAIN (COSTS OFF) SELECT x FROM hash_partial WHERE x = 1;
+SELECT x FROM hash_partial WHERE x = 1;
+
+DROP TABLE hash_partial;