From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Peter Geoghegan <pg(at)bowt(dot)ie> |
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 04:06:22 |
Message-ID: | 1258974.1724040382@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Peter Geoghegan <pg(at)bowt(dot)ie> writes:
> On Sun, Aug 18, 2024 at 10:50 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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.
Oh, sorry, I was thinking of a related problem that doesn't apply
here: matching indexes on expressions to fragments of a filter
condition. However, the fact that the OP's EXPLAIN shows heap
fetches from a supposedly all-visible table suggests that his
IN isn't getting optimized that way. I wonder why --- it seems
to work for me, even in fairly old versions. Taking a parallel
example from the regression database, even v12 can do
regression=# explain analyze select tenthous from tenk1 where thousand=99 and tenthous in (1,4,7,9,11,55,66,88,99,77,8876,9876);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using tenk1_thous_tenthous on tenk1 (cost=0.29..4.61 rows=1 width=4) (actual time=0.016..0.018 rows=1 loops=1)
Index Cond: (thousand = 99)
Filter: (tenthous = ANY ('{1,4,7,9,11,55,66,88,99,77,8876,9876}'::integer[]))
Rows Removed by Filter: 9
Heap Fetches: 0
Planning Time: 0.298 ms
Execution Time: 0.036 ms
(7 rows)
No heap fetches, so it must have done the filter from the index.
Why not in the original case?
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Samuel (Sam) | 2024-08-19 04:16:45 | Re: Trying to understand why a query is filtering when there is a composite index |
Previous Message | Peter Geoghegan | 2024-08-19 03:11:40 | Re: Trying to understand why a query is filtering when there is a composite index |