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

Commit5d9f146

Browse files
committed
What looks like some *major* improvements to btree indexing...
Patches from: aoki@CS.Berkeley.EDU (Paul M. Aoki)i gave jolly my btree bulkload code a long, long time ago but nevergave him a bunch of my bugfixes. here's a diff against the 6.0baseline.for some reason, this code has slowed down somewhat relative to theinsertion-build code on very small tables. don't know why -- it usedto be within about 10%. anyway, here are some (highly unscientific!)timings on a dec 3000/300 for synthetic tables with 10k, 100k and1000k tuples (basically, 1mb, 10mb and 100mb heaps). 'c' meansclustered (pre-sorted) inputs and 'u' means unclustered (randomlyordered) inputs. the 10k table basically fits in the buffer pool, butthe 100k and 1000k tables don't. as you can see, insertion build isfine if you've sorted your heaps on your index key or if your heapfits in core, but is absolutely horrible on unordered data (yes,that's 7.5 hours to index 100mb of data...) because of the zillions ofrandom i/os.if it doesn't work for you for whatever reason, you can always turn itback off by flipping the FastBuild flag in nbtree.c. i don't havetime to maintain it.good luck!baseline code:time psql -c 'create index c10 on k10 using btree (c int4_ops)' bttestreal 8.6time psql -c 'create index u10 on k10 using btree (b int4_ops)' bttestreal 9.1time psql -c 'create index c100 on k100 using btree (c int4_ops)' bttestreal 59.2time psql -c 'create index u100 on k100 using btree (b int4_ops)' bttestreal 652.4time psql -c 'create index c1000 on k1000 using btree (c int4_ops)' bttestreal 636.1time psql -c 'create index u1000 on k1000 using btree (b int4_ops)' bttestreal 26772.9bulkloading code:time psql -c 'create index c10 on k10 using btree (c int4_ops)' bttestreal 11.3time psql -c 'create index u10 on k10 using btree (b int4_ops)' bttestreal 10.4time psql -c 'create index c100 on k100 using btree (c int4_ops)' bttestreal 59.5time psql -c 'create index u100 on k100 using btree (b int4_ops)' bttestreal 63.5time psql -c 'create index c1000 on k1000 using btree (c int4_ops)' bttestreal 636.9time psql -c 'create index u1000 on k1000 using btree (b int4_ops)' bttestreal 701.0
1 parentd5a3f52 commit5d9f146

File tree

4 files changed

+388
-228
lines changed

4 files changed

+388
-228
lines changed

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

Lines changed: 51 additions & 22 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $Header: /cvsroot/pgsql/src/backend/access/nbtree/nbtree.c,v 1.12 1997/01/10 09:46:33 vadim Exp $
11+
* $Header: /cvsroot/pgsql/src/backend/access/nbtree/nbtree.c,v 1.13 1997/02/12 05:04:17 scrappy Exp $
1212
*
1313
* NOTES
1414
* This file contains only the public interface routines.
@@ -33,8 +33,8 @@
3333
# include<string.h>
3434
#endif
3535

36-
boolBuildingBtree= false;
37-
boolFastBuild=false;/*turn this on to make bulk builds work*/
36+
boolBuildingBtree= false;/* see comment in btbuild() */
37+
boolFastBuild=true;/*use sort/build instead of insertion build*/
3838

3939
/*
4040
* btbuild() -- build a new btree index.
@@ -67,21 +67,34 @@ btbuild(Relation heap,
6767
inti;
6868
BTItembtitem;
6969
#ifndefOMIT_PARTIAL_INDEX
70-
ExprContext*econtext;
71-
TupleTabletupleTable;
72-
TupleTableSlot*slot;
70+
ExprContext*econtext= (ExprContext*)NULL;
71+
TupleTabletupleTable= (TupleTable)NULL;
72+
TupleTableSlot*slot= (TupleTableSlot*)NULL;
7373
#endif
7474
Oidhrelid,irelid;
7575
Node*pred,*oldPred;
76-
void*spool;
76+
void*spool= (void*)NULL;
7777
boolisunique;
78-
78+
boolusefast;
79+
80+
#if0
81+
ResetBufferUsage();
82+
#endif
83+
7984
/* note that this is a new btree */
8085
BuildingBtree= true;
8186

8287
pred=predInfo->pred;
8388
oldPred=predInfo->oldPred;
8489

90+
/*
91+
* bootstrap processing does something strange, so don't use
92+
* sort/build for initial catalog indices. at some point i need
93+
* to look harder at this. (there is some kind of incremental
94+
* processing going on there.) -- pma 08/29/95
95+
*/
96+
usefast= (FastBuild&&IsNormalProcessingMode());
97+
8598
/* see if index is unique */
8699
isunique=IndexIsUniqueNoCache(RelationGetRelationId(index));
87100

@@ -110,13 +123,16 @@ btbuild(Relation heap,
110123
slot=ExecAllocTableSlot(tupleTable);
111124
econtext=makeNode(ExprContext);
112125
FillDummyExprContext(econtext,slot,htupdesc,InvalidBuffer);
126+
127+
/*
128+
* we never want to use sort/build if we are extending an
129+
* existing partial index -- it works by inserting the
130+
* newly-qualifying tuples into the existing index.
131+
* (sort/build would overwrite the existing index with one
132+
* consisting of the newly-qualifying tuples.)
133+
*/
134+
usefast= false;
113135
}
114-
else
115-
{
116-
econtext=NULL;
117-
tupleTable=NULL;
118-
slot=NULL;
119-
}
120136
#endif/* OMIT_PARTIAL_INDEX */
121137

122138
/* start a heap scan */
@@ -126,12 +142,10 @@ btbuild(Relation heap,
126142
/* build the index */
127143
nhtups=nitups=0;
128144

129-
if (FastBuild) {
145+
if (usefast) {
130146
spool=_bt_spoolinit(index,7);
131147
res= (InsertIndexResult)NULL;
132148
}
133-
else
134-
spool=NULL;
135149

136150
for (;HeapTupleIsValid(htup);htup=heap_getnext(hscan,0,&buffer)) {
137151

@@ -219,7 +233,7 @@ btbuild(Relation heap,
219233
* into a spool page for subsequent processing. otherwise, we
220234
* insert into the btree.
221235
*/
222-
if (FastBuild) {
236+
if (usefast) {
223237
_bt_spool(index,btitem,spool);
224238
}else {
225239
res=_bt_doinsert(index,btitem,isunique,heap);
@@ -248,12 +262,24 @@ btbuild(Relation heap,
248262
* merging the runs, (2) inserting the sorted tuples into btree
249263
* pages and (3) building the upper levels.
250264
*/
251-
if (FastBuild) {
252-
_bt_spool(index, (BTItem)NULL,spool);/* flush spool */
265+
if (usefast) {
266+
_bt_spool(index, (BTItem)NULL,spool);/* flushthespool */
253267
_bt_leafbuild(index,spool);
254268
_bt_spooldestroy(spool);
255269
}
256270

271+
#if0
272+
{
273+
externintReadBufferCount,BufferHitCount,BufferFlushCount;
274+
externlongNDirectFileRead,NDirectFileWrite;
275+
276+
printf("buffer(%d): r=%d w=%d\n",heap->rd_rel->relblocksz,
277+
ReadBufferCount-BufferHitCount,BufferFlushCount);
278+
printf("direct(%d): r=%d w=%d\n",LocalBlockSize,
279+
NDirectFileRead,NDirectFileWrite);
280+
}
281+
#endif
282+
257283
/*
258284
* Since we just counted the tuples in the heap, we update its
259285
* stats in pg_class to guarantee that the planner takes advantage
@@ -312,7 +338,10 @@ btinsert(Relation rel, Datum *datum, char *nulls, ItemPointer ht_ctid, Relation
312338

313339
pfree(btitem);
314340
pfree(itup);
315-
341+
342+
/* adjust any active scans that will be affected by this insertion */
343+
_bt_adjscans(rel,&(res->pointerData),BT_INSERT);
344+
316345
return (res);
317346
}
318347

@@ -533,7 +562,7 @@ void
533562
btdelete(Relationrel,ItemPointertid)
534563
{
535564
/* adjust any active scans that will be affected by this deletion */
536-
_bt_adjscans(rel,tid);
565+
_bt_adjscans(rel,tid,BT_DELETE);
537566

538567
/* delete the data from the page */
539568
_bt_pagedel(rel,tid);

0 commit comments

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