From: | Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com> |
---|---|
To: | John Naylor <john(dot)naylor(at)enterprisedb(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [RFC] speed up count(*) |
Date: | 2021-10-20 18:40:56 |
Message-ID: | aa9ec08b-3fe5-0b79-c1f2-b595da9b757c@enterprisedb.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 10/20/21 20:33, John Naylor wrote:
>
> On Wed, Oct 20, 2021 at 2:23 PM Tomas Vondra
> <tomas(dot)vondra(at)enterprisedb(dot)com <mailto:tomas(dot)vondra(at)enterprisedb(dot)com>>
> wrote:
> >
> > Couldn't we simply inspect the visibility map, use the index data only
> > for fully visible/summarized ranges, and inspect the heap for the
> > remaining pages? That'd still be a huge improvement for tables with most
> > only a few pages modified recently, which is a pretty common case.
> >
> > I think the bigger issue is that people rarely do COUNT(*) on the whole
> > table. There are usually other conditions and/or GROUP BY, and I'm not
> > sure how would that work.
>
> Right. My (possibly hazy) recollection is that people don't have quite
> as high an expectation for queries with more complex predicates and/or
> grouping. It would be interesting to see what the balance is.
>
I don't know where the balance is, and I doubt it's possible to answer
that in general - I'm sure some workloads might benefit significantly.
I wonder if multi-column BRIN indexes would help in cases with
additional predicates. Seems possible.
BTW you mentioned using BRIN indexes for min/max - I've been thinking
about using BRIN indexes for ordering/sorting, which seems related. And
I think it's actually doable, so I wonder why you concluded using BRIN
indexes for min/max is not possible?
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Stark | 2021-10-20 18:42:35 | Re: Extending amcheck to check toast size and compression |
Previous Message | Mark Dilger | 2021-10-20 18:40:39 | Non-superuser subscription owners |