I just dumped and restored a rather large database, I upgraded from 
7.2.x to 7.3.x. When I went to test my application against the new 
database, it was dog slow. It had all the indexes, and looked fine.

Then it dawned on me, Doh! ANALYZE!

Should pg_dump appened an ANALYZE for each table?

On small tables, this shouldn't take too long. On large tables, you're 
gonna have to do it anyway. I guess it could be an option as well.

It just seems like on of the tasks that is required for a "restored" 
database to work properly, and as such, should probably be specified in 
the backup procedure.




Thu, 27 Feb 2003 12:27:44 -0500
mlw <pgsql@mohawksoft.com>
analyze after a database restore?
mlw <pgsql@mohawksoft.com> writes:
> Should pg_dump appened an ANALYZE for each table?

A single ANALYZE at the end of the script would be sufficient.  I'm not
sure that pg_dump should do this automatically though.  If you're not
done restoring then it's mostly a waste of cycles, and how is pg_dump to
know that?

I do note that the docs are rather stingy with this important bit of
knowhow :-(  Neither of the obvious places that I looked in (pg_dump
reference page and admin guide's backup/restore chapter) mention the
need to issue an ANALYZE after completing a restore.  I'm pretty sure it
is mentioned *somewhere* ;-) ... but it needs to be more prominent.

			regards, tom lane

Thu, 27 Feb 2003 12:55:42 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: analyze after a database restore? 
On Thu, 2003-02-27 at 12:27, mlw wrote:
> I just dumped and restored a rather large database, I upgraded from 
> 7.2.x to 7.3.x. When I went to test my application against the new 
> database, it was dog slow. It had all the indexes, and looked fine.
> 
> Then it dawned on me, Doh! ANALYZE!
> 
> Should pg_dump appened an ANALYZE for each table?
> 
> On small tables, this shouldn't take too long. On large tables, you're 
> gonna have to do it anyway. I guess it could be an option as well.
> 
> It just seems like on of the tasks that is required for a "restored" 
> database to work properly, and as such, should probably be specified in 
> the backup procedure.

It's been debated before whether pg_dump should append ANALYZE to the
end of the load script.  It has always been determined that it shouldn't
(see archives for arguments).

However, an 'Auto-vacuum' process should watch stats and re-analyze the
table when the larger of 30% or 1000 rows has been affected (inserts, or
deletes mostly). That is probably a better solution overall.

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
27 Feb 2003 13:01:09 -0500
Rod Taylor <rbt@rbt.ca>
Re: analyze after a database restore?


Tom Lane wrote:

>mlw <pgsql@mohawksoft.com> writes:
>  
>
>>Should pg_dump appened an ANALYZE for each table?
>>    
>>
>
>A single ANALYZE at the end of the script would be sufficient.  I'm not
>sure that pg_dump should do this automatically though.  If you're not
>done restoring then it's mostly a waste of cycles, and how is pg_dump to
>know that?
>
>I do note that the docs are rather stingy with this important bit of
>knowhow :-(  Neither of the obvious places that I looked in (pg_dump
>reference page and admin guide's backup/restore chapter) mention the
>need to issue an ANALYZE after completing a restore.  I'm pretty sure it
>is mentioned *somewhere* ;-) ... but it needs to be more prominent.
>  
>
While these are all comforting points, I *know* about analyze and I 
occasionally forget. It just seems like a nessisary step after restoring 
a backup. Conceptually, one could consider it just as important as an 
index, i.e. the system will perform poorly without it.

 From an "ease of use" perspective, it would be one less step.


Thu, 27 Feb 2003 13:12:29 -0500
mlw <pgsql@mohawksoft.com>
Re: analyze after a database restore?
On Thursday 27 February 2003 13:12, mlw wrote:
> Tom Lane wrote:
> >A single ANALYZE at the end of the script would be sufficient.  I'm not
> >sure that pg_dump should do this automatically though.  If you're not
> >done restoring then it's mostly a waste of cycles, and how is pg_dump to
> >know that?
> [...]
>  From an "ease of use" perspective, it would be one less step.

Why not have pg_dump emit a friendly reminder?

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.

Thu, 27 Feb 2003 13:58:47 -0500
"D'Arcy J.M. Cain" <darcy@druid.net>
Re: analyze after a database restore?


D'Arcy J.M. Cain wrote:

>On Thursday 27 February 2003 13:12, mlw wrote:
>  
>
>>Tom Lane wrote:
>>    
>>
>>>A single ANALYZE at the end of the script would be sufficient.  I'm not
>>>sure that pg_dump should do this automatically though.  If you're not
>>>done restoring then it's mostly a waste of cycles, and how is pg_dump to
>>>know that?
>>>      
>>>
>>[...]
>> From an "ease of use" perspective, it would be one less step.
>>    
>>
>
>Why not have pg_dump emit a friendly reminder?
>
>  
>
The reminder won't work, because the backup may be happening in an 
automatic fashion, and anything but error messages will be lost. I 
dislike having to have an "expert" be present at the database "restore" 
phase of operation.

Suppose a company loses the PG admin and a reasonably experienced person 
takes his or her place temporarily, This scenario happens all the time 
with all sorts of projects. A reasonably experienced person will be able 
to accomplish a DB restore but will probably not know about performing 
an analyze. Under the pressure of restoring after a crash on a live 
system, even a reasonably experienced PG admin may forget, hell I forgot 
and I've been using PG since 1997.

The "correct" view of a database backup should be to include the 
statistics of the database as it existed at the time backup, these 
statistics are part of this state "snapshot" because the directly affect 
the operation of the database. I do not want to evoke the name of 
Larry's evil product, but it saves its statistics when the data is exported.

Short of including the relevant statistics, there should be an option on 
pg_dump to emit an "ANALYZE;" at the end of a database dump. This will 
allow a "knowledgeable" admin to selectively add the vacuum so that 
someone possibly less qualified than he can do the restore.

Does anyone disagree that a query's "explain" should look the same or 
better after a successful restore? From a product QA point of view, if a 
valid backup set, when restored, does not recreate the system in a state 
at least as efficient and workable as the system when it was backed up, 
you did not have a successful restore. Any QA department would rate this 
as a serious bug.

Are there any reasons why it should not be an option on pg_dump?


Thu, 27 Feb 2003 14:45:46 -0500
mlw <pgsql@mohawksoft.com>
Re: analyze after a database restore?
mlw <pgsql@mohawksoft.com> writes:
>  From an "ease of use" perspective, it would be one less step.

There is something to be said for that.  As Rod notes, this has been
considered and rejected before --- but I think that was back when
ANALYZE (a) could only be done as part of VACUUM, and (b) insisted on
scanning the whole table.  The current implementation is vastly
lighter-weight than what we were looking at back then.  Perhaps it's
time to reconsider.

Although I suggested doing a single unconditional ANALYZE at the end
of the script, second thought leads me to think the per-table ANALYZE
(probably issued right after the table's data-load step) might be
better.  That way you'd not have any side-effects on already-existing
tables in the database you are loading to.  OTOH, that way would leave
the system catalogs un-analyzed, which might be bad.

			regards, tom lane

Thu, 27 Feb 2003 15:12:36 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: analyze after a database restore? 
On Thu, Feb 27, 2003 at 02:45:46PM -0500, mlw wrote:
> 
> Are there any reasons why it should not be an option on pg_dump?

I wonder whether that mightn't be the best answer.  Maybe it should
even be the default, and --noanalyse an option.

I agree that from the point of view of simplifying administration,
it'd be a nice addition.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110


Thu, 27 Feb 2003 17:25:03 -0500
Andrew Sullivan <andrew@libertyrms.info>
Re: analyze after a database restore?
On Thu, Feb 27, 2003 at 03:12:36PM -0500, Tom Lane wrote:

> Although I suggested doing a single unconditional ANALYZE at the end
> of the script, second thought leads me to think the per-table ANALYZE
> (probably issued right after the table's data-load step) might be
> better.  That way you'd not have any side-effects on already-existing
> tables in the database you are loading to.  OTOH, that way would leave
> the system catalogs un-analyzed, which might be bad.

Huh... is there a way to analyze a specific schema?  Maybe that can
solve the problem of system catalogs being left un-analyzed, by having
pg_dump emit an "ANALYZE SCHEMA pg_catalog" or something.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La libertad es como el dinero; el que no la sabe emplear la pierde" (Alvarez)

Thu, 27 Feb 2003 19:44:11 -0300
Alvaro Herrera <alvherre@dcc.uchile.cl>
Re: analyze after a database restore?
Tom Lane wrote:
> mlw <pgsql@mohawksoft.com> writes:
> 
>> From an "ease of use" perspective, it would be one less step.
> 
> 
> There is something to be said for that.  As Rod notes, this has been
> considered and rejected before --- but I think that was back when
> ANALYZE (a) could only be done as part of VACUUM, and (b) insisted on
> scanning the whole table.  The current implementation is vastly
> lighter-weight than what we were looking at back then.  Perhaps it's
> time to reconsider.
> 
> Although I suggested doing a single unconditional ANALYZE at the end
> of the script, second thought leads me to think the per-table ANALYZE
> (probably issued right after the table's data-load step) might be
> better.  That way you'd not have any side-effects on already-existing
> tables in the database you are loading to.  OTOH, that way would leave
> the system catalogs un-analyzed, which might be bad.

Analyzing the impact a bit further, which has the more positives/negatives?

  o Adding an ANALYZE (per table or not)

    + Restoring from backup and getting decent performance is more
      reliably, especially for people new to PostgreSQL.

    + Reduces the number of things an admin has to remember or
      checklist

    - Could be redundant if the restore is part of a larger group of
      restores

    - Adds a bit more time to the restoration process (not much through)

  o Leaving it the way it is (no ANALYZE included)

    + Stops the potential problem of it being redundant if the restore
      is part of a larger group of restores

    - The SysAdmin/DBA has to know/remember to do this.  Especially bad
      for people new to PostgreSQL


It seems like the maximum benefits would be on the side of including it, 
and the only real negative from including an ANALYZE would be in the 
extra time taken for it.

Of course we may be forgetting Other Big Consequences.  ;-)

As a thought, we could introduce an option to explicitly not include it, 
but that doesn't feel very worthwhile.

Regards and best wishes,

Justin Clift

> 			regards, tom lane

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi


Fri, 28 Feb 2003 10:16:56 +1030
Justin Clift <justin@postgresql.org>
Re: analyze after a database restore?
> Although I suggested doing a single unconditional ANALYZE at the end
> of the script, second thought leads me to think the per-table ANALYZE
> (probably issued right after the table's data-load step) might be
> better.  That way you'd not have any side-effects on already-existing
> tables in the database you are loading to.  OTOH, that way would leave
> the system catalogs un-analyzed, which might be bad.

How about adding an ANALYZE SCHEMA pg_catalog; option :)

Chris



Fri, 28 Feb 2003 10:05:48 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: analyze after a database restore? 
Attached is a committed patch to add a recommendation for ANALYZE after
restore.  It is a shame we only have vacuumdb -a to do analyze _and_
vacuum, and no analyze-only option.

---------------------------------------------------------------------------

Tom Lane wrote:
> mlw <pgsql@mohawksoft.com> writes:
> >  From an "ease of use" perspective, it would be one less step.
> 
> There is something to be said for that.  As Rod notes, this has been
> considered and rejected before --- but I think that was back when
> ANALYZE (a) could only be done as part of VACUUM, and (b) insisted on
> scanning the whole table.  The current implementation is vastly
> lighter-weight than what we were looking at back then.  Perhaps it's
> time to reconsider.
> 
> Although I suggested doing a single unconditional ANALYZE at the end
> of the script, second thought leads me to think the per-table ANALYZE
> (probably issued right after the table's data-load step) might be
> better.  That way you'd not have any side-effects on already-existing
> tables in the database you are loading to.  OTOH, that way would leave
> the system catalogs un-analyzed, which might be bad.
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Index: doc/src/sgml/backup.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/backup.sgml,v
retrieving revision 2.24
diff -c -c -r2.24 backup.sgml
*** doc/src/sgml/backup.sgml	11 Nov 2002 20:14:02 -0000	2.24
--- doc/src/sgml/backup.sgml	17 Mar 2003 23:58:37 -0000
***************
*** 126,131 ****
--- 126,138 ----
     </para>
  
     <para>
+     Once restored, it is wise to run <command>ANALYZE</> on each
+     database so the optimizer has useful statistics. You
+     can also run <command>vacuumdb -a -z</> to <command>ANALYZE</> all
+     databases.
+    </para>
+ 
+    <para>
      The ability of <application>pg_dump</> and <application>psql</> to
      write to or read from pipes makes it possible to dump a database
      directly from one server to another, for example
Index: doc/src/sgml/ref/pg_dump.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/pg_dump.sgml,v
retrieving revision 1.56
diff -c -c -r1.56 pg_dump.sgml
*** doc/src/sgml/ref/pg_dump.sgml	13 Feb 2003 04:54:15 -0000	1.56
--- doc/src/sgml/ref/pg_dump.sgml	17 Mar 2003 23:58:38 -0000
***************
*** 650,655 ****
--- 650,660 ----
  </programlisting>
    </para>
  
+    <para>
+     Once restored, it is wise to run <command>ANALYZE</> on each
+     restored object so the optimizer has useful statistics.
+    </para>
+ 
    <para>
     <application>pg_dump</application> has a few limitations:
  
***************
*** 682,687 ****
--- 687,698 ----
     other output formats is not limited, except possibly by the
     operating system.
    </para>
+ 
+   <para>
+    Once restored, it is wise to run <command>ANALYZE</> on each
+    restored object so the optimizer has useful statistics.
+   </para>
+ 
   </refsect1>
  
   <refsect1 id="pg-dump-examples">
Index: doc/src/sgml/ref/pg_dumpall.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/pg_dumpall.sgml,v
retrieving revision 1.36
diff -c -c -r1.36 pg_dumpall.sgml
*** doc/src/sgml/ref/pg_dumpall.sgml	6 Jan 2003 18:53:24 -0000	1.36
--- doc/src/sgml/ref/pg_dumpall.sgml	17 Mar 2003 23:58:38 -0000
***************
*** 258,267 ****
     <application>pg_dumpall</application> will need to connect several
     times to the <productname>PostgreSQL</productname> server.  If password
     authentication is configured, it will ask for a password each time. In
!    that case it would be convenient to set up a password file.
    </para>
  
-   <comment>But where is that password file documented?</comment>
   </refsect1>
  
  
--- 258,274 ----
     <application>pg_dumpall</application> will need to connect several
     times to the <productname>PostgreSQL</productname> server.  If password
     authentication is configured, it will ask for a password each time. In
!    that case it would be convenient to set up a <filename>.pgpass</> 
!    password file.
!   </para>
! 
!   <para>
!    Once restored, it is wise to run <command>ANALYZE</> on each
!    database so the optimizer has useful statistics. You
!    can also run <command>vacuumdb -a -z</> to <command>ANALYZE</> all
!    databases.
    </para>
  
   </refsect1>
  
  
Index: doc/src/sgml/ref/pg_restore.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/pg_restore.sgml,v
retrieving revision 1.35
diff -c -c -r1.35 pg_restore.sgml
*** doc/src/sgml/ref/pg_restore.sgml	19 Jan 2003 00:13:31 -0000	1.35
--- doc/src/sgml/ref/pg_restore.sgml	17 Mar 2003 23:58:47 -0000
***************
*** 589,594 ****
--- 589,600 ----
     See also the <xref linkend="app-pgdump"> documentation for details on
     limitations of <application>pg_dump</application>.
    </para>
+ 
+   <para>
+    Once restored, it is wise to run <command>ANALYZE</> on each
+    restored object so the optimizer has useful statistics.
+   </para>
+ 
   </refsect1>
Mon, 17 Mar 2003 19:01:47 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: analyze after a database restore?
Bruce Momjian writes:

> Attached is a committed patch to add a recommendation for ANALYZE after
> restore.  It is a shame we only have vacuumdb -a to do analyze _and_
> vacuum, and no analyze-only option.

Isn't one copy of the text per reference page enough?

-- 
Peter Eisentraut   peter_e@gmx.net


Tue, 18 Mar 2003 15:34:53 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: analyze after a database restore?
Peter Eisentraut wrote:
> Bruce Momjian writes:
> 
> > Attached is a committed patch to add a recommendation for ANALYZE after
> > restore.  It is a shame we only have vacuumdb -a to do analyze _and_
> > vacuum, and no analyze-only option.
> 
> Isn't one copy of the text per reference page enough?

Thanks. Fixed.  There were two in pg_dump by accident.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tue, 18 Mar 2003 12:05:08 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: analyze after a database restore?