Re: Strange count(*) implementation?

Hi,

On Tue, 2004-10-26 at 15:25, Henk Ernst Blok wrote:
...

> the TPC-H query set in particular). So decision support and datamining
> are in that area for instance. My topic of interest is IR (information
> retrieval) in a database context. My experiments behave like an OLAP
> load at the moment. My current experiments involve a lot of counting
> and expensive joins as I have to compute certain estimators in a
> mathematical model I'm working on, hence the importance of the
> count... ;)
> On MySQL each of the 30 queries I have to run took on average about 24
> h. As my queries are getting even complexer I'm now trying to find out
> whether Postgres can do a better job.

In your specific application if you have not many inserts
or have a phase where you do the inserts and another distinct
phase where you do the analysis, you should be able to
count() just before your analysis. If not you can always
experiment with triggers to count() in an optimized way
using just another table to store the count value
for every table you need.

INSERT/DELETE via function, use a trigger and/or RULES.
This should do the trick.
Maybe you can precalculate a lot more - depending on
the algorithms you use.

Regards
Tino