"Ken Simpson" <ksimpson@mailchannels.com> writes:
> 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...
Yeah, because you're effectively forcing the least efficient style of
join --- a nestloop is generally going to suck for a full-table join,
even if you've got indexes. Try something like this:
select * from t t1 join t t2 on (t2.mta_id=t1.mta_id and t2.domain_id=t1.domain_id)
where (t2.attribute1 != t1.attribute1 or t2.attribute2 != t1.attribute2)
Make sure you've got work_mem cranked up to something appropriate.
regards, tom lane