MIME-Version: 1.0
From: Jim Vanns <jvanns@ilm.com>
Date: Tue, 12 Nov 2024 12:43:26 +0000
Message-ID: <CAH7vdhO_JPN33FVMi_NaHs-YgSh6FHmORtncPtjjq7QcJVV=tw@mail.gmail.com>
Subject: BitmapOr node not used in plan for ANY/IN but is for sequence of ORs ...
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000eb0d170626b6909a"
Archived-At: <https://www.postgresql.org/message-id/CAH7vdhO_JPN33FVMi_NaHs-YgSh6FHmORtncPtjjq7QcJVV%3Dtw%40mail.gmail.com>
Precedence: bulk

--000000000000eb0d170626b6909a
Content-Type: text/plain; charset="UTF-8"

In PG16.4, we have a table of key/pair data (around 30M rows) where there
are about 7 distinct keys and each has a conditional or partial index on
them (the distribution is different for each key/value pair combination).
I've found that when we have a query that uses an OR then those partial
indexes are used but not if the query is written to use ANY/IN, which is
more convenient from a programmer POV (especially any with 3rd party query
generators etc.). Naturally, the result sets returned by the queries are
identical due to the filter semantics of any of the 3 solution variants.

Here's a shareable, MRP;

https://dbfiddle.uk/OKs_7HWv

Is there any trick I can do to get the planner to make use of the
conditional/partial index? Or is this simply an unoptimised code path yet
to be exploited!?

Cheers,

Jim

-- 
Jim Vanns
Principal Production Engineer
Industrial Light & Magic, London

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

<div dir=3D"ltr"><div>In PG16.4, we have a table of key/pair data (around 3=
0M rows) where there are about 7 distinct keys and each has a conditional o=
r partial index on them (the distribution is different for each key/value p=
air combination).=C2=A0 I&#39;ve found that when we have a query that uses =
an OR then those partial indexes are used but not if the query is written t=
o use ANY/IN, which is more convenient from a programmer POV (especially an=
y with 3rd party query generators etc.). Naturally, the result sets returne=
d by the queries are identical due to the filter semantics of any of the 3 =
solution variants.</div><div><br></div><div>Here&#39;s a shareable, MRP;</d=
iv><div><br></div><div><a href=3D"https://dbfiddle.uk/OKs_7HWv">https://dbf=
iddle.uk/OKs_7HWv</a></div><div><br></div><div>Is there any trick I can do =
to get the planner to make use of the conditional/partial index? Or is this=
 simply an unoptimised code path yet to be exploited!?</div><div><br></div>=
<div>Cheers,</div><div><br></div><div>Jim</div><div><br></div><span class=
=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_s=
ignature" data-smartmail=3D"gmail_signature"><div dir=3D"ltr">Jim Vanns<br>=
Principal Production Engineer<br>Industrial Light &amp; Magic, London</div>=
</div></div>

--000000000000eb0d170626b6909a--