Re: count(*) performance improvement ideas

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Mark Mielke <mark(at)mark(dot)mielke(dot)cc> writes:
>> Fine - once per transaction instead of once per insert. Still, if there
>> is overhead to this (updating a secondary summary table), does it really
>> make sense to have it for every table?
>
> We certainly wouldn't accept a patch that imposed this overhead on every
> table.

If you look at this at the right angle it's actually a degenerate case of
materialized views. I think think it would be more useful to approach it from
that direction even if it only supported a very limited set of expressions.

In an ideal world I would love to be able to do something like:

CREATE MATERIALIZED VIEW foo AS (select count(*) from bar) WITH INCREMENTAL UPDATES;

and have that automatically create both a heap to store the count and another
to store the incremental changes. To do this would require some magic to know
what "incremental changes" means for each aggregate where it's meaningful
though.

Then it would require some magic in the optimizer to recognize when piece of
the query can be satisfied by a materialized view.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!