Re: Wierd Explain

On Tue, 18 Jun 2002, Thomas Beutin wrote:

> Hi,
>
> On Tue, Jun 18, 2002 at 09:03:50AM -0600, Scott Marlowe wrote:
> > On Tue, 18 Jun 2002, Thomas Beutin wrote:
> > > On Tue, Jun 18, 2002 at 12:33:26PM +0530, Varun Kacholia wrote:
> > > >   Many ppl have suggested me vaccum analyze .
> > > >   But the manuals say me that it is reqd after "major update/deletion".
> > > >   I have just 'created' the tables and done fresh insertions.
> > > >   is it still required?
> >
> > > AFAIK is not.
> >
> > Actually, you do have to run it then.  Analyze is never done automatically
> > by Postgresql, so the postmaster has no idea how your data are organized
> > yet.  (Varun's data, not Thomas's  :-)
>
> Sorry, i forgot the postings bevor. But correct me if i'm wrong:
> Creating a fresh table an inserting data. For the data retreiving
> the vacuum analyze is not neccessary, but to get correct values
> by explain it is required. Right?

Correct, for the most part.

Analyze is NEVER required.  It just lets the database determine the
frequency and range of values in a table so the planner can make a better
guess when deciding how to return rows.  Since a huge insert results in a
massive change in the layout of your tables, an analyze is needed if you
want the best plan for your data.

Without it, select and explain will work fine, but the numbers they will
use to make decisions will be incorrect, and the query plan chosen will
likely be suboptimal.

Scott Marlowe

 -- "Force has no place where there is need of skill.", "Haste in every
business brings failures.", "This is the bitterest pain among men, to have
much knowledge but no power." -- Herodotus