On Sat, Apr 11, 2009 at 05:43:06PM -0400, Tom Lane wrote:
> In principle, every varchar column could get toasted into an 18-byte
> (more in pre-8.3 versions) TOAST pointer, meaning you could get to
> somewhere around 450 columns in 8K regardless of how wide they are.
Wow, never realised it was that much before. Where is all that?
varattrib_1b_e = 3 bytes, but will overlap one byte with:
varatt_external = 16 bytes
Is that right?
> In practice the performance would probably suck too much to be useful
> --- you don't want to be toasting key columns, in particular.
Good point; if you're referring to any reasonable number of these
columns in each query it's going to be somewhat grim.
> So really the question is uselessly vague as posed. We'd need to know a
> lot more about the columns' average widths and usage patterns before we
> could say much about how well it's likely to work.
I guess it's things like lots of NULLs = good that the OP was trying to
find out.
Just out of interest; what would happen if you had:
CREATE TABLE wider (
col0001 TEXT,
col0002 TEXT,
-- [ .... ]
col9998 TEXT,
col9999 TEXT
);
CREATE TABLE store (
pk1 INTEGER,
pk2 TEXT,
PRIMARY KEY (pk1,pk2),
data wider
);
Would the "data" tend to end up toasted, or would PG try and expand the
data inline and fail some of the time? Also, if I ran the following
query:
SELECT pk1, pk2, (data).col0001, (data).col0101 FROM store;
Would "data" get detoasted once per row, or per column referenced?
--
Sam http://samason.me.uk/
