"Bruce Momjian" <bruce@momjian.us> writes:
> Then, figure out where the gains on the non-TEXT field seem to diminish
> in usefulness. Basically, with a lower TOAST value, we are going to
> spend more time accessing the TEXT field, but the speedup for the
> non-TEXT field should be large enough win that we don't care. As the
> TEXT column becomes shorter, it has less affect on the non-TEXT access.
I guess the key is to break down what it is we want to measure into several
parts. These can each be measured precisely for various sized TOASTed data.
Costs:
1) cost of retrieving data from TOAST pointer versus retrieving data from inline tuple. We just want the absolute time
differencebetween the two operations, not the percentage difference.
2) cost of creating TOAST pointer (ie, inserting a new tuple with a TOAST pointer or updating a previously inlined
tupleto have a TOASTed column).
3) cost of deleting a TOAST pointer (ie, deleting a tuple or updating a tuple to no longer have a TOASTed column)
3) cost of deleting a tuple with an existing TOAST pointer (or updating a tuple to be all inlined) versus deleting an
plaintuple or updating a plain tuple.
Savings:
1) time savings accessing a tuple without retrieving the TOAST pointer versus having to access the tuple with the data
inlined.
2) time savings updating a tuple without modifying toasted data versus updating same tuple with the data inlined in
bothversions.
The plan you described would be testing costs 1 and savings 1 but I think we
need to continue to the others as well.
Then the trick is to somehow make some argument about the frequency of the
various operations and the acceptable tradeoff. I think you're right that the
time spent accessing the data would be the most important metric.
-- Gregory Stark EnterpriseDB http://www.enterprisedb.com