Re: Comparing two slices within one table efficiently

"=?utf-8?B?S2VuIFNpbXBzb24=?=" <ksimpson(at)mailchannels(dot)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