This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
Subject: Re: [HACKERS] pg_attribute growing and growing and growing From: Tom Lane <tgl@sss.pgh.pa.us> Date: Sat, 19 Aug 2000 01:22:43 -0400 Brian Hirt <bhirt@mobygames.com> writes: > I run a site that get's a fair amount of traffic and we use temporary > table extensively for some more complex queries (because by breaking > down the queries into steps, we can get better performance than by > letting postgres plan the query poorly) I assume that creating a > temporary table and then dropping it will cause the pg_attribute table > to grow because our pg_attribute grows by about 15MB per day and if it > isn't vacuumed nightly the system slows down very quickly. After > "vacuum analyze pg_attribute", the pg_attribute table is back to it's > normal small size. However, the two indexes on pg_attribute do not > shrink at all. Indexes in general are not shrunk by vacuum. The only clean solution I see for this is to convert vacuum to do the "drop/rebuild index" business internally --- but AFAICS we can't do that safely without some sort of file versioning solution. See past threads in pghackers. Possibly a better short-term attack is to eliminate the need for so many temp tables. What's your gripe about bad planning, exactly? Another possibility, which just screams HACK but might fix your problem, is to swap the order of the columns in the two indexes on pg_attribute: 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? === Subject: AW: [HACKERS] pg_attribute growing and growing and growing From: Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> Date: Mon, 21 Aug 2000 11:28:03 +0200 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 ? === Subject: Re: AW: [HACKERS] pg_attribute growing and growing and growing From: Tom Lane <tgl@sss.pgh.pa.us> Date: Mon, 21 Aug 2000 10:23:37 -0400 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... ===