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
commit 89e8f5db08b7cf045b743b7d52b0b3754afff405 Author: Sergei Glukhov <[email protected]> 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;
