public inbox for [email protected]  

help / color / mirror / Atom feed

From: Peter Geoghegan <[email protected]>
To: Vitalii Tymchyshyn <[email protected]>
Cc: [email protected]
Subject: Re: Multicolumn index scan efficiency
Date: Sun, 9 Nov 2025 23:20:39 -0500
Message-ID: <CAH2-WznE3RTO-jFiFwV58sStMsYi0thDGv6NPRCtih7emPXCfw@mail.gmail.com> (raw)
In-Reply-To: <CABWW-d1Eri1zibcix1WdsgnOPnb6O=M4gkVVoSteBiNmZxY+oQ@mail.gmail.com>
References: <CABWW-d1Eri1zibcix1WdsgnOPnb6O=M4gkVVoSteBiNmZxY+oQ@mail.gmail.com>

On Sun, Nov 9, 2025 at 9:44 PM Vitalii Tymchyshyn <[email protected]> wrote:
> 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 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) <= (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-WzmLREy6r68A6SEHXnstg01kNs1HiQtOvSO5cTvWuaducw%40mai...

--
Peter Geoghegan

view thread (4+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Multicolumn index scan efficiency
  In-Reply-To: <CAH2-WznE3RTO-jFiFwV58sStMsYi0thDGv6NPRCtih7emPXCfw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox