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

Ah, his theory was that I got unlucky in my sample queries.

If I pick data that's much older in the table, then it would seem to
confirm his theory.

Index Only Scan using xx (cost=0.52..25.07 rows=1 width=19) (actual
time=0.032..0.039 rows=34 loops=1)
Index Cond: (a = 1654)
" Filter: (b = ANY
('{1654:150843999,1654:178559906,1654:196691125,1654:213859809,1654:215290364,1654:232833953,1654:234187139,1654:235553821,1654:2514914,1654:27042020,1654:28414362,1654:290939423,1654:294364845,1654:302084789,1654:308624761,1654:321909343,1654:325450448,1654:333349583,1654:333780122,1654:352705002,1654:357720420,1654:360894242,1654:37357227,1654:38419057,1654:397848555,1654:398104037,1654:414568491,1654:415804877,1654:425839729,1654:428927290,1654:430795031,1654:432428733,1654:485645738,1654:490213252}'::text[]))"
Rows Removed by Filter: 8
Heap Fetches: 1
Planning Time: 0.348 ms
Execution Time: 0.058 ms

On Mon, 19 Aug 2024 at 18:55, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Stephen Samuel (Sam)" <sam(at)sksamuel(dot)com> writes:
> > This index covers both columns needed in the predicate/projection, and
> the
> > visibility bit is almost always set, why does it need to go to the heap
> at
> > all and doesn't just get what it needs from the index?
>
> Peter's theory was that the particular tuples you were fetching were
> in not-all-visible pages. That seems plausible to me.
>
> regards, tom lane
>