Re: pg_class (system) table increasing size.

On Thu, Nov 17, 2016 at 1:33 PM, dhaval jaiswal <dhavallj(at)hotmail(dot)com>
wrote:

> select * from pg_stat_sys_tables where relname = 'pg_class';
>
> -[ RECORD 1 ]-------+-----------
> relid | 1259
> schemaname | pg_catalog
> relname | pg_class
> seq_scan | 1838
> seq_tup_read | 3177416
> idx_scan | 1027456557
> idx_tup_fetch | 959682909
> n_tup_ins | 0
> n_tup_upd | 0
> n_tup_del | 0
> n_tup_hot_upd | 0
> n_live_tup | 0
> n_dead_tup | 0
> n_mod_since_analyze | 0
> last_vacuum |
> last_autovacuum |
> last_analyze |
> last_autoanalyze |
> vacuum_count | 0
> autovacuum_count | 0
> analyze_count | 0
> autoanalyze_count | 0
>
>
> Yes, the size of pg_class table is of 5 GB. However, the existing row is
> only 2380 only. It's got fragmented.
>
> ------------------------------
> *From:* Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> *Sent:* Thursday, November 17, 2016 8:29 PM
> *To:* dhaval jaiswal; David G. Johnston
> *Cc:* pgsql-general(at)postgresql(dot)org
> *Subject:* Re: [GENERAL] pg_class (system) table increasing size.
>
> On 11/16/2016 07:08 PM, dhaval jaiswal wrote:
> >
> >>> Because you are creating (specific) objects.
> >
> > I have gone through the link and how would i figure out which
> > specific object is causing this. Can you please elaborate more here.
> >
> >
> > We do not have the much temporary table usage.
> >
> >
> > Since the size is bigger (5 GB) to maintain. does it requires
> > maintenance as well for thepg_class.
>
> Should have added to my previous post. What does:
>
> select * from pg_stat_sys_tables where relname = 'pg_class';
>
> show?
>
> >
> >
> > It seems its affecting performance.
> >
> >
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

*>Yes, the size of pg_class table is of 5 GB. However, the existing row is
only 2380 only. It's got fragmented. I strongly believe you are incorrect
about the size of the pg_class table.The correct way to determine that size
is:SELECT n.nspname as schema, c.relname as table, a.rolname as
owner, c.relfilenode as filename, c.reltuples::bigint,
pg_size_pretty(pg_relation_size(n.nspname|| '.' || c.relname)) as
size, pg_size_pretty(pg_total_relation_size(n.nspname|| '.' ||
c.relname)) as total_size, pg_relation_size(n.nspname|| '.' ||
c.relname) as size_bytes, pg_total_relation_size(n.nspname|| '.' ||
c.relname) as total_size_bytes, CASE WHEN c.reltablespace =
0 THEN 'pg_default' ELSE (SELECT t.spcname
FROM pg_tablespace t WHERE (t.oid = c.reltablespace)
) END as tablespaceFROM pg_class c JOIN
pg_namespace n ON (n.oid = c.relnamespace) JOIN pg_authid a ON ( a.oid =
c.relowner ) WHERE relname = 'pg_class' ;*

* What does that show for reltuples and total_size ?*

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.