Home • Explore • Blog • CI
☰



postgresql
2nd chunk of `doc/src/sgml/ref/create_statistics.sgml`
fa6a47a95897433781b6f1a02d27067366f839fed48e76080000000100000fb3
 the same name already
      exists.  A notice is issued in this case.  Note that only the name of
      the statistics object is considered here, not the details of its
      definition.
      Statistics name is required when <literal>IF NOT EXISTS</literal> is specified.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">statistics_name</replaceable></term>
    <listitem>
     <para>
      The name (optionally schema-qualified) of the statistics object to be
      created.
      If the name is omitted, <productname>PostgreSQL</productname> chooses a
      suitable name based on the parent table's name and the defined column
      name(s) and/or expression(s).
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">statistics_kind</replaceable></term>
    <listitem>
     <para>
      A multivariate statistics kind to be computed in this statistics object.
      Currently supported kinds are
      <literal>ndistinct</literal>, which enables n-distinct statistics,
      <literal>dependencies</literal>, which enables functional
      dependency statistics, and <literal>mcv</literal> which enables
      most-common values lists.
      If this clause is omitted, all supported statistics kinds are
      included in the statistics object. Univariate expression statistics are
      built automatically if the statistics definition includes any complex
      expressions rather than just simple column references.
      For more information, see <xref linkend="planner-stats-extended"/>
      and <xref linkend="multivariate-statistics-examples"/>.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">column_name</replaceable></term>
    <listitem>
     <para>
      The name of a table column to be covered by the computed statistics.
      This is only allowed when building multivariate statistics.  At least
      two column names or expressions must be specified, and their order is
      not significant.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">expression</replaceable></term>
    <listitem>
     <para>
      An expression to be covered by the computed statistics.  This may be
      used to build univariate statistics on a single expression, or as part
      of a list of multiple column names and/or expressions to build
      multivariate statistics.  In the latter case, separate univariate
      statistics are built automatically for each expression in the list.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">table_name</replaceable></term>
    <listitem>
     <para>
      The name (optionally schema-qualified) of the table containing the
      column(s) the statistics are computed on;  see <xref
      linkend="sql-analyze"/> for an explanation of the handling of
      inheritance and partitions.
     </para>
    </listitem>
   </varlistentry>

  </variablelist>
 </refsect1>

 <refsect1>
  <title>Notes</title>

  <para>
   You must be the owner of a table to create a statistics object
   reading it.  Once created, however, the ownership of the statistics
   object is independent of the underlying table(s).
  </para>

  <para>
   Expression statistics are per-expression and are similar to creating an
   index on the expression, except that they avoid the overhead of index
   maintenance. Expression statistics are built automatically for each
   expression in the statistics object definition.
  </para>

  <para>
   Extended statistics are not currently used by the planner for selectivity
   estimations made for table joins.  This limitation will likely be removed
   in a future version of <productname>PostgreSQL</productname>.
  </para>
 </refsect1>

 <refsect1 id="sql-createstatistics-examples">
  <title>Examples</title>

  <para>
   Create table <structname>t1</structname>
Title: CREATE STATISTICS Parameters and Notes
Summary
This section describes the parameters and notes for the CREATE STATISTICS command. The parameters include: IF NOT EXISTS (prevents errors if the statistics object already exists), statistics_name (the name of the statistics object), statistics_kind (the type of multivariate statistics to compute, such as ndistinct, dependencies, and mcv), column_name (a table column to include in the statistics), expression (an expression to include), and table_name (the name of the table). Notes highlight that the user must own the table to create statistics, expression statistics avoid index maintenance overhead, and extended statistics are not currently used for table join selectivity estimations.