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-19 04:16:45 |
Message-ID: | CANRyZ7a_b91MnzR3JxwYKXzSX5W4BGSL35Ys2s=gDbpAbwT+Rg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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 <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Peter Geoghegan <pg(at)bowt(dot)ie> writes:
> > On Sun, Aug 18, 2024 at 10:50 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Shiv Iyer | 2024-08-19 08:05:40 | Re: Trying to understand why a query is filtering when there is a composite index |
Previous Message | Tom Lane | 2024-08-19 04:06:22 | Re: Trying to understand why a query is filtering when there is a composite index |