Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 946DBFBD255 for ; Thu, 26 Apr 2012 17:58:37 -0300 (ADT) Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SNVlq-00085s-F3 for pgsql-performance@postgresql.org; Thu, 26 Apr 2012 20:58:36 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id q3QKwKok014115; Thu, 26 Apr 2012 16:58:20 -0400 (EDT) To: Claudio Freire cc: postgres performance list Subject: Re: Weird plan variation with recursive CTEs In-reply-to: References: Comments: In-reply-to Claudio Freire message dated "Thu, 26 Apr 2012 15:23:09 -0300" Date: Thu, 26 Apr 2012 16:58:20 -0400 Message-ID: <14114.1335473900@sss.pgh.pa.us> From: Tom Lane X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201204/171 X-Sequence-Number: 46817 Claudio Freire writes: > On Thu, Apr 26, 2012 at 2:37 PM, Claudio Freire wrote: >> Fun thing is, nothing in the CTE's execution really changed. The only >> change, is that now a sequential scan of overview was chosen instead >> of the index. >> Why could this be? The output (number of search values, even the >> values themselves and their order) is the same between both plans. The estimated size of the UNION output is a lot different, thus discouraging use of a nestloop for the outer query's join. > I just noticed it's misestimating the output of the union distinct, > but not of the select distinct. > One would expect the estimation procedure to be the same in both cases. No, I don't think that follows. The UNION code is aware that people frequently write UNION rather than UNION ALL even when they're not expecting any duplicates, so it shies away from assuming that UNION will reduce the number of rows at all. On the other hand, it's not at all common to write SELECT DISTINCT unless you actually expect some duplicate removal to happen, so the default assumption in the absence of any stats is different --- looks like it's assuming 10X compression by the DISTINCT operation. The real issue here of course is that we have no useful idea how many rows will be produced by the recursive-union CTE, let alone what their statistics will be like. So the planner is falling back on rules of thumb that are pretty much guaranteed to be wrong in any particular case. regards, tom lane