Re: Much Ado About COUNT(*)

> Greg Stark wrote:
>
> >I think part of the problem is that there's a bunch of features
related
> to
> >these types of queries and the lines between them blur.
> >
> >You seem to be talking about putting visibility information inside
> indexes for
> >so index-only plans can be performed. But you're also talking about
> queries
> >like "select count(*) from foo" with no where clauses. Such a query
> wouldn't
> >be helped by index-only scans.
> >
> >Perhaps you're thinking about caching the total number of records in
a
> global
> >piece of state like a materialized view? That would be a nice feature
but
> I
> >think it should done as a general materialized view implementation,
not a
> >special case solution for just this one query.
> >
> >Perhaps you're thinking of the min/max problem of being able to use
> indexes to
> >pick out just the tuples satisfying the min/max constraint. That
seems to
> me
> >to be one of the more tractable problems in this area but it would
still
> >require lots of work.
> >
> >I suggest you post a specific query you find is slow. Then discuss
how
> you
> >think it ought to be executed and why.
> >
> >
> >
> You are correct, I am proposing to add visibility to the indexes.
>
> As for unqualified counts, I believe that they could take advantage of
> an index-only scan as it requires much less I/O to perform an index
scan
> than a sequential scan on large tables.

I agree with Greg...I think the way to approach this is a general
materialized view solution.  This would solve a broad class of tricky
problems including count() and count(*)...you get to choice between the
pay now/pay later tradeoff, etc.

Merlin