Re: speeding up COUNT and DISTINCT queries

On Thu, 2003-03-13 at 10:42, Greg Stark wrote:
> Max Baker <max@warped.org> writes:
> > On Wed, Mar 12, 2003 at 05:57:50PM -0800, Joe Conway wrote:
> > That would explain why once a night isn't enough.  Thanks.
> > The contents of this table get refreshed every 4 hours.  I'll add a
> > vacuum after every refresh and comapre the results in a couple days.
>
> If it gets completely refreshed, ie, every tuple is updated or deleted and
> re-inserted in a big batch job then VACUUM might never be enough without
> boosting some config values a lot. You might need to do a VACUUM FULL after
> the refresh. VACUUM FULL locks the table though which might be unfortunate.
>

hmm... approx 35,000 records, getting updated every 4 hours. so..

35000 / (4*60) =~ 145 tuples per minute.

Lets assume we want to keep any overhead at 10% or less, so we need to
lazy vacuum every 3500 updates. so...

3500 tuples / 145 tpm =~ 25 minutes.

So, set up a cron job to lazy vacuum every 20 minutes and see how that
works for you.

Robert Treat