Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229])
	by mail.postgresql.org (Postfix) with ESMTP id CCF051431A40
	for <pgsql-performance@postgresql.org>;
	Sat,  7 Jul 2012 21:08:41 -0300 (ADT)
Received: from sss.pgh.pa.us ([66.207.139.130])
	by magus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tgl@sss.pgh.pa.us>) id 1Snf33-000364-K0
	for pgsql-performance@postgresql.org; Sun, 08 Jul 2012 00:08:39 +0000
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id q6808Bm9019125;
	Sat, 7 Jul 2012 20:08:11 -0400 (EDT)
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Nate Allan <nallan@ancestry.com>
cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Terrible plan for join to nested union
In-reply-to: <9B2D6747F4AB8A47BE45216B06DEDAF92ABE667B@PREXMB01.myfamily.int>
References: <9B2D6747F4AB8A47BE45216B06DEDAF92ABE667B@PREXMB01.myfamily.int>
Comments: In-reply-to Nate Allan <nallan@ancestry.com>
	message dated "Sat, 07 Jul 2012 22:35:06 -0000"
Date: Sat, 07 Jul 2012 20:08:10 -0400
Message-ID: <19124.1341706090@sss.pgh.pa.us>
X-Pg-Spam-Score: -1.9 (-)
X-Archive-Number: 201207/75
X-Sequence-Number: 47256

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 simplified as 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