Re: Much Ado About COUNT(*)

"Jonah H. Harris" <jharris(at)tvi(dot)edu> writes:

> Looking at the message boards, there is significant interest in the COUNT(*)
> aspect. However, rather than solely address the COUNT(*) TODO item, why not fix
> it and add additional functionality found in commercial databases as well? I
> believe Oracle has had this feature since 7.3 and I know people take advantage
> of it.

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.

--
greg