1- <!-- $PostgreSQL: pgsql/doc/src/sgml/perform.sgml,v 1.67 2007/11 /2815:42 :31petere Exp $ -->
1+ <!-- $PostgreSQL: pgsql/doc/src/sgml/perform.sgml,v 1.68 2007/12 /2821:03 :31tgl Exp $ -->
22
33 <chapter id="performance-tips">
44 <title>Performance Tips</title>
88 </indexterm>
99
1010 <para>
11- Query performance can be affected by many things. Some of these can
11+ Query performance can be affected by many things. Some of these can
1212 be manipulated by the user, while others are fundamental to the underlying
1313 design of the system. This chapter provides some hints about understanding
1414 and tuning <productname>PostgreSQL</productname> performance.
@@ -138,7 +138,7 @@ EXPLAIN SELECT * FROM tenk1;
138138 Rows output is a little tricky because it is <emphasis>not</emphasis> the
139139 number of rows processed or scanned by the plan node. It is usually less,
140140 reflecting the estimated selectivity of any <literal>WHERE</>-clause
141- conditions that are being
141+ conditions that are being
142142 applied at the node. Ideally the top-level rows estimate will
143143 approximate the number of rows actually returned, updated, or deleted
144144 by the query.
@@ -469,8 +469,8 @@ EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t
469469 One component of the statistics is the total number of entries in
470470 each table and index, as well as the number of disk blocks occupied
471471 by each table and index. This information is kept in the table
472- <link linkend="catalog-pg-class"><structname>pg_class</structname></link>, in
473- the columns <structfield>reltuples</structfield> and
472+ <link linkend="catalog-pg-class"><structname>pg_class</structname></link>,
473+ in the columns <structfield>reltuples</structfield> and
474474 <structfield>relpages</structfield>. We can look at it with
475475 queries similar to this one:
476476
@@ -493,7 +493,7 @@ SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE 't
493493 </para>
494494
495495 <para>
496- For efficiency reasons, <structfield>reltuples</structfield>
496+ For efficiency reasons, <structfield>reltuples</structfield>
497497 and <structfield>relpages</structfield> are not updated on-the-fly,
498498 and so they usually contain somewhat out-of-date values.
499499 They are updated by <command>VACUUM</>, <command>ANALYZE</>, and a
@@ -517,7 +517,8 @@ SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE 't
517517 <firstterm>selectivity</> of <literal>WHERE</> clauses, that is,
518518 the fraction of rows that match each condition in the
519519 <literal>WHERE</> clause. The information used for this task is
520- stored in the <link linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link>
520+ stored in the
521+ <link linkend="catalog-pg-statistic"><structname>pg_statistic</structname></link>
521522 system catalog. Entries in <structname>pg_statistic</structname>
522523 are updated by the <command>ANALYZE</> and <command>VACUUM
523524 ANALYZE</> commands, and are always approximate even when freshly
@@ -530,7 +531,8 @@ SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE 't
530531
531532 <para>
532533 Rather than look at <structname>pg_statistic</structname> directly,
533- it's better to look at its view <structname>pg_stats</structname>
534+ it's better to look at its view
535+ <link linkend="view-pg-stats"><structname>pg_stats</structname></link>
534536 when examining the statistics manually. <structname>pg_stats</structname>
535537 is designed to be more easily readable. Furthermore,
536538 <structname>pg_stats</structname> is readable by all, whereas
@@ -553,13 +555,8 @@ SELECT attname, n_distinct, most_common_vals FROM pg_stats WHERE tablename = 'ro
553555 </para>
554556
555557 <para>
556- <structname>pg_stats</structname> is described in detail in
557- <xref linkend="view-pg-stats">.
558- </para>
559-
560- <para>
561- The amount of information stored in <structname>pg_statistic</structname>,
562- in particular the maximum number of entries in the
558+ The amount of information stored in <structname>pg_statistic</structname>
559+ by <command>ANALYZE</>, in particular the maximum number of entries in the
563560 <structfield>most_common_vals</> and <structfield>histogram_bounds</>
564561 arrays for each column, can be set on a
565562 column-by-column basis using the <command>ALTER TABLE SET STATISTICS</>
@@ -570,7 +567,12 @@ SELECT attname, n_distinct, most_common_vals FROM pg_stats WHERE tablename = 'ro
570567 columns with irregular data distributions, at the price of consuming
571568 more space in <structname>pg_statistic</structname> and slightly more
572569 time to compute the estimates. Conversely, a lower limit might be
573- appropriate for columns with simple data distributions.
570+ sufficient for columns with simple data distributions.
571+ </para>
572+
573+ <para>
574+ Further details about the planner's use of statistics can be found in
575+ <xref linkend="planner-stats-details">.
574576 </para>
575577
576578 </sect1>
@@ -913,7 +915,7 @@ SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
913915 are designed not to write WAL at all if <varname>archive_mode</varname>
914916 is off. (They can guarantee crash safety more cheaply by doing an
915917 <function>fsync</> at the end than by writing WAL.)
916- This applies to the following commands:
918+ This applies to the following commands:
917919 <itemizedlist>
918920 <listitem>
919921 <para>