Vertical Partitioning with TOAST - Mailing list pgsql-hackers

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,