Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TY1Wm-0006BF-9l for pgsql-performance@postgresql.org; Mon, 12 Nov 2012 21:26:44 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TY1Wj-0006gh-VA for pgsql-performance@postgresql.org; Mon, 12 Nov 2012 21:26:43 +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 qACLQcWo008790; Mon, 12 Nov 2012 16:26:38 -0500 (EST) From: Tom Lane To: Willem Leenen cc: pg@fastcrypt.com, pgsql-performance@postgresql.org Subject: Re: performance regression with 9.2 In-reply-to: References: , <5518.1352753033@sss.pgh.pa.us> Comments: In-reply-to Willem Leenen message dated "Mon, 12 Nov 2012 21:13:46 +0000" Date: Mon, 12 Nov 2012 16:26:37 -0500 Message-ID: <8789.1352755597@sss.pgh.pa.us> X-Pg-Spam-Score: -2.1 (--) X-Archive-Number: 201211/145 X-Sequence-Number: 48511 Willem Leenen writes: > To me, i see a mismatch between the optimizer and the actual records > retrieved in the fast SQL as well, so plan instability is a realistic > scenario. Well, the rowcount estimates for a recursive union are certainly pretty bogus, but those are the same either way. The reason this looks like a bug and not just statistical issues is that the join inside the recursive union is done as a hash, even though that's much more expensive (according to the estimates, not reality) than a nestloop. Presumably the planner is failing to even consider a nestloop-with-inner-indexscan join there, else it would have picked that type of plan. Why it's failing is as yet unclear. regards, tom lane