Re: [HACKERS] TOASTed size
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TOASTed size
Simon Riggs [EMAIL PROTECTED] writes: On Wed, 2007-12-05 at 08:24 +, Gregory Stark wrote: Tom Lane [EMAIL PROTECTED] writes: Simon Riggs [EMAIL PROTECTED] 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's PostgreSQL training! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] TOASTed size
Tom Lane [EMAIL PROTECTED] writes: Simon Riggs [EMAIL PROTECTED] 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! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] TOASTed size
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's PostgreSQL training! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] TOASTed size
On Wed, 2007-12-05 at 08:24 +, Gregory Stark wrote: Tom Lane [EMAIL PROTECTED] writes: Simon Riggs [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] TOASTed size
Simon Riggs [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
