Re: TOAST usage setting - Mailing list pgsql-hackers

From Heikki Linnakangas
Subject Re: TOAST usage setting
Date
Msg-id 465DB731.9020106@enterprisedb.com
Whole thread Raw
In response to Re: TOAST usage setting  (Bruce Momjian <bruce@momjian.us>)
List pgsql-hackers
Bruce Momjian wrote:
> What you will see is that pushing TEXT to a TOAST column allows quick
> access to non-TOAST values and single-row TOAST values, but accessing
> all TOAST columns is slower than accessing them in the heap, by a factor
> of 3-18x.
> 
> Looking at the chart, it seems 512 is the proper breakpoint for TOAST
> because 512 gives us a 2x change in accessing non-TOAST values and
> single-row TOAST values, and it is only 2x slower to access all TOAST
> values than we have now.

I don't understand why that point is any better than any other point.

> Of course, this has all the data in the cache, but if the cache is
> limited, pushing more to TOAST is going to be a bigger win.

... assuming that you don't access the TOASTed data.

>  In general,
> I would guess that the number of times all >512 byte rows are accessed
> is much less than the number of times that pushing those >512 byte
> values to TOAST will give a speedup.

Maybe. Maybe not. I wonder how the access times for a single row change 
when you have a table that doesn't fit in cache. Especially after some 
random updates, so that the toast is not clustered in the same order as 
the heap.

The bottom line is that the optimal threshold depends on your application.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com


pgsql-hackers by date:

Previous
From: Brian Hurt
Date:
Subject: Re: Ye olde drop-the-database-you-just-left problem
Next
From: Tom Lane
Date:
Subject: Re: Ye olde drop-the-database-you-just-left problem