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: 
 <CAApHDvrsnR+=CU4wE1haWWO+ZjjN1m22joLqifrJgXv1mf+6yQ@mail.gmail.com>
References: <e200bf38-6b45-446a-83fd-48617211feff@postgrespro.ru>
 <CAApHDvpVazvK7jAjpMWW5FYR4RQZ2NT8HidS03un8T+Ha8ZQDw@mail.gmail.com>
 <1106189.1764033798@sss.pgh.pa.us> <1112416.1764036072@sss.pgh.pa.us>
 <CAApHDvpVwXQgiatD_FM_F3fYKU-j1kqPeTQn-xeRptc2nyGRVw@mail.gmail.com>
 <1615744.1764181557@sss.pgh.pa.us>
 <CAApHDvrsnR+=CU4wE1haWWO+ZjjN1m22joLqifrJgXv1mf+6yQ@mail.gmail.com>
Comments: In-reply-to David Rowley <dgrowleyml@gmail.com>
	message dated "Thu, 27 Nov 2025 16:01:14 +1300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <36826.1764216283.1@sss.pgh.pa.us>
Date: Wed, 26 Nov 2025 23:04:43 -0500
Message-ID: <36827.1764216283@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/36827.1764216283%40sss.pgh.pa.us>
Precedence: bulk

David Rowley <dgrowleyml@gmail.com> writes:
> On Thu, 27 Nov 2025 at 07:26, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> I checked the costing calculations and it's basically that
>> genericcostestimate doesn't understand about hash buckets.

> I assume you must mean using your "x % 100" case rather than Sergei's case.

Right.

>> ... tuples in the index, so it supposes that that means we'll visit
>> 1/100th of the index's pages (rounded up to 1).

> I'm not so clear on why this is bad. The index has 1000 tuples on 6
> pages and we want to scan 1% of the rows in the index. As a result,
> genericcostestimate() calculates that's 1 page. How many pages would
> you expect to scan?

To be clear, neither of genericcostestimate's estimates are bad as
a generic estimate.  And I'm not even sure that we could do better
with a hash-aware estimate implemented in hashcostestimate.  I think
the key thing about this test case is that the partial index ends
up with all its entries in one hash bucket.  I'm not sure that we
could reasonably expect to know that in the cost estimator.  Even
if we could, should we really expend a lot of effort on the case?
It's a textbook example of when you should not use a hash index.

I'm interested in fixing this can't-generate-this-plan-shape bug
because it probably impacts more-realistic use-cases.  But I
think the cost estimation problem is probably specific to cases
where you shouldn't have used a hash index, so I'm okay with
ignoring that part.  Until more evidence arrives, anyway.

			regards, tom lane