
I am looking into some performance issues with an application I have.  I 
want to do some testing to see how much overhead TOAST adds to my 
application.  I have a table that performs a similar function to the 
pg_largeobject table.  I have noticed that pg_largeobject doesn't have 
toast enabled (i.e. reltoastrelid is 0).  However when I create my table 
it always gets a value for reltoastrelid.  Since pg_largeobject is 
created without toast, I am assuming this is intentional and that for 
certain classes of tables it may make sense not to toast the tuples. 
Which makes sense because inserting into the toast table will involve 
extra disk IOs and if the tuple would have fit into the base table these 
extra IOs could be avoided.

So how do I create a table without toast enabled?  I have looked through 
the docs for 'create table' and didn't see anything that indicates this 
is possible.  Is there some undocumented syntax?

thanks,
--Barry



Tue, 27 Nov 2001 12:25:04 -0800
Barry Lind <barry@xythos.com>
How to turn off TOAST on a table/column
Barry Lind <barry@xythos.com> writes:
> So how do I create a table without toast enabled?

Unless you want to muck with the backend code, the only way to create
a table that has no toast table attached is to declare columns that
the backend can prove to itself will never add up to more than BLCKSZ
space per tuple.  For example, use varchar(n) not text.  (If you've got
MULTIBYTE enabled then that doesn't work either, since the n is
measure in characters not bytes.)

However, the mere existence of a toast table doesn't cost anything
(except for some increase of the time for CREATE TABLE).  What you
probably really want to do is turn on and off the *use* of the toast
table.  Which you can do by mucking with the attstorage attributes of
the table columns.  I don't think anyone's gotten round to providing
a nice clean ALTER TABLE interface, but a quick

UPDATE pg_attribute SET attstorage = 'p'
WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable')
AND attnum > 0

would suffice to disable toasting of all columns in 'mytable'.

See src/include/pg_attribute.h for documentation of the allowed values
for attstorage.

			regards, tom lane

Tue, 27 Nov 2001 15:52:27 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] How to turn off TOAST on a table/column 
In article <3C03F6A0.1010702@xythos.com>, "Barry Lind" <barry@xythos.com>
wrote:

> So how do I create a table without toast enabled?  I have looked through
> the docs for 'create table' and didn't see anything that indicates this
> is possible.  Is there some undocumented syntax?
>

One of the additions in my "TOAST slicing" patch is the provision of
ALTER TABLE x ALTER COLUMN y SET STORAGE {EXTERNAL | PLAIN | EXTENDED |
MAIN} -but, in short, it sets attstorage (in pg_attribute) for the
appropriate column, which has the effect of forcing certain TOAST
behaviour. 

For the moment, you could just set attstorage to 'e' which will turn off
compression (but still use the second table), or 'm' which will turn off the use
of the external table for that attribute (or 'p' which will do both). Note that
fixed-length types must have attstorage 'p' (by fixed length I mean things
like integer etc. -- varchar(n) is a variable-length type.)

Obviously all the usual caveats about altering system catalogs apply...

Regards

John
-- 
John Gray
Azuli IT    http://www.azuli.co.uk      +44 121 693 3397
jgray@azuli.co.uk

Tue, 27 Nov 2001 21:14:41 +0000
"John Gray" <jgray@azuli.co.uk>
Re: How to turn off TOAST on a table/column
On Tue, Nov 27, 2001 at 03:52:27PM -0500, Tom Lane wrote:
> Barry Lind <barry@xythos.com> writes:
> > So how do I create a table without toast enabled?
> 
> Unless you want to muck with the backend code, the only way to create
> a table that has no toast table attached is to declare columns that
> the backend can prove to itself will never add up to more than BLCKSZ
> space per tuple.  For example, use varchar(n) not text.  (If you've got
> MULTIBYTE enabled then that doesn't work either, since the n is
> measure in characters not bytes.)
> 
> However, the mere existence of a toast table doesn't cost anything
> (except for some increase of the time for CREATE TABLE).  What you
> probably really want to do is turn on and off the *use* of the toast
> table.  Which you can do by mucking with the attstorage attributes of
> the table columns.  I don't think anyone's gotten round to providing
> a nice clean ALTER TABLE interface, but a quick
> 
> UPDATE pg_attribute SET attstorage = 'p'
> WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable')
> AND attnum > 0
> 
> would suffice to disable toasting of all columns in 'mytable'.

This would reimpose the max-tuple limit on that table, would it not?
So trying to store 'too large' a text would error? Definitely one for
the regression tests, once we've got that ALTER TABLE interface.

> 
> See src/include/pg_attribute.h for documentation of the allowed values
> for attstorage.
 
This needs to get into the admin docs. I suppose it's also waiting on the
ALTER TABLE interface.

Ross


Tue, 27 Nov 2001 16:02:00 -0600
"Ross J. Reedstrom" <reedstrm@rice.edu>
Re: [HACKERS] How to turn off TOAST on a table/column
"Ross J. Reedstrom" <reedstrm@rice.edu> writes:
>> would suffice to disable toasting of all columns in 'mytable'.

> This would reimpose the max-tuple limit on that table, would it not?
> So trying to store 'too large' a text would error?

Right.  Presumably, that's what Barry wants to test.  In practice the
other values are more likely to be useful (for toastable datatypes
that is).

>> See src/include/pg_attribute.h for documentation of the allowed values
>> for attstorage.
 
> This needs to get into the admin docs. I suppose it's also waiting on the
> ALTER TABLE interface.

Yeah.  Right now it's too easy to shoot yourself in the foot (for
example, you mustn't set attstorage to anything but 'p' for a
non-varlena datatype).  So we haven't wanted to document the
UPDATE-pg_attribute approach.

			regards, tom lane

Tue, 27 Nov 2001 17:08:32 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] How to turn off TOAST on a table/column 
Ross J. Reedstrom wrote:
> On Tue, Nov 27, 2001 at 03:52:27PM -0500, Tom Lane wrote:
> > Barry Lind <barry@xythos.com> writes:
> > > So how do I create a table without toast enabled?
> >
> > UPDATE pg_attribute SET attstorage = 'p'
> > WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable')
> > AND attnum > 0
> >
> > would suffice to disable toasting of all columns in 'mytable'.
>
> This would reimpose the max-tuple limit on that table, would it not?
> So trying to store 'too large' a text would error? Definitely one for
> the regression tests, once we've got that ALTER TABLE interface.

    Yes, it would.

>
> >
> > See src/include/pg_attribute.h for documentation of the allowed values
> > for attstorage.
>
> This needs to get into the admin docs. I suppose it's also waiting on the
> ALTER TABLE interface.

    One  thing  I'd  like to add is that people should not be too
    surprised  if  turning  off  toast  will  slow   down   their
    application.

    One nice side effect of toast is, that often especially those
    fields you don't use in the where  clause  get  toasted.  Now
    while  a  query is executed and the tuples travel through the
    system, from the heap through the  filters,  in  and  out  of
    sort,  getting  merged  and  joined,  and  some of them later
    thrown away, you don't need  these  attributes.  If  toasted,
    more  tuples  with  the  key  fields  fit into the blocks, so
    you'll get better cache hit rates and  lesser  disk  IO.  The
    sort  sets  will  be  alot  smaller,  more  sorts can be done
    completely in memory without temp files. The huge  attributes
    will only be pulled if the client wanted them and that at the
    time the result is sent to the client,  by  the  type  output
    function.   And  if  you  update  a  row  and don't touch the
    toasted attribute, the value get's never read from the  disk,
    nor does it get updated.

    Just  to  give a few reasons why I like toast. One day I will
    implement a real BLOB datatype - but probably name it poptart
    :-)


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Wed, 5 Dec 2001 17:38:06 -0500 (EST)
Jan Wieck <janwieck@yahoo.com>
Re: [HACKERS] How to turn off TOAST on a table/column