MIME-Version: 1.0
References: <7db341e0-fbc6-4ec5-922c-11fdafe7be12@tantorlabs.com>
 <c5c7cf37-218d-405a-8dde-520aa313830a@gmail.com> <988e3168-6096-488a-bb42-787e1e8c21a4@tantorlabs.com>
 <21FF6A0B-886F-4132-B6A6-0F4E934B4CEE@Outlook.com> <8ee9d903-bb13-434c-8145-b3769bf2b3cb@tantorlabs.com>
 <ff7d491d-f084-4935-8417-c0b7285cdd89@gmail.com>
In-Reply-To: <ff7d491d-f084-4935-8417-c0b7285cdd89@gmail.com>
From: Tatsuya Kawata <kawatatatsuya0913@gmail.com>
Date: Sat, 7 Feb 2026 16:42:01 +0900
Message-ID: <CAHza6qdAH99C0xa27YDTixiNVFa99j90QYquUPAxL0JwahmggA@mail.gmail.com>
Subject: Re: Hash-based MCV matching for large IN-lists
To: David Geier <geidav.pg@gmail.com>
Cc: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>, 
	Chengpeng Yan <chengpeng_yan@outlook.com>, 
	"pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000b56d50064a370cd0"
Archived-At: <https://www.postgresql.org/message-id/CAHza6qdAH99C0xa27YDTixiNVFa99j90QYquUPAxL0JwahmggA%40mail.gmail.com>
Precedence: bulk

--000000000000b56d50064a370cd0
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi,

Thank you for this patch.
I've been studying how PostgreSQL handles selectivity estimation, and this
optimization for large IN-lists looks very useful.

I ran some tests for the special cases David mentioned:

- NULL + ALL: correctly returns selectivity =E2=89=88 0 (rows=3D1)
- isInequality: <> ALL estimates match NOT IN
- Cross-type: int =3D ANY(bigint[]) works correctly
- Duplicate values: IN (1,1,1,2,2,3) preserves existing behavior

I noticed a few minor points:

1. The comment in MCVInHashEntry struct contains a typo:
   "number of occurrences od current value" -> "of"

2. The ALL + NULL early-return logic appears in two places (lines 2579-2591
and 2644-2656). I initially considered consolidating this by checking for
NULL elements before building the hash table, but realized this would add
an extra loop in the common case where there are no NULLs.
   Perhaps a brief comment explaining why this check is duplicated (to
avoid the overhead of a separate NULL-scanning loop) would help future
readers understand the design choice?

3. Minor style suggestion: adding a brief SQL example in the header comment
(e.g., "WHERE x IN (1,2,3,...)" or "WHERE x =3D ANY(ARRAY[...])") might hel=
p
future readers quickly understand the use case.

Thanks again for working on this optimization. It's been very educational
to follow the discussion and understand how selectivity estimation works in
PostgreSQL.

Regards,
Tatsuya Kawata

--000000000000b56d50064a370cd0
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi,<br><br>Thank you for this patch.<br>I&#39;ve been stud=
ying how PostgreSQL handles selectivity estimation, and this optimization f=
or large IN-lists looks very useful.<br><br>I ran some tests for the specia=
l cases David mentioned:<br><br>- NULL + ALL: correctly returns selectivity=
 =E2=89=88 0 (rows=3D1)<br>- isInequality: &lt;&gt; ALL estimates match NOT=
 IN<br>- Cross-type: int =3D ANY(bigint[]) works correctly<br>- Duplicate v=
alues: IN (1,1,1,2,2,3) preserves existing behavior<br><br>I noticed a few =
minor points:<br><br>1. The comment in MCVInHashEntry struct contains a typ=
o:<br>=C2=A0 =C2=A0&quot;number of occurrences od current value&quot; -&gt;=
 &quot;of&quot;<br><br>2. The ALL + NULL early-return logic appears in two =
places (lines 2579-2591 and 2644-2656). I initially considered consolidatin=
g this by checking for NULL elements before building the hash table, but re=
alized this would add an extra loop in the common case where there are no N=
ULLs.<br>=C2=A0 =C2=A0Perhaps a brief comment explaining why this check is =
duplicated (to avoid the overhead of a separate NULL-scanning loop) would h=
elp future readers understand the design choice?<br><br>3. Minor style sugg=
estion: adding a brief SQL example in the header comment (e.g., &quot;WHERE=
 x IN (1,2,3,...)&quot; or &quot;WHERE x =3D ANY(ARRAY[...])&quot;) might h=
elp future readers quickly understand the use case.<br><br>Thanks again for=
 working on this optimization. It&#39;s been very educational to follow the=
 discussion and understand how selectivity estimation works in PostgreSQL.<=
br><br>Regards,<br>Tatsuya Kawata</div>

--000000000000b56d50064a370cd0--