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

Commit5374d09

Browse files
committed
Change planner to use the current true disk file size as its estimate of
a relation's number of blocks, rather than the possibly-obsolete valuein pg_class.relpages. Scale the value in pg_class.reltuples correspondinglyto arrive at a hopefully more accurate number of rows. When pg_classcontains 0/0, estimate a tuple width from the column datatypes and dividethat into current file size to estimate number of rows. This improvedmethodology allows us to jettison the ancient hacks that put bogus defaultvalues into pg_class when a table is first created. Also, per a suggestionfrom Simon, make VACUUM (but not VACUUM FULL or ANALYZE) adjust the valueit puts into pg_class.reltuples to try to represent the mean tuple densityinstead of the minimal density that actually prevails just after VACUUM.These changes alter the plans selected for certain regression tests, soupdate the expected files accordingly. (I removed join_1.out becauseit's not clear if it still applies; we can add back any variant versionsas they are shown to be needed.)
1 parentb7bcea6 commit5374d09

File tree

18 files changed

+1221
-3172
lines changed

18 files changed

+1221
-3172
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 6 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
<!--
22
Documentation of the system catalogs, directed toward PostgreSQL developers
3-
$PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.92 2004/11/15 06:32:13 neilc Exp $
3+
$PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.93 2004/12/01 19:00:27 tgl Exp $
44
-->
55

66
<chapter id="catalogs">
@@ -1079,11 +1079,12 @@
10791079
<entry><type>int4</type></entry>
10801080
<entry></entry>
10811081
<entry>
1082-
Size of the on-disk representation of this table in pages (size
1082+
Size of the on-disk representation of this table in pages (ofsize
10831083
<symbol>BLCKSZ</symbol>).
10841084
This is only an estimate used by the planner.
10851085
It is updated by <command>VACUUM</command>,
1086-
<command>ANALYZE</command>, and <command>CREATE INDEX</command>.
1086+
<command>ANALYZE</command>, and a few DDL commands
1087+
such as <command>CREATE INDEX</command>.
10871088
</entry>
10881089
</row>
10891090

@@ -1095,7 +1096,8 @@
10951096
Number of rows in the table.
10961097
This is only an estimate used by the planner.
10971098
It is updated by <command>VACUUM</command>,
1098-
<command>ANALYZE</command>, and <command>CREATE INDEX</command>.
1099+
<command>ANALYZE</command>, and a few DDL commands
1100+
such as <command>CREATE INDEX</command>.
10991101
</entry>
11001102
</row>
11011103

‎doc/src/sgml/diskusage.sgml

Lines changed: 25 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -1,17 +1,13 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/diskusage.sgml,v 1.11 2004/06/21 04:06:03 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/diskusage.sgml,v 1.12 2004/12/01 19:00:27 tgl Exp $
33
-->
44

55
<chapter id="diskusage">
66
<title>Monitoring Disk Usage</title>
77

88
<para>
99
This chapter discusses how to monitor the disk usage of a
10-
<productname>PostgreSQL</> database system. In the current
11-
release, the database administrator does not have much control over
12-
the on-disk storage layout, so this chapter is mostly informative
13-
and can give you some ideas how to manage the disk usage with
14-
operating system tools.
10+
<productname>PostgreSQL</> database system.
1511
</para>
1612

1713
<sect1 id="disk-usage">
@@ -23,11 +19,12 @@ $PostgreSQL: pgsql/doc/src/sgml/diskusage.sgml,v 1.11 2004/06/21 04:06:03 tgl Ex
2319

2420
<para>
2521
Each table has a primary heap disk file where most of the data is
26-
stored. To store long column values, there is also a
27-
<acronym>TOAST</> file associated with the table, named based on the
28-
table's OID (actually <literal>pg_class.relfilenode</>), and an index on the
29-
<acronym>TOAST</> table. There also may be indexes associated with
30-
the base table.
22+
stored. If the table has any columns with potentially-wide values,
23+
there is also a <acronym>TOAST</> file associated with the table,
24+
which is used to store values too wide to fit comfortably in the main
25+
table. There will be one index on the
26+
<acronym>TOAST</> table, if present. There may also be indexes associated
27+
with the base table.
3128
</para>
3229

3330
<para>
@@ -45,18 +42,24 @@ SELECT relfilenode, relpages FROM pg_class WHERE relname = 'customer';
4542
16806 | 60
4643
(1 row)
4744
</programlisting>
48-
Each page is typically 8 kilobytes. (Remember, <literal>relpages</>
49-
is only updated by <command>VACUUM</> and <command>ANALYZE</>.)
45+
Each page is typically 8 kilobytes. (Remember, <structfield>relpages</>
46+
is only updated by <command>VACUUM</>, <command>ANALYZE</>, and
47+
a few DDL commands such as <command>CREATE INDEX</>.) The
48+
<structfield>relfilenode</> value is of interest if you want to examine
49+
the table's disk file directly.
5050
</para>
5151

5252
<para>
5353
To show the space used by <acronym>TOAST</> tables, use a query
54-
like the following, substituting the <literal>relfilenode</literal>
55-
number of the heap (determined by the query above):
54+
like the following:
5655
<programlisting>
5756
SELECT relname, relpages
58-
FROM pg_class
59-
WHERE relname = 'pg_toast_16806' OR relname = 'pg_toast_16806_index'
57+
FROM pg_class,
58+
(SELECT reltoastrelid FROM pg_class
59+
WHERE relname = 'customer') ss
60+
WHERE oid = ss.reltoastrelid
61+
OR oid = (SELECT reltoastidxid FROM pg_class
62+
WHERE oid = ss.reltoastrelid)
6063
ORDER BY relname;
6164

6265
relname | relpages
@@ -74,7 +77,7 @@ SELECT c2.relname, c2.relpages
7477
WHERE c.relname = 'customer'
7578
AND c.oid = i.indrelid
7679
AND c2.oid = i.indexrelid
77-
ORDER BY c2.relname;
80+
ORDER BY c2.relname;
7881

7982
relname | relpages
8083
----------------------+----------
@@ -113,13 +116,10 @@ SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
113116

114117
<para>
115118
The most important disk monitoring task of a database administrator
116-
is to make sure the disk doesn't grow full. A filled data disk may
117-
result in subsequent corruption of database indexes, but not of the
118-
tables themselves. If the WAL files are on the same disk (as
119-
is the case for a default configuration) then a filled disk during
120-
database initialization may result in corrupted or incomplete WAL
121-
files. This failure condition is detected and the database server
122-
will refuse to start up.
119+
is to make sure the disk doesn't grow full. A filled data disk will
120+
not result in data corruption, but it may well prevent useful activity
121+
from occurring. If the disk holding the WAL files grows full, database
122+
server panic and consequent shutdown may occur.
123123
</para>
124124

125125
<para>

‎doc/src/sgml/perform.sgml

Lines changed: 8 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/perform.sgml,v 1.47 2004/11/15 06:32:14 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/perform.sgml,v 1.48 2004/12/01 19:00:27 tgl Exp $
33
-->
44

55
<chapter id="performance-tips">
@@ -389,14 +389,15 @@ SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE 't
389389
<para>
390390
For efficiency reasons, <structfield>reltuples</structfield>
391391
and <structfield>relpages</structfield> are not updated on-the-fly,
392-
and so they usually contain only approximate values (which is good
393-
enough for the planner's purposes). They are initialized with dummy
394-
values (presently 1000 and 10 respectively) when a table is created.
395-
They are updated by certain commands, presently <command>VACUUM</>,
396-
<command>ANALYZE</>, and <command>CREATE INDEX</>. A stand-alone
392+
and so they usually contain somewhat out-of-date values.
393+
They are updated by <command>VACUUM</>, <command>ANALYZE</>, and a
394+
few DDL commands such as <command>CREATE INDEX</>. A stand-alone
397395
<command>ANALYZE</>, that is one not part of <command>VACUUM</>,
398396
generates an approximate <structfield>reltuples</structfield> value
399-
since it does not read every row of the table.
397+
since it does not read every row of the table. The planner
398+
will scale the values it finds in <structname>pg_class</structname>
399+
to match the current physical table size, thus obtaining a closer
400+
approximation.
400401
</para>
401402

402403
<indexterm>

‎doc/src/sgml/release.sgml

Lines changed: 19 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/release.sgml,v 1.314 2004/11/27 21:56:04 petere Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/release.sgml,v 1.315 2004/12/01 19:00:27 tgl Exp $
33
-->
44

55
<appendix id="release">
@@ -527,6 +527,24 @@ $PostgreSQL: pgsql/doc/src/sgml/release.sgml,v 1.314 2004/11/27 21:56:04 petere
527527
</para>
528528
</listitem>
529529

530+
<listitem>
531+
<para>
532+
Use dynamically-generated table size estimates while planning (Tom)
533+
</para>
534+
<para>
535+
The optimizer now uses a table's current actual size on disk as its
536+
estimate of the number of blocks in the table, and it makes an estimate
537+
of the number of rows in the table based on the current size on disk.
538+
Formerly, the
539+
<structname>pg_class</structname>.<structfield>relpages</structfield>
540+
and
541+
<structname>pg_class</structname>.<structfield>reltuples</structfield>
542+
fields were used as-is, but these values might be quite out-of-date,
543+
leading to poor choices of plans. They are now treated only as an
544+
indication of the table's density (rows per page).
545+
</para>
546+
</listitem>
547+
530548
<listitem>
531549
<para>
532550
Improved index usage with <literal>OR</> clauses (Tom)

‎src/backend/access/nbtree/nbtree.c

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,7 @@
1212
* Portions Copyright (c) 1994, Regents of the University of California
1313
*
1414
* IDENTIFICATION
15-
* $PostgreSQL: pgsql/src/backend/access/nbtree/nbtree.c,v 1.122 2004/11/17 03:13:38 neilc Exp $
15+
* $PostgreSQL: pgsql/src/backend/access/nbtree/nbtree.c,v 1.123 2004/12/01 19:00:37 tgl Exp $
1616
*
1717
*-------------------------------------------------------------------------
1818
*/
@@ -785,6 +785,10 @@ btvacuumcleanup(PG_FUNCTION_ARGS)
785785
* Do the physical truncation.
786786
*/
787787
RelationTruncate(rel,new_pages);
788+
789+
/* update statistics */
790+
stats->pages_removed=num_pages-new_pages;
791+
788792
num_pages=new_pages;
789793
}
790794
}

‎src/backend/catalog/heap.c

Lines changed: 7 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/heap.c,v 1.276 2004/08/31 17:10:36 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/heap.c,v 1.277 2004/12/01 19:00:39 tgl Exp $
1212
*
1313
*
1414
* INTERFACE ROUTINES
@@ -607,37 +607,22 @@ AddNewRelationTuple(Relation pg_class_desc,
607607
*/
608608
new_rel_reltup=new_rel_desc->rd_rel;
609609

610-
/*
611-
* Here we insert bogus estimates of the size of the new relation. In
612-
* reality, of course, the new relation has 0 tuples and pages, and if
613-
* we were tracking these statistics accurately then we'd set the
614-
* fields that way. But at present the stats will be updated only by
615-
* VACUUM or CREATE INDEX, and the user might insert a lot of tuples
616-
* before he gets around to doing either of those.So, instead of
617-
* saying the relation is empty, we insert guesstimates. The point is
618-
* to keep the optimizer from making really stupid choices on
619-
* never-yet-vacuumed tables; so the estimates need only be large
620-
* enough to discourage the optimizer from using nested-loop plans.
621-
* With this hack, nested-loop plans will be preferred only after the
622-
* table has been proven to be small by VACUUM or CREATE INDEX.
623-
* Maintaining the stats on-the-fly would solve the problem more
624-
* cleanly, but the overhead of that would likely cost more than it'd
625-
* save. (NOTE: CREATE INDEX inserts the same bogus estimates if it
626-
* finds the relation has 0 rows and pages. See index.c.)
627-
*/
628610
switch (relkind)
629611
{
630612
caseRELKIND_RELATION:
631613
caseRELKIND_INDEX:
632614
caseRELKIND_TOASTVALUE:
633-
new_rel_reltup->relpages=10;/* bogus estimates */
634-
new_rel_reltup->reltuples=1000;
615+
/* The relation is real, but as yet empty */
616+
new_rel_reltup->relpages=0;
617+
new_rel_reltup->reltuples=0;
635618
break;
636619
caseRELKIND_SEQUENCE:
620+
/* Sequences always have a known size */
637621
new_rel_reltup->relpages=1;
638622
new_rel_reltup->reltuples=1;
639623
break;
640-
default:/* views, etc */
624+
default:
625+
/* Views, etc, have no disk storage */
641626
new_rel_reltup->relpages=0;
642627
new_rel_reltup->reltuples=0;
643628
break;

‎src/backend/catalog/index.c

Lines changed: 8 additions & 49 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/index.c,v 1.241 2004/10/15 22:39:53 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/index.c,v 1.242 2004/12/01 19:00:39 tgl Exp $
1212
*
1313
*
1414
* INTERFACE ROUTINES
@@ -33,35 +33,22 @@
3333
#include"catalog/index.h"
3434
#include"catalog/indexing.h"
3535
#include"catalog/pg_constraint.h"
36-
#include"catalog/pg_index.h"
3736
#include"catalog/pg_opclass.h"
38-
#include"catalog/pg_proc.h"
3937
#include"catalog/pg_type.h"
4038
#include"executor/executor.h"
4139
#include"miscadmin.h"
4240
#include"optimizer/clauses.h"
43-
#include"optimizer/prep.h"
4441
#include"parser/parse_expr.h"
45-
#include"parser/parse_func.h"
4642
#include"storage/sinval.h"
4743
#include"storage/smgr.h"
4844
#include"utils/builtins.h"
49-
#include"utils/catcache.h"
5045
#include"utils/fmgroids.h"
5146
#include"utils/inval.h"
5247
#include"utils/lsyscache.h"
5348
#include"utils/relcache.h"
5449
#include"utils/syscache.h"
5550

5651

57-
/*
58-
* macros used in guessing how many tuples are on a page.
59-
*/
60-
#defineAVG_ATTR_SIZE 8
61-
#defineNTUPLES_PER_PAGE(natts) \
62-
((BLCKSZ - MAXALIGN(sizeof(PageHeaderData))) / \
63-
((natts) * AVG_ATTR_SIZE + MAXALIGN(sizeof(HeapTupleHeaderData))))
64-
6552
/* non-export function prototypes */
6653
staticTupleDescConstructTupleDescriptor(RelationheapRelation,
6754
IndexInfo*indexInfo,
@@ -1153,6 +1140,8 @@ setNewRelfilenode(Relation relation)
11531140

11541141
/* update the pg_class row */
11551142
rd_rel->relfilenode=newrelfilenode;
1143+
rd_rel->relpages=0;/* it's empty until further notice */
1144+
rd_rel->reltuples=0;
11561145
simple_heap_update(pg_class,&tuple->t_self,tuple);
11571146
CatalogUpdateIndexes(pg_class,tuple);
11581147

@@ -1170,7 +1159,7 @@ setNewRelfilenode(Relation relation)
11701159
*
11711160
* Update pg_class' relpages and reltuples statistics for the given relation
11721161
* (which can be either a table or an index). Note that this is not used
1173-
* in the context of VACUUM.
1162+
* in the context of VACUUM, only CREATE INDEX.
11741163
* ----------------
11751164
*/
11761165
void
@@ -1209,7 +1198,8 @@ UpdateStats(Oid relid, double reltuples)
12091198
* Find the tuple to update in pg_class. Normally we make a copy of
12101199
* the tuple using the syscache, modify it, and apply heap_update. But
12111200
* in bootstrap mode we can't use heap_update, so we cheat and
1212-
* overwrite the tuple in-place.
1201+
* overwrite the tuple in-place. (Note: as of PG 8.0 this isn't called
1202+
* during bootstrap, but leave the code here for possible future use.)
12131203
*
12141204
* We also must cheat if reindexing pg_class itself, because the target
12151205
* index may presently not be part of the set of indexes that
@@ -1245,45 +1235,14 @@ UpdateStats(Oid relid, double reltuples)
12451235
elog(ERROR,"could not find tuple for relation %u",relid);
12461236
rd_rel= (Form_pg_class)GETSTRUCT(tuple);
12471237

1248-
/*
1249-
* Figure values to insert.
1250-
*
1251-
* If we found zero tuples in the scan, do NOT believe it; instead put a
1252-
* bogus estimate into the statistics fields. Otherwise, the common
1253-
* pattern "CREATE TABLE; CREATE INDEX; insert data" leaves the table
1254-
* with zero size statistics until a VACUUM is done. The optimizer
1255-
* will generate very bad plans if the stats claim the table is empty
1256-
* when it is actually sizable. See also CREATE TABLE in heap.c.
1257-
*
1258-
* Note: this path is also taken during bootstrap, because bootstrap.c
1259-
* passes reltuples = 0 after loading a table.We have to estimate
1260-
* some number for reltuples based on the actual number of pages.
1261-
*/
1262-
relpages=RelationGetNumberOfBlocks(whichRel);
1263-
1264-
if (reltuples==0)
1265-
{
1266-
if (relpages==0)
1267-
{
1268-
/* Bogus defaults for a virgin table, same as heap.c */
1269-
reltuples=1000;
1270-
relpages=10;
1271-
}
1272-
elseif (whichRel->rd_rel->relkind==RELKIND_INDEX&&relpages <=2)
1273-
{
1274-
/* Empty index, leave bogus defaults in place */
1275-
reltuples=1000;
1276-
}
1277-
else
1278-
reltuples= ((double)relpages)*NTUPLES_PER_PAGE(whichRel->rd_rel->relnatts);
1279-
}
1280-
12811238
/*
12821239
* Update statistics in pg_class, if they changed.(Avoiding an
12831240
* unnecessary update is not just a tiny performance improvement; it
12841241
* also reduces the window wherein concurrent CREATE INDEX commands
12851242
* may conflict.)
12861243
*/
1244+
relpages=RelationGetNumberOfBlocks(whichRel);
1245+
12871246
if (rd_rel->relpages!= (int32)relpages||
12881247
rd_rel->reltuples!= (float4)reltuples)
12891248
{

0 commit comments

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