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
Date: Sat, 07 Jul 2012 20:08:10 -0400
Message-ID: <19124.1341706090@sss.pgh.pa.us> (raw)
In-Reply-To: <9B2D6747F4AB8A47BE45216B06DEDAF92ABE667B@PREXMB01.myfamily.int>
References: <9B2D6747F4AB8A47BE45216B06DEDAF92ABE667B@PREXMB01.myfamily.int>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, nallan@ancestry.com
Subject: Re: Terrible plan for join to nested union
In-Reply-To: <19124.1341706090@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox