From: Tom Lane <tgl@sss.pgh.pa.us>
To: ksimpson@mailchannels.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Comparing two slices within one table efficiently
Date: Mon, 13 Aug 2007 15:37:40 -0400
Message-ID: <20123.1187033860@sss.pgh.pa.us> (raw)
In-Reply-To: <1694202581-1187027999-cardhu_decombobulator_blackberry.rim.net-401169594-@bxe119.bisx.prod.on.blackberry>
References: <1694202581-1187027999-cardhu_decombobulator_blackberry.rim.net-401169594-@bxe119.bisx.prod.on.blackberry>
"=?utf-8?B?S2VuIFNpbXBzb24=?=" <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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, ksimpson@mailchannels.com
Subject: Re: Comparing two slices within one table efficiently
In-Reply-To: <20123.1187033860@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox