doc/src/sgml/ref/analyze.sgml

   1 <!--
   2 $PostgreSQL$
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-ANALYZE">
   7  <refmeta>
   8   <refentrytitle id="sql-analyze-title">ANALYZE</refentrytitle>
   9   <manvolnum>7</manvolnum>
  10   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  11  </refmeta>
  12
  13  <refnamediv>
  14   <refname>ANALYZE</refname>
  15   <refpurpose>collect statistics about a database</refpurpose>
  16  </refnamediv>
  17
  18  <indexterm zone="sql-analyze">
  19   <primary>ANALYZE</primary>
  20  </indexterm>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 ANALYZE [ VERBOSE ] [ <replaceable class="PARAMETER">table</replaceable> [ ( <replaceable class="PARAMETER">column</replaceable> [, ...] ) ] ]
  25 </synopsis>
  26  </refsynopsisdiv>
  27
  28  <refsect1>
  29   <title>Description</title>
  30
  31   <para>
  32    <command>ANALYZE</command> collects statistics about the contents
  33    of tables in the database, and stores the results in the <link
  34    linkend="catalog-pg-statistic"><structname>pg_statistic</></>
  35    system catalog.  Subsequently, the query planner uses these
  36    statistics to help determine the most efficient execution plans for
  37    queries.
  38   </para>
  39
  40   <para>
  41    With no parameter, <command>ANALYZE</command> examines every table in the
  42    current database.  With a parameter, <command>ANALYZE</command> examines
  43    only that table.  It is further possible to give a list of column names,
  44    in which case only the statistics for those columns are collected.
  45   </para>
  46  </refsect1>
  47
  48  <refsect1>
  49   <title>Parameters</title>
  50
  51   <variablelist>
  52    <varlistentry>
  53     <term><literal>VERBOSE</literal></term>
  54     <listitem>
  55      <para>
  56       Enables display of progress messages.
  57      </para>
  58     </listitem>
  59    </varlistentry>
  60
  61    <varlistentry>
  62     <term><replaceable class="PARAMETER">table</replaceable></term>
  63     <listitem>
  64      <para>
  65       The name (possibly schema-qualified) of a specific table to
  66       analyze. Defaults to all tables in the current database.
  67      </para>
  68     </listitem>
  69    </varlistentry>
  70
  71    <varlistentry>
  72     <term><replaceable class="PARAMETER">column</replaceable></term>
  73     <listitem>
  74      <para>
  75       The name of a specific column to analyze. Defaults to all columns.
  76      </para>
  77     </listitem>
  78    </varlistentry>
  79   </variablelist>
  80  </refsect1>
  81
  82  <refsect1>
  83   <title>Outputs</title>
  84
  85    <para>
  86     When <literal>VERBOSE</> is specified, <command>ANALYZE</> emits
  87     progress messages to indicate which table is currently being
  88     processed.  Various statistics about the tables are printed as well.
  89    </para>
  90  </refsect1>
  91
  92  <refsect1>
  93   <title>Notes</title>
  94
  95   <para>
  96    In the default <productname>PostgreSQL</productname> configuration,
  97    <xref linkend="autovacuum" endterm="autovacuum-title">
  98    takes care of automatic analyzing of tables when they are first loaded
  99    with data, and as they change throughout regular operation.
 100    When autovacuum is disabled,
 101    it is a good idea to run <command>ANALYZE</command> periodically, or
 102    just after making major changes in the contents of a table.  Accurate
 103    statistics will help the planner to choose the most appropriate query
 104    plan, and thereby improve the speed of query processing.  A common
 105    strategy is to run <xref linkend="sql-vacuum" endterm="sql-vacuum-title">
 106    and <command>ANALYZE</command> once a day during a low-usage time of day.
 107   </para>
 108
 109   <para>
 110    <command>ANALYZE</command>
 111    requires only a read lock on the target table, so it can run in
 112    parallel with other activity on the table.
 113   </para>
 114
 115   <para>
 116    The statistics collected by <command>ANALYZE</command> usually
 117    include a list of some of the most common values in each column and
 118    a histogram showing the approximate data distribution in each
 119    column.  One or both of these can be omitted if
 120    <command>ANALYZE</command> deems them uninteresting (for example,
 121    in a unique-key column, there are no common values) or if the
 122    column data type does not support the appropriate operators.  There
 123    is more information about the statistics in <xref
 124    linkend="maintenance">.
 125   </para>
 126
 127   <para>
 128    For large tables, <command>ANALYZE</command> takes a random sample
 129    of the table contents, rather than examining every row.  This
 130    allows even very large tables to be analyzed in a small amount of
 131    time.  Note, however, that the statistics are only approximate, and
 132    will change slightly each time <command>ANALYZE</command> is run,
 133    even if the actual table contents did not change.  This might result
 134    in small changes in the planner's estimated costs shown by
 135    <xref linkend="sql-explain" endterm="sql-explain-title">.
 136    In rare situations, this non-determinism will cause the planner's
 137    choices of query plans to change after <command>ANALYZE</command> is run.
 138    To avoid this, raise the amount of statistics collected by
 139    <command>ANALYZE</command>, as described below.
 140   </para>
 141
 142   <para>
 143    The extent of analysis can be controlled by adjusting the
 144    <xref linkend="guc-default-statistics-target"> configuration variable, or
 145    on a column-by-column basis by setting the per-column statistics
 146    target with <command>ALTER TABLE ... ALTER COLUMN ... SET
 147    STATISTICS</command> (see <xref linkend="sql-altertable"
 148    endterm="sql-altertable-title">).  The target value sets the
 149    maximum number of entries in the most-common-value list and the
 150    maximum number of bins in the histogram.  The default target value
 151    is 100, but this can be adjusted up or down to trade off accuracy of
 152    planner estimates against the time taken for
 153    <command>ANALYZE</command> and the amount of space occupied in
 154    <literal>pg_statistic</literal>.  In particular, setting the
 155    statistics target to zero disables collection of statistics for
 156    that column.  It might be useful to do that for columns that are
 157    never used as part of the <literal>WHERE</>, <literal>GROUP BY</>,
 158    or <literal>ORDER BY</> clauses of queries, since the planner will
 159    have no use for statistics on such columns.
 160   </para>
 161
 162   <para>
 163    The largest statistics target among the columns being analyzed determines
 164    the number of table rows sampled to prepare the statistics.  Increasing
 165    the target causes a proportional increase in the time and space needed
 166    to do <command>ANALYZE</command>.
 167   </para>
 168  </refsect1>
 169
 170  <refsect1>
 171   <title>Compatibility</title>
 172
 173   <para>
 174    There is no <command>ANALYZE</command> statement in the SQL standard.
 175   </para>
 176  </refsect1>
 177
 178  <refsect1>
 179   <title>See Also</title>
 180
 181   <simplelist type="inline">
 182    <member><xref linkend="sql-vacuum" endterm="sql-vacuum-title"></member>
 183    <member><xref linkend="app-vacuumdb" endterm="app-vacuumdb-title"></member>
 184    <member><xref linkend="runtime-config-resource-vacuum-cost" endterm="runtime-config-resource-vacuum-cost-title"></member>
 185    <member><xref linkend="autovacuum" endterm="autovacuum-title"></member>
 186   </simplelist>
 187  </refsect1>
 188 </refentry>