MIME-Version: 1.0
References: <a52070f6-9711-4ff5-a1bd-b3f5433fb843@evolu-s.it>
 <f913ea6e7dfd5ec9130bed21a95956eba3eaba29.camel@cybertec.at>
 <CANzqJaA17=QxTjNHMmy3xBUcaT5o5ZwxDu74d9tatb8Lwka=2w@mail.gmail.com> <CAKFQuwZKD=6PQyuC-_Krw8qOESYNhOC+ZNDQYYgqC5402kY0nA@mail.gmail.com>
In-Reply-To: <CAKFQuwZKD=6PQyuC-_Krw8qOESYNhOC+ZNDQYYgqC5402kY0nA@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Fri, 28 Mar 2025 10:04:24 -0700
Message-ID: <CAKFQuwZwwoODiq5ZW7EvCotrfk7jK-wKVnzx=s_NCT7TgM7DmA@mail.gmail.com>
Subject: Re: BTREE index: field ordering
To: Ron Johnson <ronljohnsonjr@gmail.com>
Cc: PostgreSQL mailing lists <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000253fda06316a1296"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwZwwoODiq5ZW7EvCotrfk7jK-wKVnzx%3Ds_NCT7TgM7DmA%40mail.gmail.com>
Precedence: bulk

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

On Fri, Mar 28, 2025 at 10:02=E2=80=AFAM David G. Johnston <
david.g.johnston@gmail.com> wrote:

> Selectivity is evaluating cardinality with an eye on the frequency of
> the values you are actually going to be filtering on.  So low cardinality
> booleans can be highly selective in usage if you are looking for the rare
> false in a sea of trues but low selectivity if looking through those true=
s.
>

And to accommodate the highly-selective case a partial index is much better
than indexing the low cardinality boolean column values.

David J.

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

<div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_default" style=3D"fon=
t-family:arial,helvetica,sans-serif"><span style=3D"font-family:Arial,Helve=
tica,sans-serif">On Fri, Mar 28, 2025 at 10:02=E2=80=AFAM David G. Johnston=
 &lt;<a href=3D"mailto:david.g.johnston@gmail.com">david.g.johnston@gmail.c=
om</a>&gt; wrote:</span></div></div><div class=3D"gmail_quote gmail_quote_c=
ontainer"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8=
ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr=
"><div class=3D"gmail_quote"><div style=3D"font-family:arial,helvetica,sans=
-serif">Selectivity is evaluating cardinality with an eye on the frequency =
of the=C2=A0values you are actually going to be filtering on.=C2=A0 So low =
cardinality booleans can be highly selective in usage=C2=A0if you are looki=
ng for the rare false in a sea of trues but low selectivity if looking thro=
ugh those trues.</div></div></div></blockquote><div><br></div><div class=3D=
"gmail_default" style=3D"font-family:arial,helvetica,sans-serif">And to acc=
ommodate the highly-selective case a partial index is much better than inde=
xing the low cardinality boolean column values.</div><div class=3D"gmail_de=
fault" style=3D"font-family:arial,helvetica,sans-serif"><br></div><div clas=
s=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif">David =
J.</div><div class=3D"gmail_default" style=3D"font-family:arial,helvetica,s=
ans-serif"><br></div></div></div>

--000000000000253fda06316a1296--