perform.sgml

PostgreSQL7.4.6 for Linux

    also <xref linkend="explicit-joins">.)

<programlisting>
SET enable_nestloop = off;
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 &lt; 50 AND t1.unique2 = t2.unique2;

                               QUERY PLAN
--------------------------------------------------------------------------
 Hash Join  (cost=179.45..563.06 rows=49 width=296)
   Hash Cond: ("outer".unique2 = "inner".unique2)
   -&gt;  Seq Scan on tenk2 t2  (cost=0.00..333.00 rows=10000 width=148)
   -&gt;  Hash  (cost=179.33..179.33 rows=49 width=148)
         -&gt;  Index Scan using tenk1_unique1 on tenk1 t1
                                    (cost=0.00..179.33 rows=49 width=148)
               Index Cond: (unique1 &lt; 50)
</programlisting>

    This plan proposes to extract the 50 interesting rows of <classname>tenk1</classname>
    using ye same olde index scan, stash them into an in-memory hash table,
    and then do a sequential scan of <classname>tenk2</classname>, probing into the hash table
    for possible matches of <literal>t1.unique2 = t2.unique2</literal> at each <classname>tenk2</classname> row.
    The cost to read <classname>tenk1</classname> and set up the hash table is entirely start-up
    cost for the hash join, since we won't get any rows out until we can
    start reading <classname>tenk2</classname>.  The total time estimate for the join also
    includes a hefty charge for the CPU time to probe the hash table
    10000 times.  Note, however, that we are <emphasis>not</emphasis> charging 10000 times 179.33;
    the hash table setup is only done once in this plan type.
   </para>

   <para>
    It is possible to check on the accuracy of the planner's estimated costs
    by using <command>EXPLAIN ANALYZE</>.  This command actually executes the query,
    and then displays the true run time accumulated within each plan node
    along with the same estimated costs that a plain <command>EXPLAIN</command> shows.
    For example, we might get a result like this:

<screen>
EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 &lt; 50 AND t1.unique2 = t2.unique2;

                                   QUERY PLAN
-------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..327.02 rows=49 width=296)
                                 (actual time=1.181..29.822 rows=50 loops=1)
   -&gt;  Index Scan using tenk1_unique1 on tenk1 t1
                  (cost=0.00..179.33 rows=49 width=148)
                                 (actual time=0.630..8.917 rows=50 loops=1)
         Index Cond: (unique1 &lt; 50)
   -&gt;  Index Scan using tenk2_unique2 on tenk2 t2
                  (cost=0.00..3.01 rows=1 width=148)
                                 (actual time=0.295..0.324 rows=1 loops=50)
         Index Cond: ("outer".unique2 = t2.unique2)
 Total runtime: 31.604 ms
</screen>

    Note that the <quote>actual time</quote> values are in milliseconds of
    real time, whereas the <quote>cost</quote> estimates are expressed in
    arbitrary units of disk fetches; so they are unlikely to match up.
    The thing to pay attention to is the ratios.
   </para>

   <para>
    In some query plans, it is possible for a subplan node to be executed more
    than once.  For example, the inner index scan is executed once per outer
    row in the above nested-loop plan.  In such cases, the
    <quote>loops</quote> value reports the
    total number of executions of the node, and the actual time and rows
    values shown are averages per-execution.  This is done to make the numbers
    comparable with the way that the cost estimates are shown.  Multiply by
    the <quote>loops</quote> value to get the total time actually spent in
    the node.
   </para>

   <para>
    The <literal>Total runtime</literal> shown by <command>EXPLAIN ANALYZE</command> includes
    executor start-up and shut-down time, as well as time spent processing
    the result rows.  It does not include parsing, rewriting, or planning
    time.  For a <command>SELECT</> query, the total run time will normally be just a
    little larger than the total time reported for the top-level plan node.
    For <command>INSERT</>, <command>UPDATE</>, and <command>DELETE</> commands, the total run time may be
    considerably larger, because it includes the time spent processing the
    result rows.  In these commands, the time for the top plan node
    essentially is the time spent computing the new rows and/or locating
    the old ones, but it doesn't include the time spent making the changes.
   </para>

   <para>
    It is worth noting that <command>EXPLAIN</> results should not be extrapolated
    to situations other than the one you are actually testing; for example,
    results on a toy-sized table can't be assumed to apply to large tables.
    The planner's cost estimates are not linear and so it may well choose
    a different plan for a larger or smaller table.  An extreme example
    is that on a table that only occupies one disk page, you'll nearly
    always get a sequential scan plan whether indexes are available or not.
    The planner realizes that it's going to take one disk page read to
    process the table in any case, so there's no value in expending additional
    page reads to look at an index.
   </para>
  </sect1>

 <sect1 id="planner-stats">
  <title>Statistics Used by the Planner</title>

  <indexterm zone="planner-stats">
   <primary>statistics</primary>
   <secondary>of the planner</secondary>
  </indexterm>

  <para>
   As we saw in the previous section, the query planner needs to estimate
   the number of rows retrieved by a query in order to make good choices
   of query plans.  This section provides a quick look at the statistics
   that the system uses for these estimates.
  </para>

  <para>
   One component of the statistics is the total number of entries in each
   table and index, as well as the number of disk blocks occupied by each
   table and index.  This information is kept in the table
   <structname>pg_class</structname> in the columns <structfield>reltuples</structfield>
   and <structfield>relpages</structfield>.  We can look at it
   with queries similar to this one:

<screen>
SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE 'tenk1%';

    relname    | relkind | reltuples | relpages
---------------+---------+-----------+----------
 tenk1         | r       |     10000 |      233
 tenk1_hundred | i       |     10000 |       30
 tenk1_unique1 | i       |     10000 |       30
 tenk1_unique2 | i       |     10000 |       30
(4 rows)
</screen>

   Here we can see that <structname>tenk1</structname> contains 10000
   rows, as do its indexes, but the indexes are (unsurprisingly) much
   smaller than the table.
  </para>

  <para>
   For efficiency reasons, <structfield>reltuples</structfield> 
   and <structfield>relpages</structfield> are not updated on-the-fly,
   and so they usually contain only approximate values (which is good
   enough for the planner's purposes).  They are initialized with dummy
   values (presently 1000 and 10 respectively) when a table is created.
   They are updated by certain commands, presently <command>VACUUM</>,
   <command>ANALYZE</>, and <command>CREATE INDEX</>.  A stand-alone
   <command>ANALYZE</>, that is one not part of <command>VACUUM</>,
   generates an approximate <structfield>reltuples</structfield> value
   since it does not read every row of the table.
  </para>

  <indexterm>
   <primary>pg_statistic</primary>
  </indexterm>

  <para>
   Most queries retrieve only a fraction of the rows in a table, due
   to having <literal>WHERE</> clauses that restrict the rows to be examined.
   The planner thus needs to make an estimate of the
   <firstterm>selectivity</> of <literal>WHERE</> clauses, that is, the fraction of
   rows that match each condition in the <literal>WHERE</> clause.  The information
   used for this task is stored in the <structname>pg_statistic</structname>
   system catalog.  Entries in <structname>pg_statistic</structname> are
   updated by <command>ANALYZE</> and <command>VACUUM ANALYZE</> commands
   and are always approximate even when freshly updated.
  </para>

  <indexterm>
   <primary>pg_stats</primary>
  </indexterm>

  <para>
   Rather than look at <structname>pg_statistic</structname> directly,
   it's better to look at its view <structname>pg_stats</structname>
   when examining the statistics manually.  <structname>pg_stats</structname>
   is designed to be more easily readable.  Furthermore,
   <structname>pg_stats</structname> is readable by all, whereas
   <structname>pg_statistic</structname> is only readable by a superuser.
   (This prevents unprivileged users from learning something about
   the contents of other people's tables from the statistics.  The
   <structname>pg_stats</structname> view is restricted to show only
   rows about tables that the current user can read.)
   For example, we might do:

<screen>
SELECT attname, n_distinct, most_common_vals FROM pg_stats WHERE tablename = 'road';

 attname | n_distinct |                                                                                                                                                                                  most_common_vals                                                                                                                                                                                   
---------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 name    |  -0.467008 | {"I- 580                        Ramp","I- 880                        Ramp","Sp Railroad                       ","I- 580                            ","I- 680                        Ramp","I- 80                         Ramp","14th                          St  ","5th                           St  ","Mission                       Blvd","I- 880                            "}
 thepath |         20 | {"[(-122.089,37.71),(-122.0886,37.711)]"}
(2 rows)
</screen>
  </para>

  <para>
   <structname>pg_stats</structname> is described in detail in
   <xref linkend="view-pg-stats">.
  </para>

  <para>
   The amount of information stored in <structname>pg_statistic</structname>,
   in particular the maximum number of entries in the
   <structfield>most_common_vals</> and <structfield>histogram_bounds</>
   arrays for each column, can be set on a
   column-by-column basis using the <command>ALTER TABLE SET STATISTICS</>
   command, or globally by setting the
   <varname>default_statistics_target</varname> runtime parameter.
   The default limit is presently 10 entries.  Raising the limit
   may allow more accurate planner estimates to be made, particularly for
   columns with irregular data distributions, at the price of consuming
   more space in <structname>pg_statistic</structname> and slightly more
   time to compute the estimates.  Conversely, a lower limit may be
   appropriate for columns with simple data distributions.
  </para>

 </sect1>

 <sect1 id="explicit-joins">
  <title>Controlling the Planner with Explicit <literal>JOIN</> Clauses</title>

  <indexterm zone="explicit-joins">
   <primary>join</primary>
   <secondary>controlling the order</secondary>
  </indexterm>

  <para>
   It is possible
   to control the query planner to some extent by using the explicit <literal>JOIN</>
   syntax.  To see why this matters, we first need some background.
  </para>

  <para>
   In a simple join query, such as
<programlisting>
SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
</programlisting>
   the planner is free to join the given tables in any order.  For
   example, it could generate a query plan that joins A to B, using
   the <literal>WHERE</> condition <literal>a.id = b.id</>, and then
   joins C to this joined table, using the other <literal>WHERE</>
   condition.  Or it could join B to C and then join A to that result.
   Or it could join A to C and then join them with B, but that