Thread: TOASTed size
I'm thinking that there isn't any way currently of working out how big a compressed toast object is? All existing functions decompress the object before we do anything to it, AFAICS. Am I missing something? So there's no way currently of working out how good your compression is for individual values or when you have multiple toasted columns, other than writing a new function? -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
Simon Riggs <simon@2ndquadrant.com> writes:
> I'm thinking that there isn't any way currently of working out how big a
> compressed toast object is?
pg_column_size() ?
regards, tom lane
"Tom Lane" <tgl@sss.pgh.pa.us> writes: > Simon Riggs <simon@2ndquadrant.com> writes: >> I'm thinking that there isn't any way currently of working out how big a >> compressed toast object is? > > pg_column_size() ? I was going to send the same thing but I think he's looking for the compressed size of *external* data. In fact there isn't really any convenient way to find out something is stored external. pg_column_size reports the toast raw size of externally stored data. There does seem to be a need for a more general pg_column_info which returns a tuple (external bool, rawsize integer, storedsize integer). -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!
On Wed, 2007-12-05 at 08:24 +0000, Gregory Stark wrote: > "Tom Lane" <tgl@sss.pgh.pa.us> writes: > > > Simon Riggs <simon@2ndquadrant.com> writes: > >> I'm thinking that there isn't any way currently of working out how big a > >> compressed toast object is? > > > > pg_column_size() ? > > I was going to send the same thing but I think he's looking for the compressed > size of *external* data. > > In fact there isn't really any convenient way to find out something is stored > external. pg_column_size reports the toast raw size of externally stored data. > > There does seem to be a need for a more general pg_column_info which returns a > tuple (external bool, rawsize integer, storedsize integer). That sounds more like what I was after. So let me check my understanding: For TOASTed data pg_column_size() tells you how many bytes the column value occupies when decompressed. So there isn't any way of finding out how many bytes a column value actually occupies when it is both compressed and external? -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com
Simon Riggs wrote: > > That sounds more like what I was after. > > So let me check my understanding: For TOASTed data pg_column_size() > tells you how many bytes the column value occupies when decompressed. So > there isn't any way of finding out how many bytes a column value > actually occupies when it is both compressed and external? > > I dimly recall getting confused by this when writing this guy: From what I can see: pg_column_size calls toast_datum_size for any variable length attribute - and then gets the external pointer and returns its va_extsize component (which looks to me like the *compressed* size.) Cheers Mark
"Simon Riggs" <simon@2ndquadrant.com> writes: > On Wed, 2007-12-05 at 08:24 +0000, Gregory Stark wrote: >> "Tom Lane" <tgl@sss.pgh.pa.us> writes: >> >> > Simon Riggs <simon@2ndquadrant.com> writes: >> >> I'm thinking that there isn't any way currently of working out how big a >> >> compressed toast object is? >> > >> > pg_column_size() ? >> >> I was going to send the same thing but I think he's looking for the compressed >> size of *external* data. >> >> In fact there isn't really any convenient way to find out something is stored >> external. pg_column_size reports the toast raw size of externally stored data. >> >> There does seem to be a need for a more general pg_column_info which returns a >> tuple (external bool, rawsize integer, storedsize integer). > > That sounds more like what I was after. > > So let me check my understanding: For TOASTed data pg_column_size() > tells you how many bytes the column value occupies when decompressed. Wait, no, it's supposed to be the actual size on disk. *checks* yeah, it's the extsize which is the size of the datum in the toast table. So you could find the compression ratio by calling length() and pg_column_size() at least for text data. I still think a single function returning those columns would be a nice thing to have to make the api complete. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB'sPostgreSQL training!
What it turns out is hard to determine is whether the column was stored externally. To do that you have to rely on the trick of checking pg_column_size(table.*) and that only works if it's the only column likely to be stored externally. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB'sPostgreSQL training!