>>>>> "Vladimir" == Vladimir Likhachev <[EMAIL PROTECTED]> writes:
Vladimir> Maybe, indexes use a lot of space in dbmail_headervalue?
Vladimir> Will You try to select (tablesizes+toastsizes) and
Vladimir> (indexsizes+toastindexsizes) in 2 distinct querys and
Vladimir> publish results?
Here is what I get from his select, modified as above, and saved as a
view, on a db with 545754 rows in physmessage:
dbmail=# select * from usage;
nspname | relname | totalsize | noindexsize | indexsize
---------+---------------------------+------------+-------------+------------
public | dbmail_headervalue | 4694 MB | 2619 MB | 2076 MB
public | dbmail_messageblks | 2732 MB | 2651 MB | 81 MB
public | dbmail_envelope | 275 MB | 253 MB | 22 MB
public | dbmail_referencesfield | 222 MB | 79 MB | 142 MB
public | dbmail_messages | 208 MB | 83 MB | 125 MB
public | dbmail_tofield | 120 MB | 50 MB | 70 MB
public | dbmail_fromfield | 109 MB | 46 MB | 64 MB
public | dbmail_subjectfield | 107 MB | 48 MB | 59 MB
public | dbmail_replytofield | 62 MB | 26 MB | 36 MB
public | dbmail_datefield | 61 MB | 26 MB | 34 MB
public | dbmail_ccfield | 44 MB | 18 MB | 26 MB
public | dbmail_physmessage | 44 MB | 34 MB | 10032 kB
public | dbmail_mailboxes | 21 MB | 18 MB | 3728 kB
public | dbmail_headername | 1928 kB | 704 kB | 1224 kB
public | dbmail_users | 976 kB | 656 kB | 320 kB
public | dbmail_subscription | 736 kB | 464 kB | 272 kB
public | dbmail_aliases | 56 kB | 8192 bytes | 48 kB
public | dbmail_sievescripts | 32 kB | 0 bytes | 32 kB
public | dbmail_pbsp | 24 kB | 0 bytes | 24 kB
public | dbmail_replycache | 24 kB | 8192 bytes | 16 kB
public | dbmail_auto_replies | 16 kB | 0 bytes | 16 kB
public | dbmail_usermap | 8192 bytes | 0 bytes | 8192 bytes
public | dbmail_acl | 8192 bytes | 0 bytes | 8192 bytes
public | dbmail_auto_notifications | 8192 bytes | 0 bytes | 8192 bytes
(24 rows)
As you can see, the indices for headervalue are almost as large as the data.
And I only use these indices:
"dbmail_headervalue_pkey" PRIMARY KEY, btree (id)
"dbmail_headervalue_1" UNIQUE, btree (physmessage_id, id)
"dbmail_headervalue_2" btree (physmessage_id)
"dbmail_headervalue_4" btree (headername_id)
"dbmail_headervalue_5" btree (physmessage_id, headername_id)
Having the dbmail_headervalue_3 ON
dbmail_headervalue(substring(headervalue,0,255))
index would about double the indexsize.
As discussed earlier, limiting the list of headers saved in headervalue
would have a significant and beneficial impact on performance.
-JimC
--
James Cloos <[EMAIL PROTECTED]> OpenPGP: 1024D/ED7DAEA6
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
