Re: EXISTS optimization

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> explain analyze
> SELECT "A"."adjustmentNo", "A"."tranNo", "A"."countyNo", "H"."date", "H"."userId", "H"."time"
>   FROM "Adjustment" "A"
>   JOIN "TranHeader" "H" ON ("H"."tranId" = "A"."adjustmentNo" AND "H"."countyNo" = "A"."countyNo" AND "H"."tranNo" =
"A"."tranNo")
>   WHERE "H"."tranType" = 'A'
>     AND "A"."date" > DATE '2006-01-01'
>     AND "H"."countyNo" = 66
>     AND "A"."countyNo" = 66
>     AND EXISTS
>         (
>           SELECT 1 FROM "TranDetail" "D"
>             WHERE "D"."tranNo" = "H"."tranNo"
>               AND "D"."countyNo" = "H"."countyNo"
>               AND "D"."caseNo" LIKE '2006TR%'
>         )
> ;

> The commercial product scans the index on caseNo in TranDetail to build a work table of unique values, then uses
indexedaccess to the TranHeader and then to Adjustment. 

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%'
        )

We don't currently try to flatten EXISTS into a unique/join plan as we
do for IN.  I seem to recall not doing so when I rewrote IN planning
because I didn't think it would be exactly semantically equivalent,
but that was awhile ago.  Right at the moment it seems like it ought
to be equivalent as long as the comparison operators are strict.

            regards, tom lane