Re: Improving count(*)

Simon Riggs wrote:
> One of the major complaints is always "Select count(*) is slow".

Although there seem to have been plenty of ideas on this they all seem 
to just provide a solution for the "whole table" case. It might be that 
the solution provides other benefits, but for this one case it does seem 
like a lot of work.

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'tapply.
 

--   Richard Huxton  Archonet Ltd