Alvaro Herrera wrote:
> > 3) There is no easy way to analyze all databases. vacuumdb --analyze
> > does analyze _and_ vacuum, which for an 8.4 to 8.5 migration does an
> > unnecessary vacuum. Right now I recommend ANALYZE in every database,
> > but it would be nice if there were a single command which did this.
>
> +1 for vacuumdb --analyze-only
OK, I have implemented this using --only-analyze to avoid having the
'--anal' option spelling be ambiguous, which might confuse/frustrate
users.
I also moved the --freeze option documention mention into a more logical
place.
Patch attached.
--
Bruce Momjian <br...@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/ref/vacuumdb.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/vacuumdb.sgml,v
retrieving revision 1.45
diff -c -c -r1.45 vacuumdb.sgml
*** doc/src/sgml/ref/vacuumdb.sgml 27 Nov 2009 17:41:26 -0000 1.45
--- doc/src/sgml/ref/vacuumdb.sgml 4 Jan 2010 17:21:34 -0000
***************
*** 24,32 ****
<command>vacuumdb</command>
<arg rep="repeat"><replaceable>connection-option</replaceable></arg>
<group><arg>--full</arg><arg>-f</arg></group>
<group><arg>--verbose</arg><arg>-v</arg></group>
<group><arg>--analyze</arg><arg>-z</arg></group>
! <group><arg>--freeze</arg><arg>-F</arg></group>
<arg>--table | -t <replaceable>table</replaceable>
<arg>( <replaceable class="parameter">column</replaceable> [,...] )</arg>
</arg>
--- 24,33 ----
<command>vacuumdb</command>
<arg rep="repeat"><replaceable>connection-option</replaceable></arg>
<group><arg>--full</arg><arg>-f</arg></group>
+ <group><arg>--freeze</arg><arg>-F</arg></group>
<group><arg>--verbose</arg><arg>-v</arg></group>
<group><arg>--analyze</arg><arg>-z</arg></group>
! <group><arg>--only-analyze</arg><arg>-o</arg></group>
<arg>--table | -t <replaceable>table</replaceable>
<arg>( <replaceable class="parameter">column</replaceable> [,...] )</arg>
</arg>
***************
*** 36,44 ****
<arg rep="repeat"><replaceable>connection-options</replaceable></arg>
<group><arg>--all</arg><arg>-a</arg></group>
<group><arg>--full</arg><arg>-f</arg></group>
<group><arg>--verbose</arg><arg>-v</arg></group>
<group><arg>--analyze</arg><arg>-z</arg></group>
! <group><arg>--freeze</arg><arg>-F</arg></group>
</cmdsynopsis>
</refsynopsisdiv>
--- 37,46 ----
<arg rep="repeat"><replaceable>connection-options</replaceable></arg>
<group><arg>--all</arg><arg>-a</arg></group>
<group><arg>--full</arg><arg>-f</arg></group>
+ <group><arg>--freeze</arg><arg>-F</arg></group>
<group><arg>--verbose</arg><arg>-v</arg></group>
<group><arg>--analyze</arg><arg>-z</arg></group>
! <group><arg>--only-analyze</arg><arg>-o</arg></group>
</cmdsynopsis>
</refsynopsisdiv>
***************
*** 56,63 ****
<para>
<application>vacuumdb</application> is a wrapper around the SQL
command <xref linkend="SQL-VACUUM" endterm="SQL-VACUUM-title">.
! There is no effective difference between vacuuming databases via
! this utility and via other methods for accessing the server.
</para>
</refsect1>
--- 58,66 ----
<para>
<application>vacuumdb</application> is a wrapper around the SQL
command <xref linkend="SQL-VACUUM" endterm="SQL-VACUUM-title">.
! There is no effective difference between vacuuming and analyzing
! databases via this utility and via other methods for accessing the
! server.
</para>
</refsect1>
***************
*** 117,122 ****
--- 120,145 ----
</varlistentry>
<varlistentry>
+ <term><option>-F</option></term>
+ <term><option>--freeze</option></term>
+ <listitem>
+ <para>
+ Aggressively <quote>freeze</quote> tuples.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><option>-o</option></term>
+ <term><option>--only-analyze</option></term>
+ <listitem>
+ <para>
+ Only calculate statistics for use by the optimizer (no vacuum).
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><option>-q</></term>
<term><option>--quiet</></term>
<listitem>
***************
*** 133,139 ****
<para>
Clean or analyze <replaceable class="parameter">table</replaceable> only.
Column names can be specified only in conjunction with
! the <option>--analyze</option> option.
</para>
<tip>
<para>
--- 156,162 ----
<para>
Clean or analyze <replaceable class="parameter">table</replaceable> only.
Column names can be specified only in conjunction with
! the <option>--analyze</option> or <option>--only-analyze</option> options.
</para>
<tip>
<para>
***************
*** 164,178 ****
</listitem>
</varlistentry>
- <varlistentry>
- <term><option>-F</option></term>
- <term><option>--freeze</option></term>
- <listitem>
- <para>
- Aggressively <quote>freeze</quote> tuples.
- </para>
- </listitem>
- </varlistentry>
</variablelist>
</para>
--- 187,192 ----
Index: src/bin/scripts/vacuumdb.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/scripts/vacuumdb.c,v
retrieving revision 1.28
diff -c -c -r1.28 vacuumdb.c
*** src/bin/scripts/vacuumdb.c 2 Jan 2010 16:58:00 -0000 1.28
--- src/bin/scripts/vacuumdb.c 4 Jan 2010 17:21:34 -0000
***************
*** 14,25 ****
#include "common.h"
! static void vacuum_one_database(const char *dbname, bool full, bool verbose, bool analyze,
! bool freeze, const char *table,
! const char *host, const char *port,
const char *username, enum trivalue prompt_password,
const char *progname, bool echo);
! static void vacuum_all_databases(bool full, bool verbose, bool analyze, bool freeze,
const char *host, const char *port,
const char *username, enum trivalue prompt_password,
const char *progname, bool echo, bool quiet);
--- 14,26 ----
#include "common.h"
! static void vacuum_one_database(const char *dbname, bool full, bool verbose,
! bool and_analyze, bool only_analyze, bool freeze,
! const char *table, const char *host, const char *port,
const char *username, enum trivalue prompt_password,
const char *progname, bool echo);
! static void vacuum_all_databases(bool full, bool verbose, bool and_analyze,
! bool only_analyze, bool freeze,
const char *host, const char *port,
const char *username, enum trivalue prompt_password,
const char *progname, bool echo, bool quiet);
***************
*** 40,45 ****
--- 41,47 ----
{"quiet", no_argument, NULL, 'q'},
{"dbname", required_argument, NULL, 'd'},
{"analyze", no_argument, NULL, 'z'},
+ {"only-analyze", no_argument, NULL, 'o'},
{"freeze", no_argument, NULL, 'F'},
{"all", no_argument, NULL, 'a'},
{"table", required_argument, NULL, 't'},
***************
*** 59,65 ****
enum trivalue prompt_password = TRI_DEFAULT;
bool echo = false;
bool quiet = false;
! bool analyze = false;
bool freeze = false;
bool alldb = false;
char *table = NULL;
--- 61,68 ----
enum trivalue prompt_password = TRI_DEFAULT;
bool echo = false;
bool quiet = false;
! bool and_analyze = false;
! bool only_analyze = false;
bool freeze = false;
bool alldb = false;
char *table = NULL;
***************
*** 100,106 ****
dbname = optarg;
break;
case 'z':
! analyze = true;
break;
case 'F':
freeze = true;
--- 103,112 ----
dbname = optarg;
break;
case 'z':
! and_analyze = true;
! break;
! case 'o':
! only_analyze = true;
break;
case 'F':
freeze = true;
***************
*** 139,144 ****
--- 145,167 ----
setup_cancel_handler();
+ if (only_analyze)
+ {
+ if (full)
+ {
+ fprintf(stderr, _("%s: cannot use the \"full\" option when performing only analyze\n"),
+ progname);
+ exit(1);
+ }
+ if (freeze)
+ {
+ fprintf(stderr, _("%s: cannot use the \"freeze\" option when performing only analyze\n"),
+ progname);
+ exit(1);
+ }
+ /* ignore 'and_analyze' */
+ }
+
if (alldb)
{
if (dbname)
***************
*** 154,160 ****
exit(1);
}
! vacuum_all_databases(full, verbose, analyze, freeze,
host, port, username, prompt_password,
progname, echo, quiet);
}
--- 177,183 ----
exit(1);
}
! vacuum_all_databases(full, verbose, and_analyze, only_analyze, freeze,
host, port, username, prompt_password,
progname, echo, quiet);
}
***************
*** 170,176 ****
dbname = get_user_name(progname);
}
! vacuum_one_database(dbname, full, verbose, analyze, freeze, table,
host, port, username, prompt_password,
progname, echo);
}
--- 193,200 ----
dbname = get_user_name(progname);
}
! vacuum_one_database(dbname, full, verbose, and_analyze, only_analyze,
! freeze, table,
host, port, username, prompt_password,
progname, echo);
}
***************
*** 180,187 ****
static void
! vacuum_one_database(const char *dbname, bool full, bool verbose, bool analyze,
! bool freeze, const char *table,
const char *host, const char *port,
const char *username, enum trivalue prompt_password,
const char *progname, bool echo)
--- 204,211 ----
static void
! vacuum_one_database(const char *dbname, bool full, bool verbose, bool and_analyze,
! bool only_analyze, bool freeze, const char *table,
const char *host, const char *port,
const char *username, enum trivalue prompt_password,
const char *progname, bool echo)
***************
*** 192,206 ****
initPQExpBuffer(&sql);
! appendPQExpBuffer(&sql, "VACUUM");
! if (full)
! appendPQExpBuffer(&sql, " FULL");
! if (freeze)
! appendPQExpBuffer(&sql, " FREEZE");
if (verbose)
appendPQExpBuffer(&sql, " VERBOSE");
- if (analyze)
- appendPQExpBuffer(&sql, " ANALYZE");
if (table)
appendPQExpBuffer(&sql, " %s", table);
appendPQExpBuffer(&sql, ";\n");
--- 216,235 ----
initPQExpBuffer(&sql);
! if (only_analyze)
! appendPQExpBuffer(&sql, "ANALYZE");
! else
! {
! appendPQExpBuffer(&sql, "VACUUM");
! if (full)
! appendPQExpBuffer(&sql, " FULL");
! if (freeze)
! appendPQExpBuffer(&sql, " FREEZE");
! if (and_analyze)
! appendPQExpBuffer(&sql, " ANALYZE");
! }
if (verbose)
appendPQExpBuffer(&sql, " VERBOSE");
if (table)
appendPQExpBuffer(&sql, " %s", table);
appendPQExpBuffer(&sql, ";\n");
***************
*** 223,230 ****
static void
! vacuum_all_databases(bool full, bool verbose, bool analyze, bool freeze,
! const char *host, const char *port,
const char *username, enum trivalue prompt_password,
const char *progname, bool echo, bool quiet)
{
--- 252,259 ----
static void
! vacuum_all_databases(bool full, bool verbose, bool and_analyze, bool only_analyze,
! bool freeze, const char *host, const char *port,
const char *username, enum trivalue prompt_password,
const char *progname, bool echo, bool quiet)
{
***************
*** 246,253 ****
fflush(stdout);
}
! vacuum_one_database(dbname, full, verbose, analyze, freeze, NULL,
! host, port, username, prompt_password,
progname, echo);
}
--- 275,282 ----
fflush(stdout);
}
! vacuum_one_database(dbname, full, verbose, and_analyze, only_analyze,
! freeze, NULL, host, port, username, prompt_password,
progname, echo);
}
***************
*** 267,272 ****
--- 296,302 ----
printf(_(" -e, --echo show the commands being sent to the server\n"));
printf(_(" -f, --full do full vacuuming\n"));
printf(_(" -F, --freeze freeze row transaction information\n"));
+ printf(_(" -o, --only-analyze only update optimizer hints\n"));
printf(_(" -q, --quiet don't write any messages\n"));
printf(_(" -t, --table='TABLE[(COLUMNS)]' vacuum specific table only\n"));
printf(_(" -v, --verbose write a lot of output\n"));
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
