Skip site navigation (1) Skip section navigation (2)

cleanup on pg_ system tables?

From: mark <dvlhntr(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: cleanup on pg_ system tables?
Date: 2010-09-20 17:25:45
Message-ID: AANLkTimoa8EHDOZWHuGD4X4RrwJ-0DiSKG=2FE2OZPOH@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi All,

(pg 8.3.7 on RHEL  2.6.18-92.el5 )

I ran the query below (copied from
http://pgsql.tapoueh.org/site/html/news/20080131.bloat.html ) on a
production DB we have and I am looking at some pretty nasty looking
numbers for tables in the pg_catalog schema. I have tried a reindex
and vaccum but neither seem to be clearing these out, tried a cluster
and it won't let me.

I am viewing the problem wrong? is there anything I can do while the
DB is online ? do I need to clean up other things first ?


thanks,

..: Mark



-[ RECORD 1 ]+--------------------------------
schemaname   | pg_catalog
tablename    | pg_attribute
reltuples    | 5669
relpages     | 113529
otta         | 92
tbloat       | 1234.0
wastedpages  | 113437
wastedbytes  | 929275904
wastedsize   | 886 MB
iname        | pg_attribute_relid_attnam_index
ituples      | 5669
ipages       | 68
iotta        | 80
ibloat       | 0.9
wastedipages | 0
wastedibytes | 0
wastedisize  | 0 bytes




SELECT
       schemaname, tablename, reltuples::bigint, relpages::bigint, otta,
       ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric
END,1) AS tbloat,
       relpages::bigint - otta AS wastedpages,
       bs*(sml.relpages-otta)::bigint AS wastedbytes,
       pg_size_pretty((bs*(relpages-otta))::bigint) AS wastedsize,
       iname, ituples::bigint, ipages::bigint, iotta,
       ROUND(CASE WHEN iotta=0 OR ipages=0 THEN 0.0 ELSE
ipages/iotta::numeric END,1) AS ibloat,
       CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END
AS wastedipages,
       CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS
wastedibytes,
       CASE WHEN ipages < iotta THEN pg_size_pretty(0) ELSE
pg_size_pretty((bs*(ipages-iotta))::bigint) END AS wastedisize
     FROM (
       SELECT
         schemaname, tablename, cc.reltuples, cc.relpages, bs,
         CEIL((cc.reltuples*((datahdr+ma-
           (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma
END))+nullhdr2+4))/(bs-20::float)) AS otta,
         COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0)
AS ituples, COALESCE(c2.relpages,0) AS ipages,
         COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0)
AS iotta -- very rough approximation, assumes all cols
       FROM (
         SELECT
           ma,bs,schemaname,tablename,
           (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma
END)))::numeric AS datahdr,
           (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma
ELSE nullhdr%ma END))) AS nullhdr2
         FROM (
           SELECT
             schemaname, tablename, hdr, ma, bs,
             SUM((1-null_frac)*avg_width) AS datawidth,
             MAX(null_frac) AS maxfracsum,
             hdr+(
               SELECT 1+count(*)/8
               FROM pg_stats s2
               WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND
s2.tablename = s.tablename
             ) AS nullhdr
           FROM pg_stats s, (
             SELECT
               (SELECT current_setting('block_size')::numeric) AS bs,
               CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN
27 ELSE 23 END AS hdr,
               CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma
             FROM (SELECT version() AS v) AS foo
           ) AS constants
           GROUP BY 1,2,3,4,5
         ) AS foo
       ) AS rs
       JOIN pg_class cc ON cc.relname = rs.tablename
       JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname
= rs.schemaname
       LEFT JOIN pg_index i ON indrelid = cc.oid
       LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
     ) AS sml
     WHERE sml.relpages - otta > 0 OR ipages - iotta > 10
     ORDER BY wastedbytes DESC, wastedibytes DESC

Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2010-09-20 18:47:56
Subject: Need PostgreSQL data warehousing user, on the record
Previous:From: Daniele VarrazzoDate: 2010-09-20 11:59:09
Subject: Performance degradation, index bloat and planner estimates

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group