Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commiteed6c9e

Browse files
committed
Add a GUC parameter seq_page_cost, and use that everywhere we formerly
assumed that a sequential page fetch has cost 1.0. This patch doesn'tin itself change the system's behavior at all, but it opens the door topeople adopting other units of measurement for EXPLAIN costs. Also, ifwe ever decide it's worth inventing per-tablespace access cost settings,this change provides a workable intellectual framework for that.
1 parenta837851 commiteed6c9e

File tree

8 files changed

+198
-142
lines changed

8 files changed

+198
-142
lines changed

‎doc/src/sgml/config.sgml‎

Lines changed: 77 additions & 37 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.59 2006/05/21 20:10:42 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/config.sgml,v 1.60 2006/06/05 02:49:58 tgl Exp $ -->
22

33
<chapter Id="runtime-config">
44
<title>Server Configuration</title>
@@ -1739,40 +1739,39 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows
17391739
Planner Cost Constants
17401740
</title>
17411741

1742+
<para>
1743+
The <firstterm>cost</> variables described in this section are measured
1744+
on an arbitrary scale. Only their relative values matter, hence
1745+
scaling them all up or down by the same factor will result in no change
1746+
in the planner's choices. Traditionally, these variables have been
1747+
referenced to sequential page fetches as the unit of cost; that is,
1748+
<varname>seq_page_cost</> is conventionally set to <literal>1.0</>
1749+
and the other cost variables are set with reference to that. But
1750+
you can use a different scale if you prefer, such as actual execution
1751+
times in milliseconds on a particular machine.
1752+
</para>
1753+
17421754
<note>
17431755
<para>
1744-
Unfortunately, there is no well-defined method for determining
1745-
ideal values for the family of <quote>cost</quote> variables that
1746-
appear below. You are encouraged to experiment and share
1747-
your findings.
1756+
Unfortunately, there is no well-defined method for determining ideal
1757+
values for the cost variables. They are best treated as averages over
1758+
the entire mix of queries that a particular installation will get. This
1759+
means that changing them on the basis of just a few experiments is very
1760+
risky.
17481761
</para>
17491762
</note>
17501763

17511764
<variablelist>
1752-
1753-
<varlistentry id="guc-effective-cache-size" xreflabel="effective_cache_size">
1754-
<term><varname>effective_cache_size</varname> (<type>floating point</type>)</term>
1765+
1766+
<varlistentry id="guc-seq-page-cost" xreflabel="seq_page_cost">
1767+
<term><varname>seq_page_cost</varname> (<type>floating point</type>)</term>
17551768
<indexterm>
1756-
<primary><varname>effective_cache_size</> configuration parameter</primary>
1769+
<primary><varname>seq_page_cost</> configuration parameter</primary>
17571770
</indexterm>
17581771
<listitem>
17591772
<para>
1760-
Sets the planner's assumption about the effective size of the
1761-
disk cache that is available to a single index scan. This is
1762-
factored into estimates of the cost of using an index; a
1763-
higher value makes it more likely index scans will be used, a
1764-
lower value makes it more likely sequential scans will be
1765-
used. When setting this parameter you should consider both
1766-
<productname>PostgreSQL</productname>'s shared buffers and the
1767-
portion of the kernel's disk cache that will be used for
1768-
<productname>PostgreSQL</productname> data files. Also, take
1769-
into account the expected number of concurrent queries using
1770-
different indexes, since they will have to share the available
1771-
space. This parameter has no effect on the size of shared
1772-
memory allocated by <productname>PostgreSQL</productname>, nor
1773-
does it reserve kernel disk cache; it is used only for
1774-
estimation purposes. The value is measured in disk pages,
1775-
which are normally 8192 bytes each. The default is 1000.
1773+
Sets the planner's estimate of the cost of a disk page fetch
1774+
that is part of a series of sequential fetches. The default is 1.0.
17761775
</para>
17771776
</listitem>
17781777
</varlistentry>
@@ -1785,12 +1784,27 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows
17851784
<listitem>
17861785
<para>
17871786
Sets the planner's estimate of the cost of a
1788-
nonsequentially fetched disk page. This is measured as a
1789-
multiple of the cost of a sequential page fetch. A higher
1790-
value makes it more likely a sequential scan will be used, a
1791-
lower value makes it more likely an index scan will be
1792-
used. The default is four.
1787+
non-sequentially-fetched disk page. The default is 4.0.
1788+
Reducing this value relative to <varname>seq_page_cost</>
1789+
will cause the system to prefer index scans; raising it will
1790+
make index scans look relatively more expensive. You can raise
1791+
or lower both values together to change the importance of disk I/O
1792+
costs relative to CPU costs, which are described by the following
1793+
parameters.
17931794
</para>
1795+
1796+
<tip>
1797+
<para>
1798+
Although the system will let you set <varname>random_page_cost</> to
1799+
less than <varname>seq_page_cost</>, it is not physically sensible
1800+
to do so. However, setting them equal makes sense if the database
1801+
is entirely cached in RAM, since in that case there is no penalty
1802+
for touching pages out of sequence. Also, in a heavily-cached
1803+
database you should lower both values relative to the CPU parameters,
1804+
since the cost of fetching a page already in RAM is much smaller
1805+
than it would normally be.
1806+
</para>
1807+
</tip>
17941808
</listitem>
17951809
</varlistentry>
17961810

@@ -1802,8 +1816,8 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows
18021816
<listitem>
18031817
<para>
18041818
Sets the planner's estimate of the cost of processing
1805-
each row during a query. This is measured as a fraction of
1806-
the cost of a sequential page fetch.The default is 0.01.
1819+
each row during a query.
1820+
The default is 0.01.
18071821
</para>
18081822
</listitem>
18091823
</varlistentry>
@@ -1816,9 +1830,8 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows
18161830
<listitem>
18171831
<para>
18181832
Sets the planner's estimate of the cost of processing
1819-
each index row during an index scan. This is measured as a
1820-
fraction of the cost of a sequential page fetch. The default
1821-
is 0.001.
1833+
each index entry during an index scan.
1834+
The default is 0.001.
18221835
</para>
18231836
</listitem>
18241837
</varlistentry>
@@ -1831,8 +1844,35 @@ archive_command = 'copy "%p" /mnt/server/archivedir/"%f"' # Windows
18311844
<listitem>
18321845
<para>
18331846
Sets the planner's estimate of the cost of processing each
1834-
operator in a <literal>WHERE</> clause. This is measured as a fraction of
1835-
the cost of a sequential page fetch. The default is 0.0025.
1847+
operator or function executed during a query.
1848+
The default is 0.0025.
1849+
</para>
1850+
</listitem>
1851+
</varlistentry>
1852+
1853+
<varlistentry id="guc-effective-cache-size" xreflabel="effective_cache_size">
1854+
<term><varname>effective_cache_size</varname> (<type>floating point</type>)</term>
1855+
<indexterm>
1856+
<primary><varname>effective_cache_size</> configuration parameter</primary>
1857+
</indexterm>
1858+
<listitem>
1859+
<para>
1860+
Sets the planner's assumption about the effective size of the
1861+
disk cache that is available to a single index scan. This is
1862+
factored into estimates of the cost of using an index; a
1863+
higher value makes it more likely index scans will be used, a
1864+
lower value makes it more likely sequential scans will be
1865+
used. When setting this parameter you should consider both
1866+
<productname>PostgreSQL</productname>'s shared buffers and the
1867+
portion of the kernel's disk cache that will be used for
1868+
<productname>PostgreSQL</productname> data files. Also, take
1869+
into account the expected number of concurrent queries using
1870+
different indexes, since they will have to share the available
1871+
space. This parameter has no effect on the size of shared
1872+
memory allocated by <productname>PostgreSQL</productname>, nor
1873+
does it reserve kernel disk cache; it is used only for
1874+
estimation purposes. The value is measured in disk pages,
1875+
which are normally 8192 bytes each. The default is 1000.
18361876
</para>
18371877
</listitem>
18381878
</varlistentry>

‎doc/src/sgml/indexam.sgml‎

Lines changed: 14 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/indexam.sgml,v 2.12 2006/05/24 11:01:39 teodor Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/indexam.sgml,v 2.13 2006/06/05 02:49:58 tgl Exp $ -->
22

33
<chapter id="indexam">
44
<title>Index Access Method Interface Definition</title>
@@ -771,14 +771,14 @@ amcostestimate (PlannerInfo *root,
771771
</para>
772772

773773
<para>
774-
The index access costs should be computedin theunits used by
774+
The index access costs should be computedusing theparameters used by
775775
<filename>src/backend/optimizer/path/costsize.c</filename>: a sequential
776-
disk block fetch has cost1.0, a nonsequential fetch has cost
777-
<varname>random_page_cost</>, and the cost of processing one index row
778-
should usually be taken as <varname>cpu_index_tuple_cost</>. In addition,
779-
an appropriate multiple of <varname>cpu_operator_cost</> should be charged
780-
for any comparison operators invoked during index processing (especially
781-
evaluation of the indexQuals themselves).
776+
disk block fetch has cost<varname>seq_page_cost</>, a nonsequential fetch
777+
has cost<varname>random_page_cost</>, and the cost of processing one index
778+
rowshould usually be taken as <varname>cpu_index_tuple_cost</>. In
779+
addition,an appropriate multiple of <varname>cpu_operator_cost</> should
780+
be chargedfor any comparison operators invoked during index processing
781+
(especiallyevaluation of the indexQuals themselves).
782782
</para>
783783

784784
<para>
@@ -788,10 +788,10 @@ amcostestimate (PlannerInfo *root,
788788
</para>
789789

790790
<para>
791-
The <quote>start-up cost</quote> is the part of the total scan cost that must be expended
792-
before we can begin to fetch the first row. For most indexes this can
793-
be taken as zero, but an index type with a high start-up cost might want
794-
to set it nonzero.
791+
The <quote>start-up cost</quote> is the part of the total scan cost that
792+
must be expendedbefore we can begin to fetch the first row. For most
793+
indexes this canbe taken as zero, but an index type with a high start-up
794+
cost might wantto set it nonzero.
795795
</para>
796796

797797
<para>
@@ -850,13 +850,13 @@ amcostestimate (PlannerInfo *root,
850850
<programlisting>
851851
/*
852852
* Our generic assumption is that the index pages will be read
853-
* sequentially, so theyhavecost1.0 each, not random_page_cost.
853+
* sequentially, so they costseq_page_cost each, not random_page_cost.
854854
* Also, we charge for evaluation of the indexquals at each index row.
855855
* All the costs are assumed to be paid incrementally during the scan.
856856
*/
857857
cost_qual_eval(&amp;index_qual_cost, indexQuals);
858858
*indexStartupCost = index_qual_cost.startup;
859-
*indexTotalCost = numIndexPages +
859+
*indexTotalCost =seq_page_cost *numIndexPages +
860860
(cpu_index_tuple_cost + index_qual_cost.per_tuple) * numIndexTuples;
861861
</programlisting>
862862
</para>

‎doc/src/sgml/perform.sgml‎

Lines changed: 16 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/perform.sgml,v 1.56 2006/03/10 19:10:48 momjian Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/perform.sgml,v 1.57 2006/06/05 02:49:58 tgl Exp $ -->
22

33
<chapter id="performance-tips">
44
<title>Performance Tips</title>
@@ -60,7 +60,7 @@
6060
<footnote>
6161
<para>
6262
Examples in this section are drawn from the regression test database
63-
after doing a <command>VACUUM ANALYZE</>, using 8.1 development sources.
63+
after doing a <command>VACUUM ANALYZE</>, using 8.2 development sources.
6464
You should be able to get similar results if you try the examples yourself,
6565
but your estimated costs and row counts will probably vary slightly
6666
because <command>ANALYZE</>'s statistics are random samples rather
@@ -114,12 +114,13 @@ EXPLAIN SELECT * FROM tenk1;
114114
</para>
115115

116116
<para>
117-
The costs are measured in units of disk page fetches; that is, 1.0
118-
equals one sequential disk page read, by definition. (CPU effort
119-
estimates are made too; they are converted into disk-page units using some
120-
fairly arbitrary fudge factors. If you want to experiment with these
121-
factors, see the list of run-time configuration parameters in
122-
<xref linkend="runtime-config-query-constants">.)
117+
The costs are measured in arbitrary units determined by the planner's
118+
cost parameters (see <xref linkend="runtime-config-query-constants">).
119+
Traditional practice is to measure the costs in units of disk page
120+
fetches; that is, <xref linkend="guc-seq-page-cost"> is conventionally
121+
set to <literal>1.0</> and the other cost parameters are set relative
122+
to that. The examples in this section are run with the default cost
123+
parameters.
123124
</para>
124125

125126
<para>
@@ -164,9 +165,9 @@ SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';
164165

165166
you will find out that <classname>tenk1</classname> has 358 disk
166167
pages and 10000 rows. So the cost is estimated at 358 page
167-
reads,defined as costing1.0apiece, plus 10000 * <xref
168-
linkend="guc-cpu-tuple-cost"> which is
169-
typically0.01(try <command>SHOW cpu_tuple_cost</command>).
168+
reads,costing <xref linkend="guc-seq-page-cost"> apiece (1.0by
169+
default), plus 10000 * <xreflinkend="guc-cpu-tuple-cost"> which is
170+
0.01by default.
170171
</para>
171172

172173
<para>
@@ -400,8 +401,9 @@ EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 &lt; 100 AND t
400401

401402
Note that the <quote>actual time</quote> values are in milliseconds of
402403
real time, whereas the <quote>cost</quote> estimates are expressed in
403-
arbitrary units of disk fetches; so they are unlikely to match up.
404-
The thing to pay attention to is the ratios.
404+
arbitrary units; so they are unlikely to match up.
405+
The thing to pay attention to is whether the ratios of actual time and
406+
estimated costs are consistent.
405407
</para>
406408

407409
<para>
@@ -427,7 +429,7 @@ EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 &lt; 100 AND t
427429
may be considerably larger, because it includes the time spent processing
428430
the result rows. In these commands, the time for the top plan node
429431
essentially is the time spent computing the new rows and/or locating the
430-
old ones, but it doesn't include the time spentmaking the changes.
432+
old ones, but it doesn't include the time spentapplying the changes.
431433
Time spent firing triggers, if any, is also outside the top plan node,
432434
and is shown separately for each trigger.
433435
</para>

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp