Re: Trying to understand why a query is filtering when there is a composite index - Mailing list pgsql-performance

On Sun, Aug 18, 2024 at 10:50 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I think it's a good bet that this query would be *slower* if
> it were done the other way.  The filter condition is eliminating
> only one of the 11 rows matching "a = 662028765".  If we did what
> you think you want, we'd initiate ten separate index descents
> to find the other ten rows.

True - on versions prior to Postgres 17.

On 17 the number of index descents will be minimal.  If there are less
than a few hundred index tuples with the value a = <whatever>, then
there'll only be one descent.

> Yeah, that part is a weakness I've wanted to fix for a long
> time: it could do the filter condition by fetching b from the
> index, but it doesn't notice that and has to go to the heap
> to get b.

It was fixed? At least on 17.

--
Peter Geoghegan