In some of our applications, we have cases where it would be very nice if we could activate TOAST at some sort of lower threshold than the usual 2K that is true now. Let me note the current code that controls the threshold:
/* * These symbols control toaster activation. If a tuple is larger than * TOAST_TUPLE_THRESHOLD, we will try to toast it down to no more than * TOAST_TUPLE_TARGET bytes. Both numbers include all tuple header overhead * and between-fields alignment padding, but we do *not* consider any * end-of-tuple alignment padding; hence the values can be compared directly * to a tuple's t_len field. We choose TOAST_TUPLE_THRESHOLD with the * knowledge that toast-table tuples will be exactly that size, and we'd * like to fit four of them per page with minimal space wastage. * * The numbers need not be the same, though they currently are. * * Note: sizeof(PageHeaderData) includes the first ItemId, but we have * to allow for 3 more, if we want to fit 4 tuples on a page. */ #define TOAST_TUPLE_THRESHOLD^I\ ^IMAXALIGN_DOWN((BLCKSZ - \ ^I^I^I^I MAXALIGN(sizeof(PageHeaderData) + 3 * sizeof(ItemIdData))) \ ^I^I^I^I / 4) We have cases where we're storing XML message information which is near the 0.5K mark, that being the case, tuples virtually never get TOASTed. somesystem=# select min(length(xml)), max(length(xml)), avg(length(xml)), stddev(length(xml)) from table_with_xml; min | max | avg | stddev -----+------+----------------------+------------------ 244 | 2883 | 651.6900720788174376 | 191.602077911138 (1 row) I can see four controls as being pretty plausible: 1. Compile time... #define TOAST_DENOMINATOR 17 /* Use this as the divisor; current default behaviour falls from TOAST_DENOMINATOR = 4 */ #define TOAST_TUPLE_THRESHOLD^I\ ^IMAXALIGN_DOWN((BLCKSZ - \ ^I^I^I^I MAXALIGN(sizeof(PageHeaderData) + 3 * sizeof(ItemIdData))) \ ^I^I^I^I / TOAST_DENOMINATOR) That's obviously cheapest to the DB engine. I just made this change to a checkout of CVS HEAD, and it readily survived a regression test. 2. GUC value for TOAST_DENOMINATOR Do the above, but with the added detail that TOAST_DENOMINATOR refers to a GUC value. I think I could probably make this change; the principle remains much the same as with #1. 3. GUC value for TOAST_TUPLE_THRESHOLD This probably has to get modified to the nearest feasible value, modulo alignment; it's not all that different from #1 or #2. 4. A different mechanism would be to add a fifth storage column strategy (the present four are PLAIN, EXTENDED, EXTERNAL, MAIN), let's say, TOAST. At present, the 4 values are essentially advisory; columns get TOASTed if the column permits EXTENDED storage, but that only occurs if the size is greater than TOAST_TUPLE_THRESHOLD. If the new value was chosen, the column would *always* get stored as TOAST. Presumably #1 or #2 could readily get into 8.3 as they're pretty easy; #3 is a bit trickier, whilst #4 is probably not "8.3-fittable". Question: Which of these sounds preferable? -- let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;; http://cbbrowne.com/info/finances.html Where do you *not* want to go today? "Confutatis maledictis, flammis acribus addictis" (<http://www.hex.net/~cbbrowne/msprobs.html> ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
