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: Sun, 08 Jul 2012 11:56:35 -0400
Message-ID: <3857.1341762995@sss.pgh.pa.us> (raw)
In-Reply-To: <9B2D6747F4AB8A47BE45216B06DEDAF92ABE66BC@PREXMB01.myfamily.int>
References: <9B2D6747F4AB8A47BE45216B06DEDAF92ABE667B@PREXMB01.myfamily.int>
<19124.1341706090@sss.pgh.pa.us>
<9B2D6747F4AB8A47BE45216B06DEDAF92ABE66BC@PREXMB01.myfamily.int>
Nate Allan <nallan@ancestry.com> writes:
> It seems to me that the join condition (and hence the restriction) should be pushed down into both sides of the union to bring the cardinality limit from millions to 1. I'm imagining a rewrite like this:
> R(a) J (b U c) -> (b J R(a)) U (c J R(a))
> ...where R = Restrict, J = Join, U = Union
[ eyes that suspiciously ... ] I'm not convinced that such a
transformation is either correct in general (you seem to be assuming
at least that A's join column is unique, and what is the UNION operator
supposed to do with A's other columns?) or likely to lead to a
performance improvement in general.
We possibly could push down a join condition on the inner side of a
nestloop, similarly to what's done in the UNION ALL case ... but that
would require a complete refactoring of what the planner does with
UNIONs. By and large, very little optimization effort has been put
into non-ALL UNION (or INTERSECT or EXCEPT). You should not expect
that to change on a time scale of less than years.
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: <3857.1341762995@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