Re: [RFC] speed up count(*)

On 10/20/21 20:33, John Naylor wrote:
> 
> On Wed, Oct 20, 2021 at 2:23 PM Tomas Vondra 
> <tomas.vondra@enterprisedb.com <mailto:tomas.vondra@enterprisedb.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