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
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@xxxxxxxxxxxxx> wrote:
"Stephen Samuel (Sam)" <sam@xxxxxxxxxxxx> 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
