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

Commitd29b668

Browse files
committed
Tweak fillfactor code as per my recent proposal. Fix nbtsort.c so that
it can handle small fillfactors for ordinary-sized index entries withoutfailing on large ones; fix nbtinsert.c to distinguish leaf and nonleafpages; change the minimum fillfactor to 10% for all index types.
1 parent1be8f80 commitd29b668

File tree

7 files changed

+41
-28
lines changed

7 files changed

+41
-28
lines changed

‎doc/src/sgml/ref/create_index.sgml‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_index.sgml,v 1.54 2006/07/04 18:07:24 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_index.sgml,v 1.55 2006/07/11 21:05:57 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -221,17 +221,17 @@ CREATE [ UNIQUE ] INDEX <replaceable class="parameter">name</replaceable> ON <re
221221
<listitem>
222222
<para>
223223
The fillfactor for an index is a percentage that determines how full
224-
the index method will try to pack index pages. For B-trees, pages
224+
the index method will try to pack index pages. For B-trees,leafpages
225225
are filled to this percentage during initial index build, and also
226226
when extending the index at the right (largest key values). If pages
227227
subsequently become completely full, they will be split, leading to
228228
gradual degradation in the index's efficiency. B-trees use a default
229-
fillfactor of 90, but any value from70 to 100 can be selected.
229+
fillfactor of 90, but any value from10 to 100 can be selected.
230230
If the table is static then fillfactor 100 is best to minimize the
231231
index's physical size, but for heavily updated tables a smaller
232232
fillfactor is better to minimize the need for page splits. The
233233
other index methods use fillfactor in different but roughly analogous
234-
ways; the default fillfactorand allowed range varies.
234+
ways; the default fillfactorvaries between methods.
235235
</para>
236236
</listitem>
237237
</varlistentry>

‎src/backend/access/gin/ginutil.c‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/gin/ginutil.c,v 1.3 2006/07/03 22:45:36 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/gin/ginutil.c,v 1.4 2006/07/11 21:05:57 tgl Exp $
1212
*-------------------------------------------------------------------------
1313
*/
1414

@@ -213,7 +213,7 @@ ginoptions(PG_FUNCTION_ARGS)
213213
* It's not clear that fillfactor is useful for GIN, but for the moment
214214
* we'll accept it anyway. (It won't do anything...)
215215
*/
216-
#defineGIN_MIN_FILLFACTOR50
216+
#defineGIN_MIN_FILLFACTOR10
217217
#defineGIN_DEFAULT_FILLFACTOR100
218218

219219
result=default_reloptions(reloptions,validate,

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

Lines changed: 8 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/access/nbtree/nbtinsert.c,v 1.139 2006/07/03 22:45:37 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/access/nbtree/nbtinsert.c,v 1.140 2006/07/11 21:05:57 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -991,7 +991,8 @@ _bt_split(Relation rel, Buffer buf, OffsetNumber firstright,
991991
* inserting successively increasing keys (consider sequences, timestamps,
992992
* etc) we will end up with a tree whose pages are about fillfactor% full,
993993
* instead of the 50% full result that we'd get without this special case.
994-
* This is the same as nbtsort.c produces for a newly-created tree.
994+
* This is the same as nbtsort.c produces for a newly-created tree. Note
995+
* that leaf and nonleaf pages use different fillfactors.
995996
*
996997
* We are passed the intended insert position of the new tuple, expressed as
997998
* the offsetnumber of the tuple it must go in front of. (This could be
@@ -1025,10 +1026,14 @@ _bt_findsplitloc(Relation rel,
10251026
/* Passed-in newitemsz is MAXALIGNED but does not include line pointer */
10261027
newitemsz+=sizeof(ItemIdData);
10271028
state.newitemsz=newitemsz;
1028-
state.fillfactor=RelationGetFillFactor(rel,BTREE_DEFAULT_FILLFACTOR);
10291029
state.is_leaf=P_ISLEAF(opaque);
10301030
state.is_rightmost=P_RIGHTMOST(opaque);
10311031
state.have_split= false;
1032+
if (state.is_leaf)
1033+
state.fillfactor=RelationGetFillFactor(rel,
1034+
BTREE_DEFAULT_FILLFACTOR);
1035+
else
1036+
state.fillfactor=BTREE_NONLEAF_FILLFACTOR;
10321037

10331038
/* Total free space available on a btree page, after fixed overhead */
10341039
leftspace=rightspace=

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

Lines changed: 15 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -27,10 +27,10 @@
2727
* insertion would cause a split (and not only of the leaf page; the need
2828
* for a split would cascade right up the tree). The steady-state load
2929
* factor for btrees is usually estimated at 70%. We choose to pack leaf
30-
* pages to the user-controllable fill factor while upper pages are always
31-
* packed to 70%. This gives us reasonable density (there aren't many upper
32-
* pages if the keys are reasonable-size) withoutincurring a lot of cascading
33-
* splits during early insertions.
30+
* pages to the user-controllable fill factor(default 90%)while upper pages
31+
*are alwayspacked to 70%. This gives us reasonable density (there aren't
32+
*many upperpages if the keys are reasonable-size) withoutrisking a lot of
33+
*cascadingsplits during early insertions.
3434
*
3535
* Formerly the index pages being built were kept in shared buffers, but
3636
* that is of no value (since other backends have no interest in them yet)
@@ -57,7 +57,7 @@
5757
* Portions Copyright (c) 1994, Regents of the University of California
5858
*
5959
* IDENTIFICATION
60-
* $PostgreSQL: pgsql/src/backend/access/nbtree/nbtsort.c,v 1.104 2006/07/03 22:45:37 tgl Exp $
60+
* $PostgreSQL: pgsql/src/backend/access/nbtree/nbtsort.c,v 1.105 2006/07/11 21:05:57 tgl Exp $
6161
*
6262
*-------------------------------------------------------------------------
6363
*/
@@ -349,7 +349,7 @@ _bt_pagestate(BTWriteState *wstate, uint32 level)
349349
state->btps_level=level;
350350
/* set "full" threshold based on level. See notes at head of file. */
351351
if (level>0)
352-
state->btps_full= (BLCKSZ* (100-BTREE_MIN_FILLFACTOR) /100);
352+
state->btps_full= (BLCKSZ* (100-BTREE_NONLEAF_FILLFACTOR) /100);
353353
else
354354
state->btps_full=RelationGetTargetPageFreeSpace(wstate->index,
355355
BTREE_DEFAULT_FILLFACTOR);
@@ -499,11 +499,16 @@ _bt_buildadd(BTWriteState *wstate, BTPageState *state, IndexTuple itup)
499499
"Consider a function index of an MD5 hash of the value, "
500500
"or use full text indexing.")));
501501

502-
if (pgspc<itupsz||pgspc<state->btps_full)
502+
/*
503+
* Check to see if page is "full". It's definitely full if the item
504+
* won't fit. Otherwise, compare to the target freespace derived from
505+
* the fillfactor. However, we must put at least two items on each
506+
* page, so disregard fillfactor if we don't have that many.
507+
*/
508+
if (pgspc<itupsz|| (pgspc<state->btps_full&&last_off>P_FIRSTKEY))
503509
{
504510
/*
505-
* Item won't fit on this page, or we feel the page is full enough
506-
* already. Finish off the page and write it out.
511+
* Finish off the page and write it out.
507512
*/
508513
Pageopage=npage;
509514
BlockNumberoblkno=nblkno;
@@ -522,8 +527,7 @@ _bt_buildadd(BTWriteState *wstate, BTPageState *state, IndexTuple itup)
522527
* rearrange the old page so that the 'last item' becomes its high key
523528
* rather than a true data item. There had better be at least two
524529
* items on the page already, else the page would be empty of useful
525-
* data. (Hence, we must allow pages to be packed at least 2/3rds
526-
* full; the 70% figure used above is close to minimum.)
530+
* data.
527531
*/
528532
Assert(last_off>P_FIRSTKEY);
529533
ii=PageGetItemId(opage,last_off);

‎src/include/access/gist_private.h‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/access/gist_private.h,v 1.21 2006/07/1117:04:13 momjian Exp $
10+
* $PostgreSQL: pgsql/src/include/access/gist_private.h,v 1.22 2006/07/1121:05:57 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -272,7 +272,7 @@ extern Datum gistgetmulti(PG_FUNCTION_ARGS);
272272
#defineGiSTPageSize \
273273
( BLCKSZ - SizeOfPageHeaderData - MAXALIGN(sizeof(GISTPageOpaqueData)) )
274274

275-
#defineGIST_MIN_FILLFACTOR50
275+
#defineGIST_MIN_FILLFACTOR10
276276
#defineGIST_DEFAULT_FILLFACTOR90
277277

278278
externDatumgistoptions(PG_FUNCTION_ARGS);

‎src/include/access/hash.h‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/access/hash.h,v 1.71 2006/07/03 22:45:39 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/access/hash.h,v 1.72 2006/07/11 21:05:57 tgl Exp $
1111
*
1212
* NOTES
1313
*modeled after Margo Seltzer's hash implementation for unix.
@@ -167,7 +167,7 @@ typedef HashMetaPageData *HashMetaPage;
167167
MAXALIGN(sizeof(HashPageOpaqueData)) - \
168168
sizeof(ItemIdData))
169169

170-
#defineHASH_MIN_FILLFACTOR50
170+
#defineHASH_MIN_FILLFACTOR10
171171
#defineHASH_DEFAULT_FILLFACTOR75
172172

173173
/*

‎src/include/access/nbtree.h‎

Lines changed: 8 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/access/nbtree.h,v 1.100 2006/07/03 22:45:39 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/access/nbtree.h,v 1.101 2006/07/11 21:05:57 tgl Exp $
1111
*
1212
*-------------------------------------------------------------------------
1313
*/
@@ -107,11 +107,15 @@ typedef struct BTMetaPageData
107107
MAXALIGN(sizeof(BTPageOpaqueData))) / 3 - sizeof(ItemIdData))
108108

109109
/*
110-
* Because of above, min fillfactor can't be less than 2/3rds; see notes in
111-
* nbtsort.c before you change these!
110+
* The leaf-page fillfactor defaults to 90% but is user-adjustable.
111+
* For pages above the leaf level, we use a fixed 70% fillfactor.
112+
* The fillfactor is applied during index build and when splitting
113+
* a rightmost page; when splitting non-rightmost pages we try to
114+
* divide the data equally.
112115
*/
113-
#defineBTREE_MIN_FILLFACTOR70
116+
#defineBTREE_MIN_FILLFACTOR10
114117
#defineBTREE_DEFAULT_FILLFACTOR90
118+
#defineBTREE_NONLEAF_FILLFACTOR70
115119

116120
/*
117121
*Test whether two btree entries are "the same".

0 commit comments

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