FW: Vacuuming - how often? (scott's fault?)

Thank-you Andrew

I think I have done the proverbial forgot i changed something, or not.

I'm sure that I only ran vacuum between 2 calls to indentical views and got
different behaviour... But i have been wrong before.

I just thought that i'd mention that at some point after the vacuum I
noticed this debug message back from ?the backend? and I think I understand
the impact of this.

NOTICE:  Adding missing FROM-clause entry for table "transactions"

I can't describe the stuff I did up to the point because I was reorganizing
things and then noticed the debug message.  I think the reason for it was
that i had an alias for transactions in the field list, but I explicitly
used the name "transactions" in the WHERE clause.  This message also
coincided with a product like join...

What I'm saying is at this point it is inconclusive whether I had a data
related problem, but if it happens again, I'll know better how to track it
down.. thanks all for the help..

Scott.

-----Original Message-----
From: Andrew Sullivan [mailto:andrew@libertyrms.com]On Behalf Of Andrew
Sullivan
Sent: November 14, 2001 10:38 AM
To: Scott Muir
Subject: Re: [GENERAL] Vacuuming - how often?


On Tue, Nov 13, 2001 at 06:32:12PM -0700, Scott Muir wrote:
> I'm using 7.1 on redhat 7.1...
>
> I had a query result which startled me a bit...  it was a simple join and
> the result was like a cartesian product like I hadn't bothered with a when
> clause.
>
> I ran vacuum from inside pgaccess and the results of the query changed to
> what I was expecting in the first place...
>
> What I'm wondering is what is the likely problem here?  Does this mean
that
> *regular* maintenance is required?  the data in this database is pretty
much
> append only.. with some updating, but next to zero deleting is done...
>
> any comments on this?

Did you also do analyse?

Don't forget that the planner loses track of how the tables are, and
the planner will therefore at some point choose the wrong plan for
the table, skipping a useful index or something.  So yes, regular
maintenance is necessary.  Put it in a cron job.

A

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110