Hans Kristian Rosbach wrote:
On Fri, 2004-11-05 at 21:32, Sean Chittenden wrote:
CREATE TABLE headers (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
CREATE UNIQUE INDEX headers_name_udx ON headers(name);
CREATE TABLE header_msg_map (
id SERIAL8 PRIMARY KEY,
physmessage_id INT NOT NULL,
header_id INT NOT NULL,
value TEXT NOT NULL,
hdr_order INT2 NOT NULL,
FOREIGN KEY (header_id) REFERENCES headers(id)
);
CREATE INDEX header_msg_map_idx ON header_msg_map(physmessage_id,
header_id);
Well, I agree that you would save a little space.
But I do not however think that it is worth the extra join and
complexity. The join will increase dbload and tmp-file-usage
and could make the dbserver overloaded at a far lower load.
Space is cheap, even in huge amounts. IO-speed is NOT cheap.
pgsql will also compress varchar fields, and a b-tree index on
it will provide for the searching without decompressing.
On the flip I think it will reduce overall DB load since it would search
the very large header_msg_map table using integer comparison rather than
string. A moderate size DBMail database is likely to have 1M messages
in it which would translate to 20-30M headers so that could be a not
only a significant space savings, but a huge performance win when
searching. Also, space saving translates to IO saving.
