"Bruce Momjian" <[EMAIL PROTECTED]> 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.
1) cost of retrieving data from TOAST pointer versus retrieving data from
inline tuple. We just want the absolute time difference between 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 tuple to 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 plain tuple or updating a plain
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 both versions.
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.
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend