Re: Bloated pg_shdepend_depender_index

On 3/23/06, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> Gregory Maxwell wrote:
>
> > I recently noticed that this database has grown to a huge size. ...
> > Which I found to be somewhat odd because none of the tables have more
> > than around 1000 rows.   I hadn't been vacuuming because I didn't
> > think that anything would ever be deleted.... so I performed a vacuum
> > full... but no luck, it was still about 6.4GB.
> >
> > With some help of the folks on IRC I discovered...
> > postgres=#  select relname, pg_relation_size(oid) FROM pg_class ORDER
> > BY 2 DESC LIMIT 2;
> >            relname           | pg_relation_size
> > -----------------------------+------------------
> >  pg_shdepend_depender_index  |        159465472
> >  pg_shdepend_reference_index |         97271808
> > (2 rows)
>
> Huh, that's very strange.
>
> The pg_shdepend table is a shared catalog, unlike most other catalogs.
> Still I don't see why would the indexes not shrink.  Do pg_authid,
> pg_database, or pg_tablespace show a similar behavior?  Is amarok
> creating a lot of temp tables or something?  When you did that vacuum
> full, was there a process connected to the same or another database that
> could be holding onto the pg_shdepend table?
>
> I'll have a look at the code, but I thought I might throw that out, just
> in case it rings a bell.

Those were the only two objects impacted.  I'm not sure what amarok
does.. a quick glance at the source shows that it does use temp
tables, but I'm not sure how frequently.

When I vacuum fulled nothing else was connected.. I just restarted PG
and vacuumed again.. no obvious change of disk size (still.. 6.4
gigs).. but this changed:

amarokcollection=#  select relname, pg_relation_size(oid) FROM
pg_class ORDER BY 2 DESC LIMIT 20;
             relname             | pg_relation_size
---------------------------------+------------------
 pg_attribute_relid_attnam_index |       2881069056
 pg_class_relname_nsp_index      |       1185890304
 pg_attribute_relid_attnum_index |        710565888
 pg_depend_depender_index        |        451190784
 pg_depend_reference_index       |        422363136
 pg_type_typname_nsp_index       |        245293056
 pg_class_oid_index              |        208420864
 pg_shdepend_depender_index      |        159465472
 pg_shdepend_reference_index     |         97271808
 pg_constraint_conname_nsp_index |         55107584
 pg_index_indrelid_index         |         50774016
 pg_index_indexrelid_index       |         50774016
 pg_type_oid_index               |         44400640
 pg_attrdef_adrelid_adnum_index  |         15761408
 pg_constraint_conrelid_index    |         15728640
 pg_constraint_oid_index         |         15728640
 pg_attrdef_oid_index            |         12541952
 pg_constraint_contypid_index    |          7618560
 statistics_url_key              |          2113536
 url_stats                       |          2105344

The old output was:
postgres=#  select relname, pg_relation_size(oid) FROM pg_class ORDER
BY 2 DESC LIMIT 10;              relname             |
pg_relation_size
---------------------------------+------------------
 pg_shdepend_depender_index      |        159465472
 pg_shdepend_reference_index     |         97271808
 pg_proc                         |           352256
 pg_proc_proname_args_nsp_index  |           344064
 pg_depend                       |           237568
 pg_attribute_relid_attnam_index |           204800
 pg_attribute                    |           204800
 pg_depend_reference_index       |           172032
 pg_depend_depender_index        |           155648
 pg_statistic                    |           131072
(10 rows)

I'm at your disposal on this.. it's weird.