I have a table with the following simplified form: create table t ( run_id integer, domain_id integer, mta_id integer, attribute1 integer, attribute2 integer, unique(run_id, domain_id, mta_id) );
The table has about 1 million rows with run_id=1, another 1 million rows with run_id=2, and so on. I need to efficiently query the differences between "runs" - i.e. For each (domain_id, mta_id) tuple in run 1, is there a coresponding tuple in run 2 where either attribute1 or attribute2 have changed? The only way I have been able to think of doing this so far is an o(n^2) search, which even with indexes takes a long time. e.g. select * from t t1 where exists (select 1 from t t2 where t2.mta_id=t1.mta_id and t2.domain_id=t1.domain_id and (t2.attribute1 != t1.attribute1 or t2.attribute2 != t1.attribute2) This query takes millenia... Any help would be greatly appreciated. I hope I am naively missing some obvious alternative strategy, since this sort of operation must be common in databases. Thanks, Ken -- Ken Simpson, CEO MailChannels Corporation Reliable Email Delivery (tm) http://www.mailchannels.com ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
