Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id B552916FA502 for ; Mon, 9 Jul 2012 02:50:35 -0300 (ADT) Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1So6rH-0005tW-7A for pgsql-performance@postgresql.org; Mon, 09 Jul 2012 05:50:34 +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 q695nrFi029375; Mon, 9 Jul 2012 01:49:53 -0400 (EDT) From: Tom Lane To: Nate Allan cc: "pgsql-performance@postgresql.org" Subject: Re: Terrible plan for join to nested union In-reply-to: <9B2D6747F4AB8A47BE45216B06DEDAF92ABE6739@PREXMB01.myfamily.int> References: <9B2D6747F4AB8A47BE45216B06DEDAF92ABE667B@PREXMB01.myfamily.int> <19124.1341706090@sss.pgh.pa.us> <9B2D6747F4AB8A47BE45216B06DEDAF92ABE66BC@PREXMB01.myfamily.int> <3857.1341762995@sss.pgh.pa.us> <9B2D6747F4AB8A47BE45216B06DEDAF92ABE6739@PREXMB01.myfamily.int> Comments: In-reply-to Nate Allan message dated "Mon, 09 Jul 2012 04:02:23 -0000" Date: Mon, 09 Jul 2012 01:49:52 -0400 Message-ID: <29374.1341812992@sss.pgh.pa.us> X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201207/81 X-Sequence-Number: 47262 Nate Allan writes: > 2) Why would more effort go into Union All rather than Union? The UNION ALL case matches up with, and shares planning and execution code with, table-inheritance and partitioning scenarios. So yes, it really is more interesting to more people than UNION DISTINCT. (IIRC, the code that does that stuff was originally meant to support the inheritance case, and we hacked UNION ALL to be able to share the logic, not vice versa.) Right now, UNION DISTINCT, along with INTERSECT and EXCEPT, have basically no optimization support whatsoever: all of them go through a code path that just evaluates both input relations and performs the set-combination operation. All of that code dates from a period about a dozen years ago when we were more interested in getting the right answer at all than how fast it was. Rewriting it all to have some optimization capability is certainly on the wish-list ... but the fact that it hasn't risen to the top of anybody's to-do list in that time indicates to me that it probably isn't going to get done in the next little while either. And even if someone were to start working on it right now, it's not a small project. Sorry to be the bearer of bad news, but this isn't going to change just because you try to label it a bug. regards, tom lane