Re: [HACKERS] EXISTS optimization

From: Tom Lane
Subject: Re: [HACKERS] EXISTS optimization
Date: ,
Msg-id: 26175.1174691052@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: [HACKERS] EXISTS optimization  ("Kevin Grittner")
List: pgsql-performance

Tree view

EXISTS optimization  ("Kevin Grittner", )
 Re: [HACKERS] EXISTS optimization  (Tom Lane, )
  Re: [HACKERS] EXISTS optimization  ("Kevin Grittner", )
   Re: [HACKERS] EXISTS optimization  ("Kevin Grittner", )
   Re: [HACKERS] EXISTS optimization  (Tom Lane, )
  Re: [HACKERS] EXISTS optimization  (Martijn van Oosterhout, )
   Re: [HACKERS] EXISTS optimization  ("Kevin Grittner", )
    Re: [HACKERS] EXISTS optimization  (Martijn van Oosterhout, )
 Re: [HACKERS] EXISTS optimization  ("Kevin Grittner", )
  Re: [HACKERS] EXISTS optimization  ("Craig A. James", )

"Kevin Grittner" <> writes:
> Tom Lane <> 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


pgsql-performance by date:

From: "Kevin Grittner"
Date:
Subject: Re: [HACKERS] EXISTS optimization
From: "amrit angsusingh"
Date:
Subject: Optimization postgresql 8.1.4 FC 6 X64 ?