Skip site navigation (1) Skip section navigation (2)

Re: EXISTS optimization

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: pgsql-hackers(at)postgresql(dot)org, pgsql-performance(at)postgresql(dot)org, "Andrea Olson" <Aolson(dot)CCAP(dot)Courts(at)wicourts(dot)gov>, "Bill Severson" <BSEVERS(dot)CCAP(dot)Courts(at)wicourts(dot)gov>, "John Hutchins" <jhutchi(dot)CCAP(dot)Courts(at)wicourts(dot)gov>, "Randy Peterson" <RPETERS(dot)CCAP(dot)Courts(at)wicourts(dot)gov>, "Shannon Spranger" <ssprang(dot)CCAP(dot)Courts(at)wicourts(dot)gov>
Subject: Re: EXISTS optimization
Date: 2007-03-23 23:04:12
Message-ID: 26175.1174691052@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-performance
"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote: 
>> If you want that, try rewriting the EXISTS to an IN:
>> 
>> AND ("H"."tranNo", "H"."countyNo") IN
>> (
>> SELECT "D"."tranNo", "D"."countyNo" FROM "TranDetail" "D"
>> WHERE "D"."caseNo" LIKE '2006TR%'
>> )

> That's the good news.  The bad news is that I operate under a
> management portability dictate which doesn't currently allow that
> syntax, since not all of the products they want to cover support it.

Which part of it don't they like --- the multiple IN-comparisons?

> I tried something which seems equivalent, but it is running for a very
> long time.
>     AND "H"."tranNo" IN
>         (
>           SELECT "D"."tranNo" FROM "TranDetail" "D"
>             WHERE "D"."caseNo" LIKE '2006TR%'
>               AND "D"."countyNo" = "H"."countyNo"
>         )

No, that's not gonna accomplish a darn thing, because you've still got
a correlated subquery (ie, a reference to outer "H") and so turning the
IN into a join doesn't work.

			regards, tom lane

In response to

pgsql-performance by date

Next:From: Peter KovacsDate: 2007-03-23 23:04:44
Subject: Re: [PERFORM] EXISTS optimization
Previous:From: Kevin GrittnerDate: 2007-03-23 22:37:16
Subject: Re: [PERFORM] EXISTS optimization

pgsql-hackers by date

Next:From: Peter KovacsDate: 2007-03-23 23:04:44
Subject: Re: [PERFORM] EXISTS optimization
Previous:From: Kevin GrittnerDate: 2007-03-23 22:37:16
Subject: Re: [PERFORM] EXISTS optimization

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group