Re: EXISTS optimization

От: Tom Lane
Тема: Re: EXISTS optimization
Дата: ,
Msg-id: 25339.1174686582@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: EXISTS optimization  ("Kevin Grittner")
Ответы: Re: EXISTS optimization  ("Kevin Grittner")
Re: EXISTS optimization  (Martijn van Oosterhout)
Список: pgsql-hackers

Скрыть дерево обсуждения

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 по дате сообщения:

От: "Kevin Grittner"
Дата:
Сообщение: Re: [PERFORM] EXISTS optimization
От: Grzegorz Jaskiewicz
Дата:
Сообщение: Re: Time to package 8.2.4