Re: Critical performance problems on large databases

On Thu, Apr 11, 2002 at 02:05:07PM +0100, Nigel J. Andrews wrote:

> On 11 Apr 2002, Bill Gribble wrote:

> > Then the biggest slowdown is count(*), which we have to do in order to
> > fake up the scrollbar (so we know what proportion of the data has been
> > scrolled through). 

> seqscan. However, I can see that adding triggers to insert etc. in
> a table and maintain counts is going to hit the data loading time
> but is going to speed the count accesses tremendously.

I suspect the trigger would be pretty miserable for inserts.  But why
use a trigger?  If you need just pretty-close results, you could have
a process that runs (say) every 10 minutes which updated a table of
stats.  (Naturally, if you need really accurate numbers, that's no
help.  But If the idea is just a "record _r_ of _nnnn_ results" sort
of message, you can do what large databases have been doing for
years: if a query returns more than some small-ish number of records,
look in the stats table.  Then you say "record _r_ of approximately
_nnnn_ results".  

A

-- 
----
Andrew Sullivan                               87 Mowat Avenue 
Liberty RMS                           Toronto, Ontario Canada
                              M6K 3E3
                                         +1 416 646 3304 x110