MIME-Version: 1.0
References: 
 <CABWW-d1Eri1zibcix1WdsgnOPnb6O=M4gkVVoSteBiNmZxY+oQ@mail.gmail.com>
 <CAH2-WznE3RTO-jFiFwV58sStMsYi0thDGv6NPRCtih7emPXCfw@mail.gmail.com>
In-Reply-To: 
 <CAH2-WznE3RTO-jFiFwV58sStMsYi0thDGv6NPRCtih7emPXCfw@mail.gmail.com>
From: Vitalii Tymchyshyn <vit@tym.im>
Date: Sun, 9 Nov 2025 21:12:09 -0800
Message-ID: 
 <CABWW-d154jXcE143KKb8UYMnjKviG=fsJPmbYxcERyr2ocOKpg@mail.gmail.com>
Subject: Re: Multicolumn index scan efficiency
To: Peter Geoghegan <pg@bowt.ie>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000074285206433693cf"
Archived-At: 
 <https://www.postgresql.org/message-id/CABWW-d154jXcE143KKb8UYMnjKviG%3DfsJPmbYxcERyr2ocOKpg%40mail.gmail.com>
Precedence: bulk

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

Thank you so much for both clarifying and fixing it!
In our case (FYI, this is from http://github.com/cdapio/cdap) a lot of
users have just a single namespace, so it effectively means scanning till
the end of the index.
We'll fix
https://github.com/cdapio/cdap/blob/develop/cdap-data-fabric/src/main/java/=
io/cdap/cdap/spi/data/sql/PostgreSqlStructuredTable.java
to detect equality scan prefixes and make corresponding SQL. That would fix
it for all postgres versions.

Best regards, Vitalii Tymchyshyn

=D0=BD=D0=B4, 9 =D0=BB=D0=B8=D1=81=D1=82. 2025=E2=80=AF=D1=80. =D0=BE 20:21=
 Peter Geoghegan <pg@bowt.ie> =D0=BF=D0=B8=D1=88=D0=B5:

> On Sun, Nov 9, 2025 at 9:44=E2=80=AFPM Vitalii Tymchyshyn <vit@tym.im> wr=
ote:
> > I am wondering about 2 things:
> > 1) Does anyone know which specific change / version made it fast?
> > 2) What was the proper way to do a range index scan like WHERE (a,b,c)
> between (x1,y1,z1) and (x2,y2,z2) before the improvement.
> > Note that my tests can mostly be rewritten as equality at least for som=
e
> columns (and this is what we'll do), but sometimes we do need a range sca=
n
> like above, so understanding it would be important. Also I am curious :).
>
> This improvement you're seeing here is down to work in commit
> bd3f59fd. The short version is that the way we used to decide when a
> condition like "WHERE (a,b,c) <=3D (x2,y2,z2)" was needlessly
> conservative. If there were many "a" values equal to x2, we'd have to
> scan the index until we got to the next distinct/non-equal "a" value
> -- without realizing that we're already past the point where there
> cannot possibly be any more matches.
>
> See the discussion on this thread which complained about the problem,
> particularly my response to the complaint:
>
>
> https://www.postgresql.org/message-id/flat/CAH2-WzmLREy6r68A6SEHXnstg01kN=
s1HiQtOvSO5cTvWuaducw%40mail.gmail.com#62e393ac8bbf06f0f73598ba2ceeab69
>
> --
> Peter Geoghegan
>

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

<div dir=3D"ltr"><div dir=3D"ltr">Thank you so much for both clarifying and=
 fixing it!<div>In our case (FYI, this=C2=A0is from=C2=A0<a href=3D"http://=
github.com/cdapio/cdap">http://github.com/cdapio/cdap</a>) a lot of users h=
ave just a single namespace, so it effectively means scanning till the end =
of the index.</div><div>We&#39;ll fix=C2=A0<a href=3D"https://github.com/cd=
apio/cdap/blob/develop/cdap-data-fabric/src/main/java/io/cdap/cdap/spi/data=
/sql/PostgreSqlStructuredTable.java">https://github.com/cdapio/cdap/blob/de=
velop/cdap-data-fabric/src/main/java/io/cdap/cdap/spi/data/sql/PostgreSqlSt=
ructuredTable.java</a> to detect equality scan prefixes and make correspond=
ing SQL. That would fix it for all postgres versions.=C2=A0</div><div><br><=
/div><div>Best regards, Vitalii Tymchyshyn</div></div><br><div class=3D"gma=
il_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">=D0=
=BD=D0=B4, 9 =D0=BB=D0=B8=D1=81=D1=82. 2025=E2=80=AF=D1=80. =D0=BE 20:21 Pe=
ter Geoghegan &lt;<a href=3D"mailto:pg@bowt.ie">pg@bowt.ie</a>&gt; =D0=BF=
=D0=B8=D1=88=D0=B5:<br></div><blockquote class=3D"gmail_quote" style=3D"mar=
gin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1=
ex">On Sun, Nov 9, 2025 at 9:44=E2=80=AFPM Vitalii Tymchyshyn &lt;<a href=
=3D"mailto:vit@tym.im" target=3D"_blank">vit@tym.im</a>&gt; wrote:<br>
&gt; I am wondering about 2 things:<br>
&gt; 1) Does anyone know which specific change / version made it fast?<br>
&gt; 2) What was the proper way to do a range index scan like WHERE (a,b,c)=
 between (x1,y1,z1) and (x2,y2,z2) before the improvement.<br>
&gt; Note that my tests can mostly be rewritten as equality at least for so=
me columns (and this is what we&#39;ll do), but sometimes we do need a rang=
e scan like above, so understanding it would be important. Also I am curiou=
s :).<br>
<br>
This improvement you&#39;re seeing here is down to work in commit<br>
bd3f59fd. The short version is that the way we used to decide when a<br>
condition like &quot;WHERE (a,b,c) &lt;=3D (x2,y2,z2)&quot; was needlessly<=
br>
conservative. If there were many &quot;a&quot; values equal to x2, we&#39;d=
 have to<br>
scan the index until we got to the next distinct/non-equal &quot;a&quot; va=
lue<br>
-- without realizing that we&#39;re already past the point where there<br>
cannot possibly be any more matches.<br>
<br>
See the discussion on this thread which complained about the problem,<br>
particularly my response to the complaint:<br>
<br>
<a href=3D"https://www.postgresql.org/message-id/flat/CAH2-WzmLREy6r68A6SEH=
Xnstg01kNs1HiQtOvSO5cTvWuaducw%40mail.gmail.com#62e393ac8bbf06f0f73598ba2ce=
eab69" rel=3D"noreferrer" target=3D"_blank">https://www.postgresql.org/mess=
age-id/flat/CAH2-WzmLREy6r68A6SEHXnstg01kNs1HiQtOvSO5cTvWuaducw%40mail.gmai=
l.com#62e393ac8bbf06f0f73598ba2ceeab69</a><br>
<br>
--<br>
Peter Geoghegan<br>
</blockquote></div></div>

--00000000000074285206433693cf--