Re: Terrible plan for join to nested union

От: Tom Lane
Тема: Re: Terrible plan for join to nested union
Дата: ,
Msg-id: 19124.1341706090@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Terrible plan for join to nested union  (Nate Allan)
Ответы: Re: Terrible plan for join to nested union  (Nate Allan)
Список: pgsql-performance

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

Terrible plan for join to nested union  (Nate Allan, )
 Re: Terrible plan for join to nested union  (Tom Lane, )
  Re: Terrible plan for join to nested union  (Nate Allan, )
   Re: Terrible plan for join to nested union  (Pavel Stehule, )
    Re: Terrible plan for join to nested union  (Nate Allan, )
   Re: Terrible plan for join to nested union  (Tom Lane, )
    Re: Terrible plan for join to nested union  (Nate Allan, )
     Re: Terrible plan for join to nested union  (Tom Lane, )
      Re: Terrible plan for join to nested union  (Nate Allan, )

Nate Allan <> writes:
> I have a query which joins to a nested union and I'm getting a plan which never returns.  Here is the query
simplifiedas much as possible: 
> select 'anything' as result
>                from "Attribute" as A1
>                               inner join
>                               (
>                                              select R."TargetID" as "SourceID"
>                                                             from "Relationship" as R
>                                              union
>                                              select A2."PersonID" as "SourceID"
>                                                             from "Attribute" as A2
>                               ) as X on (A1."PersonID" = X."SourceID")
>                where (A1."ID" = 124791200)

What exactly are you trying to accomplish here?  AFAICS, the UNION
result must include every possible value of Attribute.PersonID, which
means the inner join cannot eliminate any rows of A1 (except those with
null PersonID), which seems a tad silly.

Anyway, I wonder whether you'd get better results with an EXISTS over
a correlated UNION ALL subquery, ie, something like

select 'anything' as result
               from "Attribute" as A1
               where (A1."ID" = 124791200)
                  and exists (
                              select 1 from "Relationship" as R
                                where R."TargetID" = A1."PersonID"
                              union all
                              select 1 from "Attribute" as A2
                                where A2."PersonID" = A1."PersonID"
                             )

since you're evidently hoping that the EXISTS won't need to be evaluated
for very many rows of A1.  Or you could use an OR of two EXISTS to skip
the UNION altogether.

            regards, tom lane


В списке pgsql-performance по дате сообщения:

От: Pavel Stehule
Дата:
Сообщение: Re: Terrible plan for join to nested union
От: Nate Allan
Дата:
Сообщение: Re: Terrible plan for join to nested union