[SQL] Comparing two slices within one table efficiently

[Ken Simpson]

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

Re: [SQL] Comparing two slices within one table efficiently

[Ken Simpson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Christian Kindler [13/08/07 21:34 +0200]:
> Yes and you could make it even more speedy with the use table partitioning.
> http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html

Thanks for all your speedy help, everyone. I tried doing a "self join"
and that sped things up enormously (query took on the order of 30
seconds to compare two million-row table slices, resulting in a 20K
row result). I will also try re-ordering the unique constraint to get
speedier indexing out of it and will look at table partitioning.

Regards,
Ken

- -- 
Ken Simpson
CEO, MailChannels

Fax: +1 604 677 6320
Web: http://mailchannels.com
MailChannels - Reliable Email Delivery (tm)
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGwLQq2YHPr/ypq5QRApP8AKDfRGqDFkcONh0YaojX7362nXP12gCg3WZ6
k5ZBwcMplXyVkEguQtbgdFU=
=bsyu
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate