Re: EXISTS optimization

From: Tom Lane
Subject: Re: EXISTS optimization
Date: ,
Msg-id: 25339.1174686582@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: EXISTS optimization  ("Kevin Grittner")
Responses: Re: EXISTS optimization  ("Kevin Grittner")
Re: EXISTS optimization  (Martijn van Oosterhout)
List: pgsql-hackers

Tree view

EXISTS optimization  ("Kevin Grittner", )
 Re: EXISTS optimization  (Tom Lane, )
  Re: EXISTS optimization  ("Kevin Grittner", )
   Re: [PERFORM] EXISTS optimization  ("Kevin Grittner", )
   Re: EXISTS optimization  (Tom Lane, )
   Re: [PERFORM] EXISTS optimization  ("Peter Kovacs", )
  Re: EXISTS optimization  (Martijn van Oosterhout, )
   Re: EXISTS optimization  ("Kevin Grittner", )
    Re: EXISTS optimization  (Martijn van Oosterhout, )
 Re: [PERFORM] EXISTS optimization  ("Kevin Grittner", )

"Kevin Grittner" <> 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


pgsql-hackers by date:

From: "Kevin Grittner"
Date:
Subject: Re: [PERFORM] EXISTS optimization
From: Grzegorz Jaskiewicz
Date:
Subject: Re: Time to package 8.2.4