runtime.sgml

PostgreSQL7.4.6 for Linux

        <productname>PostgreSQL</productname> must wait for the
        operating system to flush the write-ahead log to disk.  When
        <varname>fsync</varname> is disabled, the operating system is
        allowed to do its best in buffering, ordering, and delaying
        writes. This can result in significantly improved performance.
        However, if the system crashes, the results of the last few
        committed transactions may be lost in part or whole. In the
        worst case, unrecoverable data corruption may occur.
       </para>

       <para>
        Due to the risks involved, there is no universally correct
        setting for <varname>fsync</varname>. Some administrators
        always disable <varname>fsync</varname>, while others only
        turn it off for bulk loads, where there is a clear restart
        point if something goes wrong, whereas some administrators
        always leave <varname>fsync</varname> enabled. The default is
        to enable <varname>fsync</varname>, for maximum reliability.
        If you trust your operating system, your hardware, and your
        utility company (or your battery backup), you can consider
        disabling <varname>fsync</varname>.
       </para>

       <para>
        This option can only be set at server start or in the
        <filename>postgresql.conf</filename> file.
       </para>
      </listitem>
     </varlistentry>
     
     <varlistentry>
      <term><varname>wal_sync_method</varname> (<type>string</type>)</term>
      <listitem>
       <para>
        Method used for forcing WAL updates out to disk.  Possible
        values are
        <literal>fsync</> (call <function>fsync()</> at each commit),
        <literal>fdatasync</> (call <function>fdatasync()</> at each commit),
        <literal>open_sync</> (write WAL files with <function>open()</> option <symbol>O_SYNC</>), and
        <literal>open_datasync</> (write WAL files with <function>open()</> option <symbol>O_DSYNC</>).
        Not all of these choices are available on all platforms.
        This option can only be set at server start or in the
        <filename>postgresql.conf</filename> file.
       </para>
      </listitem>
     </varlistentry>
     
     <varlistentry>
      <term><varname>wal_buffers</varname> (<type>integer</type>)</term>
      <listitem>
       <para>
        Number of disk-page buffers in shared memory for WAL
        logging. The default is 8. This option can only be set at
        server start.
       </para>
      </listitem>
     </varlistentry>

     </variablelist>
     </sect3>
     <sect3 id="runtime-config-wal-checkpoints">
     <title>Checkpoints</title>

    <variablelist>
     <varlistentry>
      <term><varname>checkpoint_segments</varname> (<type>integer</type>)</term>
      <listitem>
       <para>
        Maximum distance between automatic WAL checkpoints, in log
        file segments (each segment is normally 16 megabytes). The
        default is three.  This option can only be set at server start
        or in the <filename>postgresql.conf</filename> file.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><varname>checkpoint_timeout</varname> (<type>integer</type>)</term>
      <listitem>
       <para>
        Maximum time between automatic WAL checkpoints, in
        seconds. The default is 300 seconds.  This option can only be
        set at server start or in the <filename>postgresql.conf</>
        file.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><varname>checkpoint_warning</varname> (<type>integer</type>)</term>
      <listitem>
       <para>
        Write a message to the server logs if checkpoints caused by
        the filling of checkpoint segment files happens more
        frequently than this number of seconds.  The default is 30
        seconds.  Zero turns off the warning.
       </para>
      </listitem>
     </varlistentry>
                
                
     <varlistentry>
      <term><varname>commit_delay</varname> (<type>integer</type>)</term>
      <listitem>
       <para>
        Time delay between writing a commit record to the WAL buffer
        and flushing the buffer out to disk, in microseconds. A
        nonzero delay allows multiple transactions to be committed
        with only one <function>fsync()</function> system call, if
        system load is high enough additional transactions may become
        ready to commit within the given interval. But the delay is
        just wasted if no other transactions become ready to
        commit. Therefore, the delay is only performed if at least
        <varname>commit_siblings</varname> other transactions are
        active at the instant that a server process has written its
        commit record. The default is zero (no delay).
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><varname>commit_siblings</varname> (<type>integer</type>)</term>
      <listitem>
       <para>
        Minimum number of concurrent open transactions to require
        before performing the <varname>commit_delay</> delay. A larger
        value makes it more probable that at least one other
        transaction will become ready to commit during the delay
        interval. The default is five.
       </para>
      </listitem>
     </varlistentry>
     
     </variablelist>
    </sect3>
   </sect2>

   <sect2 id="runtime-config-query">
    <title>Query Planning</title>

    <sect3 id="runtime-config-query-enable">
     <title>Planner Method Configuration</title>

     <note>
      <para>
       These configuration parameters provide a crude method for
       influencing the query plans chosen by the query optimizer. If
       the default plan chosen by the optimizer for a particular query
       is not optimal, a temporary solution may be found by using one
       of these configuration parameters to force the optimizer to
       choose a better plan. Other ways to improve the quality of the
       plans chosen by the optimizer include configuring the <xref
       linkend="runtime-config-query-constants"
       endterm="runtime-config-query-constants-title">, running
       <command>ANALYZE</command> more frequently, and increasing the
       amount of statistics collected for a particular column using
       <command>ALTER TABLE SET STATISTICS</command>.
      </para>
     </note>

     <variablelist>
     <varlistentry>
      <term><varname>enable_hashagg</varname> (<type>boolean</type>)</term>
      <listitem>
       <para>
        Enables or disables the query planner's use of hashed
        aggregation plan types. The default is on. This is used for
        debugging the query planner.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><varname>enable_hashjoin</varname> (<type>boolean</type>)</term>
      <listitem>
       <para>
        Enables or disables the query planner's use of hash-join plan
        types. The default is on. This is used for debugging the query
        planner.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <indexterm>
       <primary>index scan</primary>
      </indexterm>

      <term><varname>enable_indexscan</varname> (<type>boolean</type>)</term>
      <listitem>
       <para>
        Enables or disables the query planner's use of index-scan plan
        types. The default is on. This is used for debugging the query
        planner.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><varname>enable_mergejoin</varname> (<type>boolean</type>)</term>
      <listitem>
       <para>
        Enables or disables the query planner's use of merge-join plan
        types. The default is on. This is used for debugging the query
        planner.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><varname>enable_nestloop</varname> (<type>boolean</type>)</term>
      <listitem>
       <para>
        Enables or disables the query planner's use of nested-loop join
        plans. It's not possible to suppress nested-loop joins entirely,
        but turning this variable off discourages the planner from using
        one if there are other methods available. The default is
        on. This is used for debugging the query planner.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <indexterm>
       <primary>sequential scan</primary>
      </indexterm>

      <term><varname>enable_seqscan</varname> (<type>boolean</type>)</term>
      <listitem>
       <para>
        Enables or disables the query planner's use of sequential scan
        plan types. It's not possible to suppress sequential scans
        entirely, but turning this variable off discourages the planner
        from using one if there are other methods available. The
        default is on. This is used for debugging the query planner.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><varname>enable_sort</varname> (<type>boolean</type>)</term>
      <listitem>
       <para>
        Enables or disables the query planner's use of explicit sort
        steps. It's not possible to suppress explicit sorts entirely,
        but turning this variable off discourages the planner from
        using one if there are other methods available. The default
        is on. This is used for debugging the query planner.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><varname>enable_tidscan</varname> (<type>boolean</type>)</term>
      <listitem>
       <para>
        Enables or disables the query planner's use of <acronym>TID</>
        scan plan types. The default is on. This is used for debugging
        the query planner.
       </para>
      </listitem>
     </varlistentry>
     
     </variablelist>
     </sect3>
     <sect3 id="runtime-config-query-constants">
     <title id="runtime-config-query-constants-title">
      Planner Cost Constants
     </title>

   <note>
    <para>
     Unfortunately, there is no well-defined method for determining
     ideal values for the family of <quote>cost</quote> variables that
     appear below. You are encouraged to experiment and share
     your findings.
    </para>
   </note>

     <variablelist>
     
     <varlistentry>
      <term><varname>effective_cache_size</varname> (<type>floating point</type>)</term>
      <listitem>
       <para>
        Sets the planner's assumption about the effective size of the
        disk cache (that is, the portion of the kernel's disk cache
        that will be used for <productname>PostgreSQL</productname>
        data files). This is measured in disk pages, which are
        normally 8192 bytes each. The default is 1000.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><varname>random_page_cost</varname> (<type>floating point</type>)</term>
      <listitem>
       <para>
        Sets the query planner's estimate of the cost of a
        nonsequentially fetched disk page. This is measured as a
        multiple of the cost of a sequential page fetch. A higher
        value makes it more likely a sequential scan will be used, a
        lower value makes it more likely an index scan will be
        used. The default is four.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><varname>cpu_tuple_cost</varname> (<type>floating point</type>)</term>
      <listitem>
       <para>
        Sets the query planner's estimate of the cost of processing
        each row during a query. This is measured as a fraction of
        the cost of a sequential page fetch. The default is 0.01.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><varname>cpu_index_tuple_cost</varname> (<type>floating point</type>)</term>
      <listitem>
       <para>
        Sets the query planner's estimate of the cost of processing
        each index row during an index scan. This is measured as a
        fraction of the cost of a sequential page fetch. The default
        is 0.001.
       </para>
      </listitem>
     </varlistentry>
    
     <varlistentry>
      <term><varname>cpu_operator_cost</varname> (<type>floating point</type>)</term>
      <listitem>
       <para>
        Sets the planner's estimate of the cost of processing each
        operator in a <literal>WHERE</> clause. This is measured as a fraction of
        the cost of a sequential page fetch. The default is 0.0025.
       </para>