Home Explore Blog CI



postgresql

8th chunk of `doc/src/sgml/ref/alter_table.sgml`
737463d688ab362f65a79d6f68c2437055f7510a31bb0cf30000000100000fa0
 linkend="guc-default-statistics-target"/>).
      (Setting to a value of -1 is an obsolete way spelling to get the same
      outcome.)
      For more information on the use of statistics by the
      <productname>PostgreSQL</productname> query planner, refer to
      <xref linkend="planner-stats"/>.
     </para>
     <para>
      <literal>SET STATISTICS</literal> acquires a
      <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-altertable-desc-set-attribute-option">
    <term><literal>SET ( <replaceable class="parameter">attribute_option</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] )</literal></term>
    <term><literal>RESET ( <replaceable class="parameter">attribute_option</replaceable> [, ... ] )</literal></term>
    <listitem>
     <para>
      This form sets or resets per-attribute options.  Currently, the only
      defined per-attribute options are <literal>n_distinct</literal> and
      <literal>n_distinct_inherited</literal>, which override the
      number-of-distinct-values estimates made by subsequent
      <link linkend="sql-analyze"><command>ANALYZE</command></link>
      operations.  <literal>n_distinct</literal> affects the statistics for the table
      itself, while <literal>n_distinct_inherited</literal> affects the statistics
      gathered for the table plus its inheritance children.  When set to a
      positive value, <command>ANALYZE</command> will assume that the column contains
      exactly the specified number of distinct nonnull values.  When set to a
      negative value, which must be greater
      than or equal to -1, <command>ANALYZE</command> will assume that the number of
      distinct nonnull values in the column is linear in the size of the
      table; the exact count is to be computed by multiplying the estimated
      table size by the absolute value of the given number.  For example,
      a value of -1 implies that all values in the column are distinct, while
      a value of -0.5 implies that each value appears twice on the average.
      This can be useful when the size of the table changes over time, since
      the multiplication by the number of rows in the table is not performed
      until query planning time.  Specify a value of 0 to revert to estimating
      the number of distinct values normally.  For more information on the use
      of statistics by the <productname>PostgreSQL</productname> query
      planner, refer to <xref linkend="planner-stats"/>.
     </para>
     <para>
      Changing per-attribute options acquires a
      <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry id="sql-altertable-desc-set-storage">
    <term>
     <literal>SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN | DEFAULT }</literal>
     <indexterm>
      <primary>TOAST</primary>
      <secondary>per-column storage settings</secondary>
     </indexterm>
    </term>
    <listitem>
     <para>
      This form sets the storage mode for a column. This controls whether this
      column is held inline or in a secondary <acronym>TOAST</acronym> table,
      and whether the data
      should be compressed or not. <literal>PLAIN</literal> must be used
      for fixed-length values such as <type>integer</type> and is
      inline, uncompressed. <literal>MAIN</literal> is for inline,
      compressible data. <literal>EXTERNAL</literal> is for external,
      uncompressed data, and <literal>EXTENDED</literal> is for external,
      compressed data.
      Writing <literal>DEFAULT</literal> sets the storage mode to the default
      mode for the column's data type.  <literal>EXTENDED</literal> is the
      default for most data types that support non-<literal>PLAIN</literal>
      storage.
      Use of <literal>EXTERNAL</literal> will make substring operations on
      very large <type>text</type> and <type>bytea</type> values run faster,
  

Title: ALTER TABLE: Setting Attribute Options and Storage Modes
Summary
This section discusses the ALTER TABLE options for setting and resetting per-attribute options, specifically n_distinct and n_distinct_inherited, to influence the distinct value estimates used by ANALYZE. It also covers setting the storage mode for a column, controlling inline vs. external storage and compression using PLAIN, MAIN, EXTERNAL, EXTENDED, and DEFAULT options, impacting performance for large text and bytea values.