Re: Terrible plan for join to nested union - Mailing list pgsql-performance

Nate Allan <nallan@ancestry.com> 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