public inbox for [email protected]  

help / color / mirror / Atom feed

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

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...
to detect equality scan prefixes and make corresponding SQL. That would fix
it for all postgres versions.

Best regards, Vitalii Tymchyshyn

нд, 9 лист. 2025 р. о 20:21 Peter Geoghegan <[email protected]> пише:

> 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: <CABWW-d154jXcE143KKb8UYMnjKviG=fsJPmbYxcERyr2ocOKpg@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