MIME-Version: 1.0
References: 
 <CABWW-d1Eri1zibcix1WdsgnOPnb6O=M4gkVVoSteBiNmZxY+oQ@mail.gmail.com>
In-Reply-To: 
 <CABWW-d1Eri1zibcix1WdsgnOPnb6O=M4gkVVoSteBiNmZxY+oQ@mail.gmail.com>
From: Peter Geoghegan <pg@bowt.ie>
Date: Sun, 9 Nov 2025 23:20:39 -0500
Message-ID: 
 <CAH2-WznE3RTO-jFiFwV58sStMsYi0thDGv6NPRCtih7emPXCfw@mail.gmail.com>
Subject: Re: Multicolumn index scan efficiency
To: Vitalii Tymchyshyn <vit@tym.im>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAH2-WznE3RTO-jFiFwV58sStMsYi0thDGv6NPRCtih7emPXCfw%40mail.gmail.com>
Precedence: bulk

On Sun, Nov 9, 2025 at 9:44=E2=80=AFPM Vitalii Tymchyshyn <vit@tym.im> wrot=
e:
> 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) be=
tween (x1,y1,z1) and (x2,y2,z2) before the improvement.
> Note that my tests can mostly be rewritten as equality at least for some =
columns (and this is what we'll do), but sometimes we do need a range scan =
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-WzmLREy6r68A6SEHXnstg01kNs1=
HiQtOvSO5cTvWuaducw%40mail.gmail.com#62e393ac8bbf06f0f73598ba2ceeab69

--
Peter Geoghegan