Обсуждение: AW: pg_attribute growing and growing and growing

> foo=# \d pg_attribute_relid_attnam_index
> Index "pg_attribute_relid_attnam_index"
>  Attribute | Type
> -----------+------
>  attrelid  | oid
>  attname   | name
> unique btree
> 
> foo=# \d pg_attribute_relid_attnum_index
> Index "pg_attribute_relid_attnum_index"
>  Attribute |   Type
> -----------+----------
>  attrelid  | oid
>  attnum    | smallint
> unique btree
> 
> Since table OIDs keep increasing, this formulation ensures that new
> entries will always sort to the end of the index, and so space freed
> internally in the indexes can never get re-used.  Swapping the column
> order may eliminate that problem --- but I'm not sure what if any
> speed penalty would be incurred.  Thoughts anyone?

Isn't pg_attribute often accessed with a "where oid=xxx" restriction
to get all cols for a given table ?

Andreas

Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:
>> Since table OIDs keep increasing, this formulation ensures that new
>> entries will always sort to the end of the index, and so space freed
>> internally in the indexes can never get re-used.  Swapping the column
>> order may eliminate that problem --- but I'm not sure what if any
>> speed penalty would be incurred.  Thoughts anyone?

> Isn't pg_attribute often accessed with a "where oid=xxx" restriction
> to get all cols for a given table ?

Hmm, good point.  I don't think the system itself does that --- AFAIR
it just looks up specific rows by relid+name or relid+num --- but making
this change would make the indexes useless for applications that make
that kind of query.

Oh well, back to the drawing board...
        regards, tom lane