Tom Lane wrote:
> Alvaro Herrera <[email protected]> writes:
> > Bruce asked me to look for places in the docs that mention that an
> > ANALYZE is recommended, to mention the possibility that autovacuum takes
> > care. This patch does that.
>
> I think you found the right places to touch, but is "let the autovacuum
> daemon do it" sufficient? It seems like that needs some qualifiers
> about whether autovacuum is enabled, how long you should expect to wait
> for the stats to get updated, etc. It's probably not a good idea to
> duplicate all that in each place, but maybe a link to the main
> documentation about autovacuum is reasonable in each place.
Sorry this fell through the cracks. How does this look? My idea would
be to backpatch it to 8.3 and 8.4.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
Index: doc/src/sgml/backup.sgml
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/doc/src/sgml/backup.sgml,v
retrieving revision 2.129
diff -c -p -r2.129 backup.sgml
*** doc/src/sgml/backup.sgml 26 Jun 2009 22:06:11 -0000 2.129
--- doc/src/sgml/backup.sgml 6 Aug 2009 21:10:41 -0000
*************** pg_dump -h <replaceable>host1</> <replac
*** 168,177 ****
<para>
After restoring a backup, it is wise to run <xref
linkend="sql-analyze" endterm="sql-analyze-title"> on each
! database so the query optimizer has useful statistics. An easy way
! to do this is to run <command>vacuumdb -a -z</>; this is
! equivalent to running <command>VACUUM ANALYZE</> on each database
! manually. For more advice on how to load large amounts of data
into <productname>PostgreSQL</> efficiently, refer to <xref
linkend="populate">.
</para>
--- 168,177 ----
<para>
After restoring a backup, it is wise to run <xref
linkend="sql-analyze" endterm="sql-analyze-title"> on each
! database so the query optimizer has useful statistics;
! see <xref linkend="vacuum-for-statistics" endterm="vacuum-for-statistics-title">
! and <xref linkend="autovacuum" endterm="autovacuum-title"> for more information.
! For more advice on how to load large amounts of data
into <productname>PostgreSQL</> efficiently, refer to <xref
linkend="populate">.
</para>
Index: doc/src/sgml/indices.sgml
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/doc/src/sgml/indices.sgml,v
retrieving revision 1.78
diff -c -p -r1.78 indices.sgml
*** doc/src/sgml/indices.sgml 17 Jun 2009 21:58:49 -0000 1.78
--- doc/src/sgml/indices.sgml 6 Aug 2009 21:11:09 -0000
*************** SELECT am.amname AS index_method,
*** 1025,1031 ****
real statistics, some default values are assumed, which are
almost certain to be inaccurate. Examining an application's
index usage without having run <command>ANALYZE</command> is
! therefore a lost cause.
</para>
</listitem>
--- 1025,1033 ----
real statistics, some default values are assumed, which are
almost certain to be inaccurate. Examining an application's
index usage without having run <command>ANALYZE</command> is
! therefore a lost cause.
! See <xref linkend="vacuum-for-statistics" endterm="vacuum-for-statistics-title">
! and <xref linkend="autovacuum" endterm="autovacuum-title"> for more information.
</para>
</listitem>
Index: doc/src/sgml/maintenance.sgml
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/doc/src/sgml/maintenance.sgml,v
retrieving revision 1.95
diff -c -p -r1.95 maintenance.sgml
*** doc/src/sgml/maintenance.sgml 17 Jun 2009 13:59:28 -0000 1.95
--- doc/src/sgml/maintenance.sgml 6 Aug 2009 20:12:00 -0000
***************
*** 253,259 ****
</sect2>
<sect2 id="vacuum-for-statistics">
! <title>Updating Planner Statistics</title>
<indexterm zone="vacuum-for-statistics">
<primary>statistics</primary>
--- 253,259 ----
</sect2>
<sect2 id="vacuum-for-statistics">
! <title id="vacuum-for-statistics-title">Updating Planner Statistics</title>
<indexterm zone="vacuum-for-statistics">
<primary>statistics</primary>
Index: doc/src/sgml/perform.sgml
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/doc/src/sgml/perform.sgml,v
retrieving revision 1.71
diff -c -p -r1.71 perform.sgml
*** doc/src/sgml/perform.sgml 17 Jun 2009 21:58:49 -0000 1.71
--- doc/src/sgml/perform.sgml 6 Aug 2009 21:11:36 -0000
*************** SELECT * FROM x, y, a, b, c WHERE someth
*** 974,980 ****
table. With no statistics or obsolete statistics, the planner might
make poor decisions during query planning, leading to poor
performance on any tables with inaccurate or nonexistent
! statistics.
</para>
</sect2>
--- 974,983 ----
table. With no statistics or obsolete statistics, the planner might
make poor decisions during query planning, leading to poor
performance on any tables with inaccurate or nonexistent
! statistics. Note that if the autovacuum daemon is enabled, it might
! run <command>ANALYZE</command> automatically; see
! <xref linkend="vacuum-for-statistics" endterm="vacuum-for-statistics-title">
! and <xref linkend="autovacuum" endterm="autovacuum-title"> for more information.
</para>
</sect2>
*************** SELECT * FROM x, y, a, b, c WHERE someth
*** 1054,1060 ****
while loading the data, but don't bother increasing
<varname>maintenance_work_mem</varname>; rather, you'd do that while
manually recreating indexes and foreign keys afterwards.
! And don't forget to <command>ANALYZE</> when you're done.
</para>
</sect2>
</sect1>
--- 1057,1065 ----
while loading the data, but don't bother increasing
<varname>maintenance_work_mem</varname>; rather, you'd do that while
manually recreating indexes and foreign keys afterwards.
! And don't forget to <command>ANALYZE</> when you're done; see
! <xref linkend="vacuum-for-statistics" endterm="vacuum-for-statistics-title">
! and <xref linkend="autovacuum" endterm="autovacuum-title"> for more information.
</para>
</sect2>
</sect1>
Index: doc/src/sgml/ref/pg_dump.sgml
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/doc/src/sgml/ref/pg_dump.sgml,v
retrieving revision 1.113
diff -c -p -r1.113 pg_dump.sgml
*** doc/src/sgml/ref/pg_dump.sgml 22 Mar 2009 16:44:26 -0000 1.113
--- doc/src/sgml/ref/pg_dump.sgml 6 Aug 2009 21:11:59 -0000
*************** CREATE DATABASE foo WITH TEMPLATE templa
*** 834,840 ****
does not contain the statistics used by the optimizer to make
query planning decisions. Therefore, it is wise to run
<command>ANALYZE</command> after restoring from a dump file
! to ensure good performance. The dump file also does not
contain any <command>ALTER DATABASE ... SET</> commands;
these settings are dumped by <xref linkend="app-pg-dumpall">,
along with database users and other installation-wide settings.
--- 834,842 ----
does not contain the statistics used by the optimizer to make
query planning decisions. Therefore, it is wise to run
<command>ANALYZE</command> after restoring from a dump file
! to ensure good performance; see <xref linkend="vacuum-for-statistics">
! and <xref linkend="autovacuum"> for more information.
! The dump file also does not
contain any <command>ALTER DATABASE ... SET</> commands;
these settings are dumped by <xref linkend="app-pg-dumpall">,
along with database users and other installation-wide settings.
Index: doc/src/sgml/ref/pg_restore.sgml
===================================================================
RCS file: /home/alvherre/Code/cvs/pgsql/doc/src/sgml/ref/pg_restore.sgml,v
retrieving revision 1.81
diff -c -p -r1.81 pg_restore.sgml
*** doc/src/sgml/ref/pg_restore.sgml 20 Mar 2009 09:21:08 -0000 1.81
--- doc/src/sgml/ref/pg_restore.sgml 6 Aug 2009 21:12:24 -0000
*************** CREATE DATABASE foo WITH TEMPLATE templa
*** 679,685 ****
<para>
Once restored, it is wise to run <command>ANALYZE</> on each
! restored table so the optimizer has useful statistics.
</para>
</refsect1>
--- 679,687 ----
<para>
Once restored, it is wise to run <command>ANALYZE</> on each
! restored table so the optimizer has useful statistics; see
! <xref linkend="vacuum-for-statistics"> and
! <xref linkend="autovacuum"> for more information.
</para>
</refsect1>
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
