Maybe, indexes use a lot of space in dbmail_headervalue?
Will You try to select
(tablesizes+toastsizes)
and
(indexsizes+toastindexsizes)
in 2 distinct querys and publish results?
> From: [EMAIL PROTECTED]> To: [email protected]> Date: Thu, 14 Feb 2008
> 15:15:49 +0100> Subject: [Dbmail] Size of dbmail DB in postgreSQL> > Dear
> all,> > I just found a nice SELECT which shows the size of the database you
> are > connected to. For my dbmail, it reads this. dbmail_headervalue is about
> > 1/3 of the whole e-mail size, can this be reduced? Is dbmail2.3 saving >
> space only for message parts, or for headervalues also?> > nspname | relname
> | totalsize> ---------+---------------------------+------------> public |
> dbmail_messageblks | 9735 MB> public | dbmail_headervalue | 3195 MB> public |
> dbmail_messages | 241 MB> public | dbmail_envelope | 148 MB> public |
> dbmail_tofield | 102 MB> public | dbmail_fromfield | 76 MB> public |
> dbmail_referencesfield | 74 MB> public | dbmail_subjectfield | 70 MB> public
> | dbmail_datefield | 69 MB> public | dbmail_physmessage | 40 MB> public |
> dbmail_replytofield | 23 MB> public | dbmail_ccfield | 20 MB> public |
> dbmail_users | 4696 kB> public | dbmail_headername | 624 kB> public |
> dbmail_mailboxes | 424 kB> public | dbmail_aliases | 280 kB> public |
> dbmail_sievescripts | 272 kB> public | dbmail_replycache | 184 kB> public |
> dbmail_subscription | 144 kB> > The SELECT reads:> SELECT nspname, relname,>
> pg_size_pretty(tablesize+indexsize+toastsize+toastindexsize) AS > totalsize>
> FROM> (SELECT ns.nspname, cl.relname, pg_relation_size(cl.oid) AS tablesize,>
> COALESCE((SELECT SUM(pg_relation_size(indexrelid))::bigint> FROM pg_index
> WHERE cl.oid=indrelid), 0) AS indexsize,> CASE WHEN reltoastrelid=0 THEN 0>
> ELSE pg_relation_size(reltoastrelid)> END AS toastsize,> CASE WHEN
> reltoastrelid=0 THEN 0> ELSE pg_relation_size((SELECT reltoastidxid FROM
> pg_class ct> WHERE ct.oid = cl.reltoastrelid))> END AS toastindexsize> FROM
> pg_class cl, pg_namespace ns> WHERE cl.relnamespace = ns.oid> AND ns.nspname
> NOT IN ('pg_catalog', 'information_schema')> AND cl.relname IN> (SELECT
> table_name FROM information_schema.tables> WHERE table_type = 'BASE TABLE'))
> ss> ORDER BY tablesize+indexsize+toastsize+toastindexsize DESC;> > -- > //
> Michael Monnerie, Ing.BSc ----- http://it-management.at> // Tel: 0676/846 914
> 666 .network.your.ideas.> // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg
> --import"> // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4>
> // Keyserver: www.keyserver.net Key-ID: 1C1209B4
_________________________________________________________________
Express yourself instantly with MSN Messenger! Download today it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
