I doubt it's much. I'd guess that in most of my email the headers are
1/3 of the total messages bytes.
On Feb 14, 2008, at 11:25 PM, Vladimir Likhachev wrote:
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: dbmail@dbmail.org
> 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! MSN Messenger
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
_______________________________________________
DBmail mailing list
DBmail@dbmail.org
https://mailman.fastxs.nl/mailman/listinfo/dbmail
