Re: reindexing pg_shdepend - Mailing list pgsql-general

Me too.  I don't change my db schema that much, but I experience bloat
in the pg_tables that I don't expect.  For instance pg_opclass needs a
VACUUM FULL/REINDEX once a week or I notice the indexes are larger than
the table itself.  Could it be my heavy use of temp tables?

Today I noticed that pg_statistic (which I actually expect to be updated
in the normal course of operations) was over 20 meg (with large indexes
as well) so I gave it a VACUUM FULL/REINDEX and it now stands at 344,064.

These tables never get *really* large, so I've never noticed a big
performance hit, but they still get bigger than they should be and could
be slowing everything down a little.

around 3pm today I did a VACUUM FULL/REINDEX of all the trouble tables I
have in my list, and I didn't save the before/after sizes, bit it is
9:30 pm now and I can tell you how much they've grown since then:

BEFORE VACUUM FULL/REINDEX at 9:30:

pg_catalog    pg_class    table    196,608    21.526
pg_catalog    pg_class_oid_index    index    49,152    81.8
pg_catalog    pg_class_relname_nsp_index    index    172,032    21.526


pg_catalog    pg_type    table    180,224    15.045
pg_catalog    pg_type_oid_index    index    40,960    66.2
pg_catalog    pg_type_typname_nsp_index    index    106,496    25.462

AFTER:

pg_catalog    pg_class    table    81,920    41.1
pg_catalog    pg_class_oid_index    index    32,768    102.5
pg_catalog    pg_class_relname_nsp_index    index    57,344    58.714

pg_catalog    pg_type    table    65,536    41.375
pg_catalog    pg_type_oid_index    index    16,384    165.5
pg_catalog    pg_type_typname_nsp_index    index    49,152    55.167

My apologies for the tabs.  That was a cut & paste from a web page I set
up to monitor the database size.  The columns are: schema    relname
Type    bytes    tuplesperpage

The list of tables I have in my list are:
pg_attribute pg_class pg_depend pg_index pg_shdepend pg_proc
pg_statistic pg_type pg_trigger pg_shdepend
I put them in my list bec. I once noticed that their indexes seemed big
relative to the size of the table itself.  I didn't really analyze if
they were indeed recurring problems or just one time problems, but I
know pg_class and pg_opclass are ones where this is a recurring problem.

BTW Tom do you prefer the replies to go to you directly as well as to
the list?  Most of the time I just hit 'Reply' and since this list
doesn't set the Reply-to: the replies go to the OP as well.

Tom Lane wrote:
> Joseph S <jks@selectacast.net> writes:
>> My pg_shdepend table has a size of 16,384, but
>> pg_shdepend_depender_index has a size of 19,169,280 and
>> pg_shdepend_reference_index has a size of 49,152.
>
> I'd be interested to see the usage pattern that made it get like that
> ...
>
>             regards, tom lane