From: | Joseph S <jks(at)selectacast(dot)net> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: reindexing pg_shdepend |
Date: | 2007-08-03 01:44:17 |
Message-ID: | 46B28871.9070303@selectacast.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)selectacast(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Joseph S | 2007-08-03 01:49:02 | Re: == PostgreSQL Weekly News - July 29 2007 == |
Previous Message | Tom Lane | 2007-08-03 01:07:21 | Re: reindexing pg_shdepend |