Thread

1. Raw size

   Ioannis Theoharis <theohari@ics.forth.gr> — 2005-03-10T18:07:13Z

   
   Hi,
   
   i have a table:
   
   create table triples(
   	att0 varchar(1000),
   	att1 int4,
   	att2 varchar(20),
   	att3 varchar(1000)
   )
   
   My table has 990 raws.
   
   The (possibly wrong) way, with wich i compute the size of the table is:
   att0: 1000 * 1 Byte + 4 = 1004 Bytes
   att2: 20 * 1 Byte + 4 = 24 Bytes
   att3: 1000 * 1 Byte + 4 = 1004
   
   2032 Bytes + 40 (for oid) = 2072 Bytes
   
   990 * 2072 = 2,051,280 Bytes
   
   BUT after clustering triples according to an index on att1:
   
   
   
   select relname, relpages from pg_class ;
                relname             | relpages
   ---------------------------------+----------
    triples                         |      142  (8KB/buffer)
   
   142 * 8 * 1024 = 1,163,264 Bytes
   
   
   Is there any compression or what?
   
   
   

2. Re: Raw size

   Jaime Casanova <systemguards@gmail.com> — 2005-03-10T21:28:06Z

   On Thu, 10 Mar 2005 20:07:13 +0200 (EET), Ioannis Theoharis
   <theohari@ics.forth.gr> wrote:
   > 
   > 
   > Hi,
   > 
   > i have a table:
   > 
   > create table triples(
   > 	att0 varchar(1000),
   > 	att1 int4,
   > 	att2 varchar(20),
   > 	att3 varchar(1000)
   > )
   > 
   > My table has 990 raws.
   > 
   > The (possibly wrong) way, with wich i compute the size of the table is:
   > att0: 1000 * 1 Byte + 4 = 1004 Bytes
   >
   i don't know what the varchar size is in byte but i think is not 1 per
   character. IIRC, it varies on diferent encodings.
   
   regards,
   Jaime Casanova
   
   

3. Re: Raw size

   Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2005-03-11T01:27:03Z

   > BUT after clustering triples according to an index on att1:
   > 
   > 
   > 
   > select relname, relpages from pg_class ;
   >              relname             | relpages
   > ---------------------------------+----------
   >  triples                         |      142  (8KB/buffer)
   > 
   > 142 * 8 * 1024 = 1,163,264 Bytes
   > 
   > 
   > Is there any compression or what?
   
   Yes, there is:
   
   http://www.postgresql.org/docs/8.0/interactive/storage-toast.html
   
   Chris
   
   

4. Re: Raw size

   Ioannis Theoharis <theohari@ics.forth.gr> — 2005-03-11T13:23:27Z

   
   > >
   > > Is there any compression or what?
   >
   > Yes, there is:
   >
   > http://www.postgresql.org/docs/8.0/interactive/storage-toast.html
   
   thanks, is there any way to increase the limit, upper wich toast strategy
   is selected? By defaullt is Block_size/4 = about 2000 Bytes.
   
   
   
   

5. Re: Raw size

   Christopher Kings-Lynne <chriskl@familyhealth.com.au> — 2005-03-12T02:30:57Z

   >>> Is there any compression or what?
   >>
   >> Yes, there is:
   >>
   >> http://www.postgresql.org/docs/8.0/interactive/storage-toast.html
   >
   > thanks, is there any way to increase the limit, upper wich toast strategy
   > is selected? By defaullt is Block_size/4 = about 2000 Bytes.
   
   Dunno, but you can alter the column and go 'set storage' to control the 
   storage strategy for TOAST on the column...
   
   Chris
   
   
   

6. Re: Raw size

   Hannu Krosing <hannu@tm.ee> — 2005-03-14T07:01:09Z

   Ühel kenal päeval (neljapäev, 10. märts 2005, 20:07+0200), kirjutas
   Ioannis Theoharis:
   > 
   > Hi,
   > 
   > i have a table:
   > 
   > create table triples(
   > 	att0 varchar(1000),
   > 	att1 int4,
   > 	att2 varchar(20),
   > 	att3 varchar(1000)
   > )
   > 
   > My table has 990 raws.
   > 
   > The (possibly wrong) way, with wich i compute the size of the table is:
   
   Varchar fields (actually most *char and text fields) use only actual
   length bytes + some overhead for tuple header + page header, so unless
   you fill all varchar(1000) fields with exactly 1000-byte strings, you
   should use less than that.
   
   
   > Is there any compression or what?
   
   Compression is not used for tuples under 2k, so there _may_ be
   coimpression depending on your exact data and TOAST settings.
   
   > 
   > ---------------------------(end of broadcast)---------------------------
   > TIP 9: the planner will ignore your desire to choose an index scan if your
   >       joining column's datatypes do not match
   -- 
   Hannu Krosing <hannu@tm.ee>