Re: Improving count(*)

Tom Lane wrote:
> Richard Huxton <dev@archonet.com> writes:
> > Might it be possible to apply rule-style rewriting to a clause of an 
> > ordinary select query? That is, is it prohibitively expensive to get PG 
> > to recognise
> >    SELECT count(*) FROM big_table
> > and replace it with
> >    SELECT sum(summary_count) FROM my_materialised_view
> 
> > This should allow you to have where-clauses and apply to a range of 
> > cases. What I fear is that checking to see if the rule applies will cost 
> >   too much on all those queries where it doesn't apply.
> 
> There is already code in the optimizer that does similar rewriting
> for min/max queries.   However, that's a hard-wired transformation.
> I don't see any very simple way to provide a user-configurable
> equivalent.

I guess there must be a query-rewriting mechanism for implementing
materialized views.  With that in place we may be able to implement this
other thing ...  Is anybody working on materialized views?

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support