On Fri, 2004-11-05 at 21:32, Sean Chittenden wrote: > > Couldn't this be solved by having a headers table like this? > > > > Create table headers( > > hid serial primary key, > > physmessage_id int not null, > > header varchar not null, > > value varchar not null, > > ); > > create index headers_physid_header on headers(physmessage_id,header); > > > > This would let us do the following: > > Select value from headers where physmessage_id='4' AND header='From'; > > Actually, normalize this one step further for a rather large space > savings (and potentially a speed boost): > > 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. -=Dead2=-
