From: | Peter Geoghegan <pg(at)bowt(dot)ie> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "Stephen Samuel (Sam)" <sam(at)sksamuel(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Trying to understand why a query is filtering when there is a composite index |
Date: | 2024-08-19 03:11:40 |
Message-ID: | CAH2-WznnZuHxiUXR2wa7c-pcd3WcTwcz+OO9QZpeNhWR6DVbUA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Sun, Aug 18, 2024 at 10:50 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2024-08-19 04:06:22 | Re: Trying to understand why a query is filtering when there is a composite index |
Previous Message | Tom Lane | 2024-08-19 02:50:10 | Re: Trying to understand why a query is filtering when there is a composite index |