From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
cc: Sergei Glukhov <s.glukhov@postgrespro.ru>,
        pgsql-hackers@lists.postgresql.org
Subject: Re: Partial hash index is not used for implied qual.
In-reply-to: <1106189.1764033798@sss.pgh.pa.us>
References: <e200bf38-6b45-446a-83fd-48617211feff@postgrespro.ru>
 <CAApHDvpVazvK7jAjpMWW5FYR4RQZ2NT8HidS03un8T+Ha8ZQDw@mail.gmail.com>
 <1106189.1764033798@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Mon, 24 Nov 2025 20:23:18 -0500"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <1112333.1764036036.0@sss.pgh.pa.us>
Date: Mon, 24 Nov 2025 21:01:12 -0500
Message-ID: <1112416.1764036072@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1112416.1764036072%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1112333.1764036036.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable

I wrote:
> Wouldn't it be better to handle it more like the is_target_rel logic
> a few lines further up?

Actually, after thinking a bit longer, it'd be better to do something
like the attached so that we don't keep redundant quals unless they'd
*all* be excluded.

There's definitely something fishy about the costing though.
I experimented with this variant of Sergei's example:

regression=3D# CREATE TABLE hash_partial(x) AS SELECT x % 100 as y from ge=
nerate_series(1, 1000) as x;
SELECT 1000
regression=3D# ANALYZE hash_partial;
ANALYZE
regression=3D# CREATE INDEX partial_idx ON hash_partial USING hash(x) WHER=
E x =3D 1;
CREATE INDEX
regression=3D# set enable_seqscan TO 0;  -- else we'll go for a seqscan
SET
regression=3D# EXPLAIN SELECT x FROM hash_partial WHERE x =3D 1;
                                 QUERY PLAN                               =
  =

--------------------------------------------------------------------------=
--
 Bitmap Heap Scan on hash_partial  (cost=3D24.08..32.56 rows=3D10 width=3D=
4)
   Recheck Cond: (x =3D 1)
   ->  Bitmap Index Scan on partial_idx  (cost=3D0.00..24.07 rows=3D10 wid=
th=3D0)
         Index Cond: (x =3D 1)
(4 rows)

regression=3D# drop index partial_idx;
DROP INDEX
regression=3D# CREATE INDEX ON hash_partial USING hash(x);
CREATE INDEX
regression=3D# EXPLAIN SELECT x FROM hash_partial WHERE x =3D 1;
                                    QUERY PLAN                            =
      =

--------------------------------------------------------------------------=
--------
 Bitmap Heap Scan on hash_partial  (cost=3D4.08..12.56 rows=3D10 width=3D4=
)
   Recheck Cond: (x =3D 1)
   ->  Bitmap Index Scan on hash_partial_x_idx  (cost=3D0.00..4.08 rows=3D=
10 width=3D0)
         Index Cond: (x =3D 1)
(4 rows)

Why are we thinking that a non-partial index would be substantially
cheaper to scan?  That seems surely wrong, and it runs counter to my
intuition about why this fix is incomplete.  (I expected an unfair
bias towards the partial index, not against it.)

			regards, tom lane


------- =_aaaaaaaaaa0
Content-Type: text/x-diff; name="wip-fix-partial-hash-index-scan.patch";
	charset="us-ascii"
Content-ID: <1112333.1764036036.2@sss.pgh.pa.us>
Content-Description: wip-fix-partial-hash-index-scan.patch
Content-Transfer-Encoding: quoted-printable

diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer=
/path/indxpath.c
index c62e3f87724..c2687dec425 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -4038,6 +4038,7 @@ check_index_predicates(PlannerInfo *root, RelOptInfo=
 *rel)
 	foreach(lc, rel->indexlist)
 	{
 		IndexOptInfo *index =3D (IndexOptInfo *) lfirst(lc);
+		List	   *newrestrictinfo;
 		ListCell   *lcr;
 =

 		if (index->indpred =3D=3D NIL)
@@ -4051,8 +4052,8 @@ check_index_predicates(PlannerInfo *root, RelOptInfo=
 *rel)
 		if (is_target_rel)
 			continue;
 =

-		/* Else compute indrestrictinfo as the non-implied quals */
-		index->indrestrictinfo =3D NIL;
+		/* Else compute new indrestrictinfo as the non-implied quals */
+		newrestrictinfo =3D NIL;
 		foreach(lcr, rel->baserestrictinfo)
 		{
 			RestrictInfo *rinfo =3D (RestrictInfo *) lfirst(lcr);
@@ -4061,8 +4062,18 @@ check_index_predicates(PlannerInfo *root, RelOptInf=
o *rel)
 			if (contain_mutable_functions((Node *) rinfo->clause) ||
 				!predicate_implied_by(list_make1(rinfo->clause),
 									  index->indpred, false))
-				index->indrestrictinfo =3D lappend(index->indrestrictinfo, rinfo);
+				newrestrictinfo =3D lappend(newrestrictinfo, rinfo);
 		}
+
+		/*
+		 * If we excluded every qual as implied by the predicate, and the
+		 * index cannot do full-index scans, then it's better to leave
+		 * indrestrictinfo alone so that we can still build a scan on this
+		 * index.  XXX We will underestimate the cost of such an indexscan;
+		 * what can we do about that?
+		 */
+		if (!(newrestrictinfo =3D=3D NIL && !index->amoptionalkey))
+			index->indrestrictinfo =3D newrestrictinfo;
 	}
 }
 =


------- =_aaaaaaaaaa0--