Обсуждение: vacuum analyze after updating from CVS?

I have found that at least one query I do gets a poor plan after I update
from CVS even if I don't need to do an initdb. Sometimes I have done an
initdb to clear things up; most recently a vacuum analyze did the trick.
This is a database used read only for web pages that I occasionally
reload (part of the reload process is to do a vacuum analyze).
Is this something I should expect? I would think since the stats were
stored in the database, they would continue to be valid after updating
the code (unless an initdb was forced).

Bruno Wolff III <bruno@wolff.to> writes:
> I have found that at least one query I do gets a poor plan after I update
> from CVS even if I don't need to do an initdb. Sometimes I have done an
> initdb to clear things up; most recently a vacuum analyze did the trick.
> This is a database used read only for web pages that I occasionally
> reload (part of the reload process is to do a vacuum analyze).
> Is this something I should expect? I would think since the stats were
> stored in the database, they would continue to be valid after updating
> the code (unless an initdb was forced).

If you didn't do initdb then I'd not expect pg_statistic to get wiped.
Details please?

            regards, tom lane

On Sat, Jul 12, 2003 at 17:13:36 -0400,
  Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
> > I have found that at least one query I do gets a poor plan after I update
> > from CVS even if I don't need to do an initdb. Sometimes I have done an
> > initdb to clear things up; most recently a vacuum analyze did the trick.
> > This is a database used read only for web pages that I occasionally
> > reload (part of the reload process is to do a vacuum analyze).
> > Is this something I should expect? I would think since the stats were
> > stored in the database, they would continue to be valid after updating
> > the code (unless an initdb was forced).
>
> If you didn't do initdb then I'd not expect pg_statistic to get wiped.
> Details please?
>
>             regards, tom lane

I am not sure what to check that will help.

I fetch a new copy from cvs, make distclean, run configure, and
make. I shutdown the database and then do a make install.
I then check a particular query that I had been noticing having
problems after doing that. I do explain analyse select ...
and get a plan that is typically several times lower than expected.
I then either do an initdb and reload my data or in the last case
I just did a vacuum analyze and things sped up again.

It would be easy to show you the explain analyze output before
the rebuild, the explain analyze after the rebuild and explain
analyze after the vacuum analyze.
What else would be useful to see?

Bruno Wolff III <bruno@wolff.to> writes:
> It would be easy to show you the explain analyze output before
> the rebuild, the explain analyze after the rebuild and explain
> analyze after the vacuum analyze.
> What else would be useful to see?

The contents of pg_stats for the table(s) involved, before and after,
might be interesting too.

            regards, tom lane

On Sun, Jul 13, 2003 at 11:12:21 -0400,
  Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Bruno Wolff III <bruno@wolff.to> writes:
> > It would be easy to show you the explain analyze output before
> > the rebuild, the explain analyze after the rebuild and explain
> > analyze after the vacuum analyze.
> > What else would be useful to see?
>
> The contents of pg_stats for the table(s) involved, before and after,
> might be interesting too.
>
>             regards, tom lane

I didn't see the effect on my latest upgrade. I will keep watching (dumping
pg_stats just before upgrading) for this for a while and see if it happens
again.