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 <t...@sss.pgh.pa.us> wrote:

> Peter Geoghegan <p...@bowt.ie> writes:
> > On Sun, Aug 18, 2024 at 10:50 PM Tom Lane <t...@sss.pgh.pa.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
>

Reply via email to