Re: R: R: space taken by a row & compressed data

On 8/26/2004 4:13 PM, Greg Stark wrote:

> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>
>> "Leonardo Francalanci" <lfrancalanci(at)simtel(dot)ie> writes:
>> > Could you point me to some docs on this TOAST-mechanism?
>> > For instance: what kind of method is used to compress data?
>>
>> When in doubt, read the code ;-)
>> src/backend/utils/adt/pg_lzcompress.c
>> src/include/utils/pg_lzcompress.h
>
> Are toasted values stored in the table itself or in a separate table?

Some here and some there. Toast tries by default to get a row under 2K
size. As long as that isn't the case, it compresses the largest varlen
attribute. If there are no more uncompressed varlen attributes, it takes
the largest value and stores it in slices in the toast table, again only
until the main row is under 2K.

>
> My understanding was that it was the latter, which leads me to wonder whether
> he'll actually gain anything by having all the records in his table be
> toasted. It'll mean every record lookup has to traverse two indexes, and a
> sequential scan loses the sequential read performance boost.
>
> Or am I wrong and toasted values can be stored inline?
>

Depends on the type of query. Queries that really access a lot of
toasted values lose. Queries that shovel around a lot of rows but don't
touch most of the toasted values win.

Jan

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