Re: Answering my own question

Well, the VACUUM ANALYZE took 4 hours last night, but it seems to have
fixed the problem. EXPLAIN SELECT .... and SELECT from both tables is now
using the indicies and gets sub-second response, even with 800,000 rows of
data.

Now to see whether or not I make VACUUM ANALYZE a nightly thing, or
weekly. 4 hours is long, but its been a LONG time since the last one (like
never).

btw I was confusing VERBOSE with ANALYZE.(Well, this IS the NOVICE list,
right?) VERBOSE definitely makes the cron_log output too long to read.
ANALYZE produces nothing in the cron log, unless VERBOSE Is given as well.

Thanks for all the help so far folks, it's been bang on.

Gerry

Chris
<csmith(at)squiz(dot)ne To: gerry(dot)smit(at)lombard(dot)ca
t> cc: pgsql-novice(at)postgresql(dot)org
Fax to:
15/05/2002 07:28 Subject: Re: [NOVICE] Answering my own question
PM

>Now THAT's REALLY fascinating. We upgraded from 6.5 to 7.1 about 4-5
>months ago , and only got this problem after that. I leave "ANALYZE" out
of
>VACUUM due to the large volume of (I though needless) output that gets
>piped to my cron_log.
>
>Does anyone have a feal for how "necessary" VACUUM ANALYZE is over
"VACUUM"
>??

As you noticed, pretty necessary :)

A vacuum analyze updates the statistics used when the planner decides on
what action to take (whether it's a sequential scan, using an index etc).

A plain vacuum removes dead tuples from the system, not much else.

-----------------
Chris Smith
http://www.squiz.net/