Re: Vertical Partitioning with TOAST - Mailing list pgsql-hackers
From | Jim C. Nasby |
---|---|
Subject | Re: Vertical Partitioning with TOAST |
Date | |
Msg-id | 20051208050227.GH16053@nasby.net Whole thread Raw |
In response to | Vertical Partitioning with TOAST (Junji TERAMOTO <teramoto.junji@lab.ntt.co.jp>) |
Responses |
Re: Vertical Partitioning with TOAST
|
List | pgsql-hackers |
This seems like a useful feature to add, allowing for easy built-in verticle partitioning. Are there issues with the patch as-is? (Other than it probably should have gone to -patches...) On Thu, Dec 01, 2005 at 05:59:08PM +0900, Junji TERAMOTO wrote: > Hi all, > > I wrote a experimental patch for a vertical partitioning > function. > > I decided to use the code of TOAST to create the function > easily. In a word, the row that the user specified is forcedly > driven out with TOAST. > > The performance gain of 10% was seen by driving out c_data of the > customer table in the DBT-2 benchmark in our environment. > > The mechanism of TOAST is an overdesigned system to use it for a > vertical partitioning. Because the overhead of processing is large, > the performance might down according to the environment. > > There are seriously a lot of things that should be considered if > a vertical partitioning is mounted. > For instance, TOAST index is omitted, and ctid is used for link. > > Your comments are welcome. Thanks. > > --- > How To Use > --- > Use "ALTER TABLE" command. > http://www.postgresql.org/docs/8.1/static/sql-altertable.html > > ALTER TABLE name ALTER COLUMN column SET STRAGE FORCEEXTERNAL; > > I do not understand whether "FORCEEXTERNAL" is an appropriate > word. Please teach when there is a better word... > > > -- > Junji Teramoto > diff -purN postgresql-8.1.0.org/src/backend/access/heap/heapam.c postgresql-8.1.0/src/backend/access/heap/heapam.c > --- postgresql-8.1.0.org/src/backend/access/heap/heapam.c 2005-10-15 11:49:08.000000000 +0900 > +++ postgresql-8.1.0/src/backend/access/heap/heapam.c 2005-12-01 15:31:38.307713257 +0900 > @@ -1070,6 +1070,36 @@ heap_get_latest_tid(Relation relation, > } /* end of loop */ > } > > +// Add by junji from here > +/* > + * has_rel_forceexternal - Is there "SET STORAGE FORCEEXTERNAL"ed rows? > + */ > +bool > +has_rel_forceexternal(Relation relation) > +{ > + TupleDesc tupleDesc; > + Form_pg_attribute *att; > + int numAttrs; > + int i; > + > + /* > + * Get the tuple descriptor and break down the tuple(s) into fields. > + */ > + tupleDesc = relation->rd_att; > + att = tupleDesc->attrs; > + numAttrs = tupleDesc->natts; > + > + for (i = 0; i < numAttrs; i++) > + { > + if (att[i]->attstorage == 'f') > + return true; > + } > + > + return false; > +} > +// Add by junji to here > + > + > /* > * heap_insert - insert tuple into a heap > * > @@ -1130,6 +1160,9 @@ heap_insert(Relation relation, HeapTuple > * out-of-line attributes from some other relation, invoke the toaster. > */ > if (HeapTupleHasExternal(tup) || > +// Add by junji from here > + (has_rel_forceexternal(relation)) || > +// Add by junji to here > (MAXALIGN(tup->t_len) > TOAST_TUPLE_THRESHOLD)) > heap_tuple_toast_attrs(relation, tup, NULL); > > @@ -1762,6 +1795,9 @@ l2: > */ > need_toast = (HeapTupleHasExternal(&oldtup) || > HeapTupleHasExternal(newtup) || > +// Add by junji from here > + (has_rel_forceexternal(relation)) || > +// Add by junji to here > (MAXALIGN(newtup->t_len) > TOAST_TUPLE_THRESHOLD)); > > newtupsize = MAXALIGN(newtup->t_len); > diff -purN postgresql-8.1.0.org/src/backend/access/heap/tuptoaster.c postgresql-8.1.0/src/backend/access/heap/tuptoaster.c > --- postgresql-8.1.0.org/src/backend/access/heap/tuptoaster.c 2005-10-15 11:49:09.000000000 +0900 > +++ postgresql-8.1.0/src/backend/access/heap/tuptoaster.c 2005-12-01 15:29:29.722579466 +0900 > @@ -512,6 +512,46 @@ toast_insert_or_update(Relation rel, Hea > } > } > > +// Add by junji from here > + /* > + * We look for attributes of attstorage 'f'. > + */ > + if (rel->rd_rel->reltoastrelid != InvalidOid) > + { > + Datum old_value; > + > + /*------ > + * Search for the biggest yet inlined attribute with > + * attstorage equals 'x' or 'e' > + *------ > + */ > + for (i = 0; i < numAttrs; i++) > + { > + if (toast_action[i] == 'p') > + continue; > + if (VARATT_IS_EXTERNAL(toast_values[i])) > + continue; > + if (att[i]->attstorage != 'f') > + continue; > + > + /* > + * Store this external > + */ > + old_value = toast_values[i]; > + toast_action[i] = 'p'; > + toast_values[i] = toast_save_datum(rel, toast_values[i]); > + if (toast_free[i]) > + pfree(DatumGetPointer(old_value)); > + > + toast_free[i] = true; > + toast_sizes[i] = VARATT_SIZE(toast_values[i]); > + > + need_change = true; > + need_free = true; > + } > + } > +// Add by junji to here > + > /* ---------- > * Compress and/or save external until data fits into target length > * > diff -purN postgresql-8.1.0.org/src/backend/commands/tablecmds.c postgresql-8.1.0/src/backend/commands/tablecmds.c > --- postgresql-8.1.0.org/src/backend/commands/tablecmds.c 2005-10-15 11:49:15.000000000 +0900 > +++ postgresql-8.1.0/src/backend/commands/tablecmds.c 2005-12-01 15:29:29.726577573 +0900 > @@ -3439,6 +3439,10 @@ ATExecSetStorage(Relation rel, const cha > newstorage = 'x'; > else if (pg_strcasecmp(storagemode, "main") == 0) > newstorage = 'm'; > +// Add by junji from here > + else if (pg_strcasecmp(storagemode, "forceexternal") == 0) > + newstorage = 'f'; > +// Add by junji to here > else > { > ereport(ERROR, > @@ -6045,8 +6049,14 @@ needs_toast_table(Relation rel) > tupdesc = rel->rd_att; > att = tupdesc->attrs; > > + > + > for (i = 0; i < tupdesc->natts; i++) > { > +// Add by junji from here > + if (att[i]->attstorage == 'f') > + return true; > +// Add by junji to here > if (att[i]->attisdropped) > continue; > data_length = att_align(data_length, att[i]->attalign); > diff -purN postgresql-8.1.0.org/src/include/access/heapam.h postgresql-8.1.0/src/include/access/heapam.h > --- postgresql-8.1.0.org/src/include/access/heapam.h 2005-10-15 11:49:42.000000000 +0900 > +++ postgresql-8.1.0/src/include/access/heapam.h 2005-12-01 15:29:29.726577573 +0900 > @@ -155,6 +155,10 @@ extern void heap_get_latest_tid(Relation > ItemPointer tid); > extern void setLastTid(const ItemPointer tid); > > +// Add by junji from here > +extern bool has_rel_forceexternal(Relation relation); > +// Add by junji to here > + > extern Oid heap_insert(Relation relation, HeapTuple tup, CommandId cid, > bool use_wal, bool use_fsm); > extern HTSU_Result heap_delete(Relation relation, ItemPointer tid, > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
pgsql-hackers by date: