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 wrote:
> Peter Geoghegan writes:
> > On Sun, A
Peter Geoghegan writes:
> On Sun, Aug 18, 2024 at 10:50 PM Tom Lane 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? A
On Sun, Aug 18, 2024 at 10:50 PM Tom Lane wrote:
> I think it's a good bet that this query would be *slower* if
> it were done the other way. The filter condition is eliminating
> only one of the 11 rows matching "a = 662028765". If we did what
> you think you want, we'd initiate ten separate in
"Stephen Samuel (Sam)" writes:
> There is a unique index on (a,b)
> The query is:
> SELECT b
> FROM table
> WHERE a =
> AND b IN ()
> The planner says index only scan, but is filtering on b.
> Index Only Scan using pkey on table (cost=0.46..29.09 rows=1
> width=19) (actual time=0.033..0.053
Performance is pretty good anyway, and I'm only running 5 r7.large readers
on this service, I was just looking at the query planner and it surprised
me.
On Sun, 18 Aug 2024 at 21:08, Peter Geoghegan wrote:
> On Sun, Aug 18, 2024 at 10:01 PM Stephen Samuel (Sam)
> wrote:
> > Oh as simple as upg
On Sun, Aug 18, 2024 at 10:01 PM Stephen Samuel (Sam) wrote:
> Oh as simple as upgrading!
> Ok great, appreciate the quick reply. Will have to wait for AWS to support 17
> :)
It is possible to use index quals for both a and b on earlier
versions, with certain restrictions. You might try setting
Oh as simple as upgrading!
Ok great, appreciate the quick reply. Will have to wait for AWS to support
17 :)
On Sun, 18 Aug 2024 at 20:59, Peter Geoghegan wrote:
> On Sun, Aug 18, 2024 at 9:56 PM Stephen Samuel (Sam)
> wrote:
> > My question is, why isn't it using the index for column b? Is thi
On Sun, Aug 18, 2024 at 9:56 PM Stephen Samuel (Sam) wrote:
> My question is, why isn't it using the index for column b? Is this expected?
> And why is it doing heap lookups for every row,.
This has been fixed for Postgres 17:
https://pganalyze.com/blog/5mins-postgres-17-faster-btree-index-scan
Hi folks.
I have a table with 4.5m rows per partition (16 partitions) (I know, very
small, probably didn't need to be partitioned).
The table has two columns, a bigint and b text.
There is a unique index on (a,b)
The query is:
SELECT b
FROM table
WHERE a =
AND b IN ()
The visibility map is
