Обсуждение: 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