Re: TOAST usage setting

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