doc/src/sgml/ref/analyze.sgml

   1 <!--
   2 doc/src/sgml/ref/analyze.sgml
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="sql-analyze">
   7  <indexterm zone="sql-analyze">
   8   <primary>ANALYZE</primary>
   9  </indexterm>
  10
  11  <refmeta>
  12   <refentrytitle>ANALYZE</refentrytitle>
  13   <manvolnum>7</manvolnum>
  14   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  15  </refmeta>
  16
  17  <refnamediv>
  18   <refname>ANALYZE</refname>
  19   <refpurpose>collect statistics about a database</refpurpose>
  20  </refnamediv>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
  25
  26 <phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
  27
  28     VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
  29     SKIP_LOCKED [ <replaceable class="parameter">boolean</replaceable> ]
  30     BUFFER_USAGE_LIMIT <replaceable class="parameter">size</replaceable>
  31
  32 <phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
  33
  34     [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
  35 </synopsis>
  36  </refsynopsisdiv>
  37
  38  <refsect1>
  39   <title>Description</title>
  40
  41   <para>
  42    <command>ANALYZE</command> collects statistics about the contents
  43    of tables in the database, and stores the results in the <link
  44    linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link>
  45    system catalog.  Subsequently, the query planner uses these
  46    statistics to help determine the most efficient execution plans for
  47    queries.
  48   </para>
  49
  50   <para>
  51    Without a <replaceable class="parameter">table_and_columns</replaceable>
  52    list, <command>ANALYZE</command> processes every table and materialized view
  53    in the current database that the current user has permission to analyze.
  54    With a list, <command>ANALYZE</command> processes only those table(s).
  55    It is further possible to give a list of column names for a table,
  56    in which case only the statistics for those columns are collected.
  57   </para>
  58  </refsect1>
  59
  60  <refsect1>
  61   <title>Parameters</title>
  62
  63   <variablelist>
  64    <varlistentry>
  65     <term><literal>VERBOSE</literal></term>
  66     <listitem>
  67      <para>
  68       Enables display of progress messages at <literal>INFO</literal> level.
  69      </para>
  70     </listitem>
  71    </varlistentry>
  72
  73    <varlistentry>
  74     <term><literal>SKIP_LOCKED</literal></term>
  75     <listitem>
  76      <para>
  77       Specifies that <command>ANALYZE</command> should not wait for any
  78       conflicting locks to be released when beginning work on a relation:
  79       if a relation cannot be locked immediately without waiting, the relation
  80       is skipped.  Note that even with this option, <command>ANALYZE</command>
  81       may still block when opening the relation's indexes or when acquiring
  82       sample rows from partitions, table inheritance children, and some
  83       types of foreign tables.  Also, while <command>ANALYZE</command>
  84       ordinarily processes all partitions of specified partitioned tables,
  85       this option will cause <command>ANALYZE</command> to skip all
  86       partitions if there is a conflicting lock on the partitioned table.
  87      </para>
  88     </listitem>
  89    </varlistentry>
  90
  91    <varlistentry>
  92     <term><literal>BUFFER_USAGE_LIMIT</literal></term>
  93     <listitem>
  94      <para>
  95       Specifies the
  96       <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
  97       ring buffer size for <command>ANALYZE</command>.  This size is used to
  98       calculate the number of shared buffers which will be reused as part of
  99       this strategy.  <literal>0</literal> disables use of a
 100       <literal>Buffer Access Strategy</literal>.   When this option is not
 101       specified, <command>ANALYZE</command> uses the value from
 102       <xref linkend="guc-vacuum-buffer-usage-limit"/>.  Higher settings can
 103       allow <command>ANALYZE</command> to run more quickly, but having too
 104       large a setting may cause too many other useful pages to be evicted from
 105       shared buffers.  The minimum value is <literal>128 kB</literal> and the
 106       maximum value is <literal>16 GB</literal>.
 107      </para>
 108     </listitem>
 109    </varlistentry>
 110
 111    <varlistentry>
 112     <term><replaceable class="parameter">boolean</replaceable></term>
 113     <listitem>
 114      <para>
 115       Specifies whether the selected option should be turned on or off.
 116       You can write <literal>TRUE</literal>, <literal>ON</literal>, or
 117       <literal>1</literal> to enable the option, and <literal>FALSE</literal>,
 118       <literal>OFF</literal>, or <literal>0</literal> to disable it.  The
 119       <replaceable class="parameter">boolean</replaceable> value can also
 120       be omitted, in which case <literal>TRUE</literal> is assumed.
 121      </para>
 122     </listitem>
 123    </varlistentry>
 124
 125    <varlistentry>
 126     <term><replaceable class="parameter">size</replaceable></term>
 127     <listitem>
 128      <para>
 129       Specifies an amount of memory in kilobytes.  Sizes may also be specified
 130       as a string containing the numerical size followed by any one of the
 131       following memory units: <literal>B</literal> (bytes),
 132       <literal>kB</literal> (kilobytes), <literal>MB</literal> (megabytes),
 133       <literal>GB</literal> (gigabytes), or <literal>TB</literal> (terabytes).
 134      </para>
 135     </listitem>
 136    </varlistentry>
 137
 138    <varlistentry>
 139     <term><replaceable class="parameter">table_name</replaceable></term>
 140     <listitem>
 141      <para>
 142       The name (possibly schema-qualified) of a specific table to
 143       analyze.  If omitted, all regular tables, partitioned tables, and
 144       materialized views in the current database are analyzed (but not
 145       foreign tables).  If <literal>ONLY</literal> is specified before
 146       the table name, only that table is analyzed.  If <literal>ONLY</literal>
 147       is not specified, the table and all its inheritance child tables or
 148       partitions (if any) are analyzed.  Optionally, <literal>*</literal>
 149       can be specified after the table name to explicitly indicate that
 150       inheritance child tables (or partitions) are to be analyzed.
 151      </para>
 152     </listitem>
 153    </varlistentry>
 154
 155    <varlistentry>
 156     <term><replaceable class="parameter">column_name</replaceable></term>
 157     <listitem>
 158      <para>
 159       The name of a specific column to analyze. Defaults to all columns.
 160      </para>
 161     </listitem>
 162    </varlistentry>
 163   </variablelist>
 164  </refsect1>
 165
 166  <refsect1>
 167   <title>Outputs</title>
 168
 169    <para>
 170     When <literal>VERBOSE</literal> is specified, <command>ANALYZE</command> emits
 171     progress messages to indicate which table is currently being
 172     processed.  Various statistics about the tables are printed as well.
 173    </para>
 174  </refsect1>
 175
 176  <refsect1>
 177   <title>Notes</title>
 178
 179   <para>
 180    To analyze a table, one must ordinarily have the <literal>MAINTAIN</literal>
 181    privilege on the table.  However, database owners are allowed to
 182    analyze all tables in their databases, except shared catalogs.
 183    <command>ANALYZE</command> will skip over any tables that the calling user
 184    does not have permission to analyze.
 185   </para>
 186
 187   <para>
 188    Foreign tables are analyzed only when explicitly selected.  Not all
 189    foreign data wrappers support <command>ANALYZE</command>.  If the table's
 190    wrapper does not support <command>ANALYZE</command>, the command prints a
 191    warning and does nothing.
 192   </para>
 193
 194   <para>
 195    In the default <productname>PostgreSQL</productname> configuration,
 196    the autovacuum daemon (see <xref linkend="autovacuum"/>)
 197    takes care of automatic analyzing of tables when they are first loaded
 198    with data, and as they change throughout regular operation.
 199    When autovacuum is disabled,
 200    it is a good idea to run <command>ANALYZE</command> periodically, or
 201    just after making major changes in the contents of a table.  Accurate
 202    statistics will help the planner to choose the most appropriate query
 203    plan, and thereby improve the speed of query processing.  A common
 204    strategy for read-mostly databases is to run <link linkend="sql-vacuum"><command>VACUUM</command></link>
 205    and <command>ANALYZE</command> once a day during a low-usage time of day.
 206    (This will not be sufficient if there is heavy update activity.)
 207   </para>
 208
 209   <para>
 210    While <command>ANALYZE</command> is running, the <xref
 211    linkend="guc-search-path"/> is temporarily changed to <literal>pg_catalog,
 212    pg_temp</literal>.
 213   </para>
 214
 215   <para>
 216    <command>ANALYZE</command>
 217    requires only a read lock on the target table, so it can run in
 218    parallel with other non-DDL activity on the table.
 219   </para>
 220
 221   <para>
 222    The statistics collected by <command>ANALYZE</command> usually
 223    include a list of some of the most common values in each column and
 224    a histogram showing the approximate data distribution in each
 225    column.  One or both of these can be omitted if
 226    <command>ANALYZE</command> deems them uninteresting (for example,
 227    in a unique-key column, there are no common values) or if the
 228    column data type does not support the appropriate operators.  There
 229    is more information about the statistics in <xref
 230    linkend="maintenance"/>.
 231   </para>
 232
 233   <para>
 234    For large tables, <command>ANALYZE</command> takes a random sample
 235    of the table contents, rather than examining every row.  This
 236    allows even very large tables to be analyzed in a small amount of
 237    time.  Note, however, that the statistics are only approximate, and
 238    will change slightly each time <command>ANALYZE</command> is run,
 239    even if the actual table contents did not change.  This might result
 240    in small changes in the planner's estimated costs shown by
 241    <link linkend="sql-explain"><command>EXPLAIN</command></link>.
 242    In rare situations, this non-determinism will cause the planner's
 243    choices of query plans to change after <command>ANALYZE</command> is run.
 244    To avoid this, raise the amount of statistics collected by
 245    <command>ANALYZE</command>, as described below.
 246   </para>
 247
 248   <para>
 249    The extent of analysis can be controlled by adjusting the
 250    <xref linkend="guc-default-statistics-target"/> configuration variable, or
 251    on a column-by-column basis by setting the per-column statistics
 252    target with <link linkend="sql-altertable"><command>ALTER TABLE ... ALTER COLUMN ... SET
 253    STATISTICS</command></link>.
 254    The target value sets the
 255    maximum number of entries in the most-common-value list and the
 256    maximum number of bins in the histogram.  The default target value
 257    is 100, but this can be adjusted up or down to trade off accuracy of
 258    planner estimates against the time taken for
 259    <command>ANALYZE</command> and the amount of space occupied in
 260    <literal>pg_statistic</literal>.  In particular, setting the
 261    statistics target to zero disables collection of statistics for
 262    that column.  It might be useful to do that for columns that are
 263    never used as part of the <literal>WHERE</literal>, <literal>GROUP BY</literal>,
 264    or <literal>ORDER BY</literal> clauses of queries, since the planner will
 265    have no use for statistics on such columns.
 266   </para>
 267
 268   <para>
 269    The largest statistics target among the columns being analyzed determines
 270    the number of table rows sampled to prepare the statistics.  Increasing
 271    the target causes a proportional increase in the time and space needed
 272    to do <command>ANALYZE</command>.
 273   </para>
 274
 275   <para>
 276    One of the values estimated by <command>ANALYZE</command> is the number of
 277    distinct values that appear in each column.  Because only a subset of the
 278    rows are examined, this estimate can sometimes be quite inaccurate, even
 279    with the largest possible statistics target.  If this inaccuracy leads to
 280    bad query plans, a more accurate value can be determined manually and then
 281    installed with
 282    <link linkend="sql-altertable"><command>ALTER TABLE ... ALTER COLUMN ... SET (n_distinct = ...)</command></link>.
 283   </para>
 284
 285   <para>
 286     If the table being analyzed has inheritance children,
 287     <command>ANALYZE</command> gathers two sets of statistics: one on the rows
 288     of the parent table only, and a second including rows of both the parent
 289     table and all of its children.  This second set of statistics is needed when
 290     planning queries that process the inheritance tree as a whole.  The
 291     autovacuum daemon, however, will only consider inserts or updates on the
 292     parent table itself when deciding whether to trigger an automatic analyze
 293     for that table.  If that table is rarely inserted into or updated, the
 294     inheritance statistics will not be up to date unless you run
 295     <command>ANALYZE</command> manually.  By default,
 296     <command>ANALYZE</command> will also recursively collect and update the
 297     statistics for each inheritance child table.  The <literal>ONLY</literal>
 298     keyword may be used to disable this.
 299   </para>
 300
 301   <para>
 302     For partitioned tables, <command>ANALYZE</command> gathers statistics by
 303     sampling rows from all partitions.  By default,
 304     <command>ANALYZE</command> will also recursively collect and update the
 305     statistics for each partition.  The <literal>ONLY</literal> keyword may be
 306     used to disable this.
 307   </para>
 308
 309   <para>
 310     The autovacuum daemon does not process partitioned tables, nor does it
 311     process inheritance parents if only the children are ever modified.
 312     It is usually necessary to periodically run a manual
 313     <command>ANALYZE</command> to keep the statistics of the table hierarchy
 314     up to date.
 315   </para>
 316
 317   <para>
 318     If any child tables or partitions are foreign tables whose foreign
 319     data wrappers do not support <command>ANALYZE</command>, those tables are
 320     ignored while gathering inheritance statistics.
 321   </para>
 322
 323   <para>
 324     If the table being analyzed is completely empty, <command>ANALYZE</command>
 325     will not record new statistics for that table.  Any existing statistics
 326     will be retained.
 327   </para>
 328
 329   <para>
 330     Each backend running <command>ANALYZE</command> will report its progress
 331     in the <structname>pg_stat_progress_analyze</structname> view. See
 332     <xref linkend="analyze-progress-reporting"/> for details.
 333   </para>
 334  </refsect1>
 335
 336  <refsect1>
 337   <title>Compatibility</title>
 338
 339   <para>
 340    There is no <command>ANALYZE</command> statement in the SQL standard.
 341   </para>
 342
 343   <para>
 344    The following syntax was used before <productname>PostgreSQL</productname>
 345    version 11 and is still supported:
 346 <synopsis>
 347 ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
 348 </synopsis>
 349   </para>
 350  </refsect1>
 351
 352  <refsect1>
 353   <title>See Also</title>
 354
 355   <simplelist type="inline">
 356    <member><xref linkend="sql-vacuum"/></member>
 357    <member><xref linkend="app-vacuumdb"/></member>
 358    <member><xref linkend="runtime-config-resource-vacuum-cost"/></member>
 359    <member><xref linkend="autovacuum"/></member>
 360    <member><xref linkend="analyze-progress-reporting"/></member>
 361   </simplelist>
 362  </refsect1>
 363 </refentry>