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.htmlALTER 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
