Toasting more system-table columns

Started by Tom Laneover 25 years ago4 messages
#1Tom Lane
tgl@sss.pgh.pa.us

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

#2Mark Hollomon
mhh@nortelnetworks.com
In reply to: Tom Lane (#1)
Re: Toasting more system-table columns

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

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Mark Hollomon (#2)
Re: Toasting more system-table columns

"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

#4Mark Hollomon
mhh@nortelnetworks.com
In reply to: Tom Lane (#1)
Re: Toasting more system-table columns

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