From: | "Stephen Samuel (Sam)" <sam(at)sksamuel(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Peter Geoghegan <pg(at)bowt(dot)ie>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Trying to understand why a query is filtering when there is a composite index |
Date: | 2024-08-20 00:00:59 |
Message-ID: | CANRyZ7aMGMG32QgCN_DWy8x_4K00s=GM55h43C2eVEmcoz5+SA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Sbob | 2024-08-22 21:44:49 | checking for a NULL date in a partitioned table kills performance |
Previous Message | Tom Lane | 2024-08-19 23:55:26 | Re: Trying to understand why a query is filtering when there is a composite index |