Adding upstream version 16.2.upstream/16.2

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 355 insertions, 0 deletions

diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
new file mode 100644
index 0000000..73fa3b3
--- /dev/null
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -0,0 +1,355 @@
+<!--
+doc/src/sgml/ref/analyze.sgml
+PostgreSQL documentation
+-->
+
+<refentry id="sql-analyze">
+ <indexterm zone="sql-analyze">
+  <primary>ANALYZE</primary>
+ </indexterm>
+
+ <refmeta>
+  <refentrytitle>ANALYZE</refentrytitle>
+  <manvolnum>7</manvolnum>
+  <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+
+ <refnamediv>
+  <refname>ANALYZE</refname>
+  <refpurpose>collect statistics about a database</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+ANALYZE [ ( <replaceable class="parameter">option</replaceable> [, ...] ) ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
+ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replaceable> [, ...] ]
+
+<phrase>where <replaceable class="parameter">option</replaceable> can be one of:</phrase>
+
+    VERBOSE [ <replaceable class="parameter">boolean</replaceable> ]
+    SKIP_LOCKED [ <replaceable class="parameter">boolean</replaceable> ]
+    BUFFER_USAGE_LIMIT <replaceable class="parameter">size</replaceable>
+
+<phrase>and <replaceable class="parameter">table_and_columns</replaceable> is:</phrase>
+
+    <replaceable class="parameter">table_name</replaceable> [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+  <title>Description</title>
+
+  <para>
+   <command>ANALYZE</command> collects statistics about the contents
+   of tables in the database, and stores the results in the <link
+   linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link>
+   system catalog.  Subsequently, the query planner uses these
+   statistics to help determine the most efficient execution plans for
+   queries.
+  </para>
+
+  <para>
+   Without a <replaceable class="parameter">table_and_columns</replaceable>
+   list, <command>ANALYZE</command> processes every table and materialized view
+   in the current database that the current user has permission to analyze.
+   With a list, <command>ANALYZE</command> processes only those table(s).
+   It is further possible to give a list of column names for a table,
+   in which case only the statistics for those columns are collected.
+  </para>
+
+  <para>
+   When the option list is surrounded by parentheses, the options can be
+   written in any order.  The parenthesized syntax was added in
+   <productname>PostgreSQL</productname> 11;  the unparenthesized syntax
+   is deprecated.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Parameters</title>
+
+  <variablelist>
+   <varlistentry>
+    <term><literal>VERBOSE</literal></term>
+    <listitem>
+     <para>
+      Enables display of progress messages.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><literal>SKIP_LOCKED</literal></term>
+    <listitem>
+     <para>
+      Specifies that <command>ANALYZE</command> should not wait for any
+      conflicting locks to be released when beginning work on a relation:
+      if a relation cannot be locked immediately without waiting, the relation
+      is skipped.  Note that even with this option, <command>ANALYZE</command>
+      may still block when opening the relation's indexes or when acquiring
+      sample rows from partitions, table inheritance children, and some
+      types of foreign tables.  Also, while <command>ANALYZE</command>
+      ordinarily processes all partitions of specified partitioned tables,
+      this option will cause <command>ANALYZE</command> to skip all
+      partitions if there is a conflicting lock on the partitioned table.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><literal>BUFFER_USAGE_LIMIT</literal></term>
+    <listitem>
+     <para>
+      Specifies the
+      <glossterm linkend="glossary-buffer-access-strategy">Buffer Access Strategy</glossterm>
+      ring buffer size for <command>ANALYZE</command>.  This size is used to
+      calculate the number of shared buffers which will be reused as part of
+      this strategy.  <literal>0</literal> disables use of a
+      <literal>Buffer Access Strategy</literal>.   When this option is not
+      specified, <command>ANALYZE</command> uses the value from
+      <xref linkend="guc-vacuum-buffer-usage-limit"/>.  Higher settings can
+      allow <command>ANALYZE</command> to run more quickly, but having too
+      large a setting may cause too many other useful pages to be evicted from
+      shared buffers.  The minimum value is <literal>128 kB</literal> and the
+      maximum value is <literal>16 GB</literal>.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="parameter">boolean</replaceable></term>
+    <listitem>
+     <para>
+      Specifies whether the selected option should be turned on or off.
+      You can write <literal>TRUE</literal>, <literal>ON</literal>, or
+      <literal>1</literal> to enable the option, and <literal>FALSE</literal>,
+      <literal>OFF</literal>, or <literal>0</literal> to disable it.  The
+      <replaceable class="parameter">boolean</replaceable> value can also
+      be omitted, in which case <literal>TRUE</literal> is assumed.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="parameter">size</replaceable></term>
+    <listitem>
+     <para>
+      Specifies an amount of memory in kilobytes.  Sizes may also be specified
+      as a string containing the numerical size followed by any one of the
+      following memory units: <literal>B</literal> (bytes),
+      <literal>kB</literal> (kilobytes), <literal>MB</literal> (megabytes),
+      <literal>GB</literal> (gigabytes), or <literal>TB</literal> (terabytes).
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="parameter">table_name</replaceable></term>
+    <listitem>
+     <para>
+      The name (possibly schema-qualified) of a specific table to
+      analyze.  If omitted, all regular tables, partitioned tables, and
+      materialized views in the current database are analyzed (but not
+      foreign tables).  If the specified table is a partitioned table, both the
+      inheritance statistics of the partitioned table as a whole and
+      statistics of the individual partitions are updated.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="parameter">column_name</replaceable></term>
+    <listitem>
+     <para>
+      The name of a specific column to analyze. Defaults to all columns.
+     </para>
+    </listitem>
+   </varlistentry>
+  </variablelist>
+ </refsect1>
+
+ <refsect1>
+  <title>Outputs</title>
+
+   <para>
+    When <literal>VERBOSE</literal> is specified, <command>ANALYZE</command> emits
+    progress messages to indicate which table is currently being
+    processed.  Various statistics about the tables are printed as well.
+   </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Notes</title>
+
+  <para>
+   To analyze a table, one must ordinarily be the table's owner or a
+   superuser.  However, database owners are allowed to
+   analyze all tables in their databases, except shared catalogs.
+   (The restriction for shared catalogs means that a true database-wide
+   <command>ANALYZE</command> can only be performed by a superuser.)
+   <command>ANALYZE</command> will skip over any tables that the calling user
+   does not have permission to analyze.
+  </para>
+
+  <para>
+   Foreign tables are analyzed only when explicitly selected.  Not all
+   foreign data wrappers support <command>ANALYZE</command>.  If the table's
+   wrapper does not support <command>ANALYZE</command>, the command prints a
+   warning and does nothing.
+  </para>
+
+  <para>
+   In the default <productname>PostgreSQL</productname> configuration,
+   the autovacuum daemon (see <xref linkend="autovacuum"/>)
+   takes care of automatic analyzing of tables when they are first loaded
+   with data, and as they change throughout regular operation.
+   When autovacuum is disabled,
+   it is a good idea to run <command>ANALYZE</command> periodically, or
+   just after making major changes in the contents of a table.  Accurate
+   statistics will help the planner to choose the most appropriate query
+   plan, and thereby improve the speed of query processing.  A common
+   strategy for read-mostly databases is to run <link linkend="sql-vacuum"><command>VACUUM</command></link>
+   and <command>ANALYZE</command> once a day during a low-usage time of day.
+   (This will not be sufficient if there is heavy update activity.)
+  </para>
+
+  <para>
+   <command>ANALYZE</command>
+   requires only a read lock on the target table, so it can run in
+   parallel with other activity on the table.
+  </para>
+
+  <para>
+   The statistics collected by <command>ANALYZE</command> usually
+   include a list of some of the most common values in each column and
+   a histogram showing the approximate data distribution in each
+   column.  One or both of these can be omitted if
+   <command>ANALYZE</command> deems them uninteresting (for example,
+   in a unique-key column, there are no common values) or if the
+   column data type does not support the appropriate operators.  There
+   is more information about the statistics in <xref
+   linkend="maintenance"/>.
+  </para>
+
+  <para>
+   For large tables, <command>ANALYZE</command> takes a random sample
+   of the table contents, rather than examining every row.  This
+   allows even very large tables to be analyzed in a small amount of
+   time.  Note, however, that the statistics are only approximate, and
+   will change slightly each time <command>ANALYZE</command> is run,
+   even if the actual table contents did not change.  This might result
+   in small changes in the planner's estimated costs shown by
+   <link linkend="sql-explain"><command>EXPLAIN</command></link>.
+   In rare situations, this non-determinism will cause the planner's
+   choices of query plans to change after <command>ANALYZE</command> is run.
+   To avoid this, raise the amount of statistics collected by
+   <command>ANALYZE</command>, as described below.
+  </para>
+
+  <para>
+   The extent of analysis can be controlled by adjusting the
+   <xref linkend="guc-default-statistics-target"/> configuration variable, or
+   on a column-by-column basis by setting the per-column statistics
+   target with <link linkend="sql-altertable"><command>ALTER TABLE ... ALTER COLUMN ... SET
+   STATISTICS</command></link>.
+   The target value sets the
+   maximum number of entries in the most-common-value list and the
+   maximum number of bins in the histogram.  The default target value
+   is 100, but this can be adjusted up or down to trade off accuracy of
+   planner estimates against the time taken for
+   <command>ANALYZE</command> and the amount of space occupied in
+   <literal>pg_statistic</literal>.  In particular, setting the
+   statistics target to zero disables collection of statistics for
+   that column.  It might be useful to do that for columns that are
+   never used as part of the <literal>WHERE</literal>, <literal>GROUP BY</literal>,
+   or <literal>ORDER BY</literal> clauses of queries, since the planner will
+   have no use for statistics on such columns.
+  </para>
+
+  <para>
+   The largest statistics target among the columns being analyzed determines
+   the number of table rows sampled to prepare the statistics.  Increasing
+   the target causes a proportional increase in the time and space needed
+   to do <command>ANALYZE</command>.
+  </para>
+
+  <para>
+   One of the values estimated by <command>ANALYZE</command> is the number of
+   distinct values that appear in each column.  Because only a subset of the
+   rows are examined, this estimate can sometimes be quite inaccurate, even
+   with the largest possible statistics target.  If this inaccuracy leads to
+   bad query plans, a more accurate value can be determined manually and then
+   installed with
+   <link linkend="sql-altertable"><command>ALTER TABLE ... ALTER COLUMN ... SET (n_distinct = ...)</command></link>.
+  </para>
+
+  <para>
+    If the table being analyzed has inheritance children,
+    <command>ANALYZE</command> gathers two sets of statistics: one on the rows
+    of the parent table only, and a second including rows of both the parent
+    table and all of its children.  This second set of statistics is needed when
+    planning queries that process the inheritance tree as a whole.  The child
+    tables themselves are not individually analyzed in this case.
+    The autovacuum daemon, however, will only consider inserts or
+    updates on the parent table itself when deciding whether to trigger an
+    automatic analyze for that table.  If that table is rarely inserted into
+    or updated, the inheritance statistics will not be up to date unless you
+    run <command>ANALYZE</command> manually.
+  </para>
+
+  <para>
+    For partitioned tables, <command>ANALYZE</command> gathers statistics by
+    sampling rows from all partitions; in addition, it will recurse into each
+    partition and update its statistics.  Each leaf partition is analyzed only
+    once, even with multi-level partitioning.  No statistics are collected for
+    only the parent table (without data from its partitions), because with
+    partitioning it's guaranteed to be empty.
+  </para>
+
+  <para>
+    The autovacuum daemon does not process partitioned tables, nor does it
+    process inheritance parents if only the children are ever modified.
+    It is usually necessary to periodically run a manual
+    <command>ANALYZE</command> to keep the statistics of the table hierarchy
+    up to date.
+  </para>
+
+  <para>
+    If any child tables or partitions are foreign tables whose foreign
+    data wrappers do not support <command>ANALYZE</command>, those tables are
+    ignored while gathering inheritance statistics.
+  </para>
+
+  <para>
+    If the table being analyzed is completely empty, <command>ANALYZE</command>
+    will not record new statistics for that table.  Any existing statistics
+    will be retained.
+  </para>
+
+  <para>
+    Each backend running <command>ANALYZE</command> will report its progress
+    in the <structname>pg_stat_progress_analyze</structname> view. See
+    <xref linkend="analyze-progress-reporting"/> for details.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>Compatibility</title>
+
+  <para>
+   There is no <command>ANALYZE</command> statement in the SQL standard.
+  </para>
+ </refsect1>
+
+ <refsect1>
+  <title>See Also</title>
+
+  <simplelist type="inline">
+   <member><xref linkend="sql-vacuum"/></member>
+   <member><xref linkend="app-vacuumdb"/></member>
+   <member><xref linkend="runtime-config-resource-vacuum-cost"/></member>
+   <member><xref linkend="autovacuum"/></member>
+   <member><xref linkend="analyze-progress-reporting"/></member>
+  </simplelist>
+ </refsect1>
+</refentry>