michael.monnerie at is
May 3, 2010, 3:40 AM
Post #5 of 5
(628 views)
Permalink
|
On Samstag, 13. Februar 2010 Paul J Stevens wrote:
> I've blogged about a better approach on blog.dbmail.eu.
There's an error, at least for postgresql 8.4:
CREATE VIEW header_count AS
SELECT count(1) AS count, n.id, n.headername
FROM dbmail_headervalue v
LEFT JOIN dbmail_headername n ON v.headername_id=n.id
GROUP BY n.id;
ERROR: column "n.headername" must appear in the GROUP BY clause or be
used in an aggregate function
just change the last line to
GROUP BY n.id, n.headername;
and it works.
Also, this delete finds zero records:
DELETE FROM dbmail_headername WHERE headername = 'Received';
but this one works (again postgresql 8.4):
DELETE FROM dbmail_headername WHERE headername = 'received';
To make it a simple copy, here are the most often appearing entries
which probably won't be IMAP SEARCHed:
DELETE FROM dbmail_headername WHERE headername = 'received' or
headername = 'x-virus-scanned' or headername like 'x-spam%';
This deleted more than half of our headervalue entries. Afterwards,
running CLUSTER, REINDEX and VACUUM ANALYZE on the two tables is a good
way to gain performance immediately.
For CLUSTER to work, you'd need to have applied the CLUSTER commands
once that I described on http://dbmail.org/dokuwiki/doku.php/performance
I've also put the information from Paul on that page, to keep the
"performance" tips on one page.
--
mit freundlichen Grüssen,
Michael Monnerie, Ing. BSc
it-management Internet Services
http://proteger.at [gesprochen: Prot-e-schee]
Tel: 0660 / 415 65 31
// Wir haben im Moment zwei Häuser zu verkaufen:
// http://zmi.at/langegg/
// http://zmi.at/haus2009/
|
signature.asc
