Home • Explore • Blog • CI
☰



postgresql
5th chunk of `doc/src/sgml/ref/create_index.sgml`
39207264711cbdeca84c1567e510345c1595fea636fa42ab0000000100000fac
     </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>ASC</literal></term>
      <listitem>
       <para>
        Specifies ascending sort order (which is the default).
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>DESC</literal></term>
      <listitem>
       <para>
        Specifies descending sort order.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>NULLS FIRST</literal></term>
      <listitem>
       <para>
        Specifies that nulls sort before non-nulls.  This is the default
        when <literal>DESC</literal> is specified.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>NULLS LAST</literal></term>
      <listitem>
       <para>
        Specifies that nulls sort after non-nulls.  This is the default
        when <literal>DESC</literal> is not specified.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><literal>NULLS DISTINCT</literal></term>
      <term><literal>NULLS NOT DISTINCT</literal></term>
      <listitem>
       <para>
        Specifies whether for a unique index, null values should be considered
        distinct (not equal).  The default is that they are distinct, so that
        a unique index could contain multiple null values in a column.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">storage_parameter</replaceable></term>
      <listitem>
       <para>
        The name of an index-method-specific storage parameter.  See
        <xref linkend="sql-createindex-storage-parameters"/> below
        for details.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">tablespace_name</replaceable></term>
      <listitem>
       <para>
        The tablespace in which to create the index.  If not specified,
        <xref linkend="guc-default-tablespace"/> is consulted, or
        <xref linkend="guc-temp-tablespaces"/> for indexes on temporary
        tables.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">predicate</replaceable></term>
      <listitem>
       <para>
        The constraint expression for a partial index.
       </para>
      </listitem>
     </varlistentry>

    </variablelist>

  <refsect2 id="sql-createindex-storage-parameters" xreflabel="Index Storage Parameters">
   <title>Index Storage Parameters</title>

   <para>
    The optional <literal>WITH</literal> clause specifies <firstterm>storage
    parameters</firstterm> for the index.  Each index method has its own set
    of allowed storage parameters.
   </para>

   <para>
    The B-tree, hash, GiST and SP-GiST index methods all accept this
    parameter:
   </para>

   <variablelist>
   <varlistentry id="index-reloption-fillfactor" xreflabel="fillfactor">
    <term><literal>fillfactor</literal> (<type>integer</type>)
     <indexterm>
      <primary><varname>fillfactor</varname> storage parameter</primary>
     </indexterm>
    </term>
    <listitem>
     <para>
      Controls how full
      the index method will try to pack index pages.  For B-trees, leaf pages
      are filled to this percentage during initial index builds, and also
      when extending the index at the right (adding new largest key values).
      If pages
      subsequently become completely full, they will be split, leading to
      fragmentation of the on-disk index structure.  B-trees use a default
      fillfactor of 90, but any integer value from 10 to 100 can be selected.
     </para>
     <para>
      B-tree indexes on tables where many inserts and/or updates are
      anticipated can benefit from lower fillfactor settings at
      <command>CREATE INDEX</command> time (following bulk loading into the
      table).  Values in the range of 50 - 90 can usefully <quote>smooth
Title: CREATE INDEX Parameters: Null Handling, Storage, and Tablespaces
Summary
This section details parameters for the CREATE INDEX command, including handling of NULL values (DISTINCT/NOT DISTINCT), storage parameters specific to the index method, the tablespace in which to create the index, and the constraint expression for partial indexes. It also describes the 'fillfactor' storage parameter for B-tree, hash, GiST, and SP-GiST indexes, which controls how full the index pages are packed.