From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Sergei Glukhov <s.glukhov@postgrespro.ru>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: Partial hash index is not used for implied qual.
Date: Mon, 24 Nov 2025 21:01:12 -0500
Message-ID: <1112416.1764036072@sss.pgh.pa.us> (raw)
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>
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=# CREATE TABLE hash_partial(x) AS SELECT x % 100 as y from generate_series(1, 1000) as x;
SELECT 1000
regression=# ANALYZE hash_partial;
ANALYZE
regression=# CREATE INDEX partial_idx ON hash_partial USING hash(x) WHERE x = 1;
CREATE INDEX
regression=# set enable_seqscan TO 0; -- else we'll go for a seqscan
SET
regression=# EXPLAIN SELECT x FROM hash_partial WHERE x = 1;
QUERY PLAN
----------------------------------------------------------------------------
Bitmap Heap Scan on hash_partial (cost=24.08..32.56 rows=10 width=4)
Recheck Cond: (x = 1)
-> Bitmap Index Scan on partial_idx (cost=0.00..24.07 rows=10 width=0)
Index Cond: (x = 1)
(4 rows)
regression=# drop index partial_idx;
DROP INDEX
regression=# CREATE INDEX ON hash_partial USING hash(x);
CREATE INDEX
regression=# EXPLAIN SELECT x FROM hash_partial WHERE x = 1;
QUERY PLAN
----------------------------------------------------------------------------------
Bitmap Heap Scan on hash_partial (cost=4.08..12.56 rows=10 width=4)
Recheck Cond: (x = 1)
-> Bitmap Index Scan on hash_partial_x_idx (cost=0.00..4.08 rows=10 width=0)
Index Cond: (x = 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
Attachments:wip-fix-partial-hash-index-scan.patch (text/x-diff, 1.6K) downloaddiff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.cindex 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 = (IndexOptInfo *) lfirst(lc);
+ List *newrestrictinfo;
ListCell *lcr;
if (index->indpred == 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 = NIL;+ /* Else compute new indrestrictinfo as the non-implied quals */+ newrestrictinfo = NIL;
foreach(lcr, rel->baserestrictinfo)
{
RestrictInfo *rinfo = (RestrictInfo *) lfirst(lcr);
@@ -4061,8 +4062,18 @@ check_index_predicates(PlannerInfo *root, RelOptInfo *rel)
if (contain_mutable_functions((Node *) rinfo->clause) ||
!predicate_implied_by(list_make1(rinfo->clause),
index->indpred, false))
- index->indrestrictinfo = lappend(index->indrestrictinfo, rinfo);+ newrestrictinfo = 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 == NIL && !index->amoptionalkey))+ index->indrestrictinfo = newrestrictinfo;
}
}
=
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: pgsql-hackers@postgresql.org
Cc: tgl@sss.pgh.pa.us, dgrowleyml@gmail.com, s.glukhov@postgrespro.ru, pgsql-hackers@lists.postgresql.org
Subject: Re: Partial hash index is not used for implied qual.
In-Reply-To: <1112416.1764036072@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox