Re: Trying to understand why a query is filtering when there is a composite index

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