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

select all_visible, count(*)
from pg_visibility('table')
group by all_visible

false,1614
true,30575

The table is partitioned if that matters (but same results if I run the
queries directly on the partition).

On Sun, 18 Aug 2024 at 23:06, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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
>