"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. Costs: 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 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 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. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
