Thread

1. Toasting more system-table columns

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-08-03T17:48:57Z

   I find the following varlena (ie, potentially toastable) columns
   present in the system tables:
   
       relname     |   attname    | typname  | attstorage
   ----------------+--------------+----------+------------
    pg_aggregate   | agginitval   | text     | x
    pg_attrdef     | adbin        | text     | p
    pg_attrdef     | adsrc        | text     | p
    pg_class       | relacl       | _aclitem | m
    pg_database    | datpath      | text     | p
    pg_description | description  | text     | x
    pg_group       | grolist      | _int4    | p
    pg_index       | indpred      | text     | x
    pg_language    | lancompiler  | text     | x
    pg_proc        | probin       | bytea    | p
    pg_proc        | prosrc       | text     | p
    pg_relcheck    | rcbin        | text     | p
    pg_relcheck    | rcsrc        | text     | p
    pg_rewrite     | ev_action    | text     | x
    pg_rewrite     | ev_qual      | text     | x
    pg_shadow      | passwd       | text     | p
    pg_statistic   | stacommonval | text     | x
    pg_statistic   | stahival     | text     | x
    pg_statistic   | staloval     | text     | x
    pg_trigger     | tgargs       | bytea    | p
    pg_type        | typdefault   | text     | p
   
   Of these tables, only pg_rewrite has a toast table so far.
   
   Offhand it would seem to be a good idea to create toast tables for
   pg_attrdef, pg_description, pg_proc, pg_relcheck, possibly pg_statistic.
   Certainly a toast table for pg_proc is a must so that we can deal with
   large procedure bodies.  Toasting pg_attrdef would allow very large
   column default expressions (dubious value), pg_description for long
   comments (probably should have this), pg_relcheck for long constraint
   expressions (probably want this), pg_statistic to cope with long
   min/max/common values (not sure about this).
   
   I doubt the other system tables need toast tables, although we may as
   well mark all of these attributes "m", ie, they should be compressible
   in-line even if there is no toast table.
   
   Comments?
   
   			regards, tom lane
   
   

2. Re: Toasting more system-table columns

   Mark Hollomon <mhh@nortelnetworks.com> — 2000-08-03T18:38:01Z

   Tom Lane wrote:
   > Offhand it would seem to be a good idea to create toast tables for
   > pg_attrdef, pg_description, pg_proc, pg_relcheck, possibly pg_statistic.
   > Certainly a toast table for pg_proc is a must so that we can deal with
   > large procedure bodies.  Toasting pg_attrdef would allow very large
   > column default expressions (dubious value), pg_description for long
   > comments (probably should have this), pg_relcheck for long constraint
   > expressions (probably want this), pg_statistic to cope with long
   > min/max/common values (not sure about this).
   
   I would think pg_statistic would be a 'must'.
   
   
   -- 
   
   Mark Hollomon
   mhh@nortelnetworks.com
   ESN 451-9008 (302)454-9008
   
   

3. Re: Toasting more system-table columns

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-08-03T19:57:47Z

   "Mark Hollomon" <mhh@nortelnetworks.com> writes:
   > Tom Lane wrote:
   >> pg_statistic to cope with long
   >> min/max/common values (not sure about this).
   
   > I would think pg_statistic would be a 'must'.
   
   Well, it's not a "must" because the code copes fine now (there's a test
   in there that simply drops pg_statistic tuples that won't fit).
   
   Question is whether we need good selectivity estimation on columns where
   the min/max/common values exceed 8K total (when represented in text form).
   Or for that matter whether the estimates we derive from such values are
   really useful ...
   
   			regards, tom lane
   
   

4. Re: Toasting more system-table columns

   Mark Hollomon <mhh@nortelnetworks.com> — 2000-08-03T20:23:56Z

   Tom Lane wrote:
   > 
   > "Mark Hollomon" <mhh@nortelnetworks.com> writes:
   > > Tom Lane wrote:
   > >> pg_statistic to cope with long
   > >> min/max/common values (not sure about this).
   > 
   > > I would think pg_statistic would be a 'must'.
   > 
   > Well, it's not a "must" because the code copes fine now (there's a test
   > in there that simply drops pg_statistic tuples that won't fit).
   
   Oh, well of course. Even pre-toast len(min)+len(max)+len(common) > 8K
   was possible.
   
   
   -- 
   
   Mark Hollomon
   mhh@nortelnetworks.com
   ESN 451-9008 (302)454-9008