Обсуждение: Toasting more system-table columns

I find the following varlena (ie, potentially toastable) columns
present in the system tables:
   relname     |   attname    | typname  | attstorage
----------------+--------------+----------+------------pg_aggregate   | agginitval   | text     | xpg_attrdef     |
adbin       | text     | ppg_attrdef     | adsrc        | text     | ppg_class       | relacl       | _aclitem |
mpg_database   | datpath      | text     | ppg_description | description  | text     | xpg_group       | grolist      |
_int4   | ppg_index       | indpred      | text     | xpg_language    | lancompiler  | text     | xpg_proc        |
probin      | bytea    | ppg_proc        | prosrc       | text     | ppg_relcheck    | rcbin        | text     |
ppg_relcheck   | rcsrc        | text     | ppg_rewrite     | ev_action    | text     | xpg_rewrite     | ev_qual      |
text    | xpg_shadow      | passwd       | text     | ppg_statistic   | stacommonval | text     | xpg_statistic   |
stahival    | text     | xpg_statistic   | staloval     | text     | xpg_trigger     | tgargs       | bytea    |
ppg_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

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

"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

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