Message-ID: <cb0b9982-ca1a-4f03-9d7f-f9ca678be35c@postgrespro.ru>
Date: Tue, 25 Nov 2025 15:50:59 +0400
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Partial hash index is not used for implied qual.
To: Tom Lane <tgl@sss.pgh.pa.us>, David Rowley <dgrowleyml@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org
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>
Content-Language: en-US
From: Sergei Glukhov <s.glukhov@postgrespro.ru>
In-Reply-To: <1112416.1764036072@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/cb0b9982-ca1a-4f03-9d7f-f9ca678be35c%40postgrespro.ru>
Precedence: bulk

Hi,

On 11/25/25 6:01 AM, Tom Lane wrote:
> 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=
 generate_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) W=
HERE 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 widt=
h=3D4)
>     Recheck Cond: (x =3D 1)
>     ->  Bitmap Index Scan on partial_idx  (cost=3D0.00..24.07 rows=3D10=
 width=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 row=
s=3D10 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
>

Thanks for the fix. It seems there is another case for investigation:

DROP TABLE hash_partial;
CREATE TABLE hash_partial(x, y) AS
SELECT x, x + x as y from generate_series(1, 1000) as x;
ANALYZE hash_partial;
CREATE INDEX partial_idx=C2=A0 ON hash_partial USING hash(x) WHERE x =3D =
1;
SET enable_seqscan TO 0;
EXPLAIN SELECT x FROM hash_partial WHERE x =3D 1 and y < 0;
-------------------------------------------------------------------------=
-------
Seq Scan on hash_partial=C2=A0 (cost=3D0.00..23.00 rows=3D1 width=3D4)
 =C2=A0=C2=A0 Disabled: true
 =C2=A0=C2=A0 Filter: ((y < 0) AND (x =3D 1))
(3 rows)


 =C2=A0Regarding strangeness of the cost,
 =C2=A0cost is depends on numIndexPages and
 =C2=A0in genericcostestimate() we calulate numIndexPages:

 =C2=A0numIndexPages =3D ceil(numIndexTuples * index->pages / index->tupl=
es);

 =C2=A0For non-partial index index->pages =3D 6 and index->tuples =3D 100=
0
 =C2=A0and for partial index index->pages =3D 6 and index->tuples =3D 10.
 =C2=A0Number of pages depends on index relation size and
 =C2=A0initial size is 6 * BLCKSZ for both, partial and non-partial hash =
indexes
 =C2=A0Initial size of the hash index relation, in turn,
 =C2=A0depends on total number of tuples in the table.

 =C2=A0Regards,
 =C2=A0Sergei Glukhov