select all_visible, count(*)
from pg_visibility('table')
group by all_visible
from pg_visibility('table')
group by all_visible
false,1614
true,30575
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@xxxxxxxxxxxxx> wrote:
Peter Geoghegan <pg@xxxxxxx> writes:
> On Sun, Aug 18, 2024 at 10:50 PM Tom Lane <tgl@xxxxxxxxxxxxx> 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
