X-Original-To: pgsql-performance@postgresql.org Received: from localhost (unknown [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 93BF2925142 for ; Tue, 20 May 2003 13:34:01 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 34638-03 for ; Tue, 20 May 2003 13:33:56 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by developer.postgresql.org (Postfix) with ESMTP id A094992514B for ; Tue, 20 May 2003 13:33:54 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h4KHXeU6000194; Tue, 20 May 2003 13:33:40 -0400 (EDT) To: Josh Berkus Cc: Eugene Fokin , Victor Yegorov , pgsql-performance@postgresql.org Subject: Re: 7.3.2 vs 7.1.2 In-reply-to: <200305200935.25766.josh@agliodbs.com> References: <20030520122838.GA28654@solvo.ru> <20030520130913.GM1976@nordlb.lv> <20030520132842.GA29262@solvo.ru> <200305200935.25766.josh@agliodbs.com> Comments: In-reply-to Josh Berkus message dated "Tue, 20 May 2003 09:35:25 -0700" Date: Tue, 20 May 2003 13:33:40 -0400 Message-ID: <193.1053452020@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/203 X-Sequence-Number: 1982 Josh Berkus writes: > Another question ... given that 7.3.2's estimates are very similar to 7.2.1's > estimates, but the real execution time is much slower, is it possible that > the 7.3.2 copy of the database is being loaded on a much slower disk? Nah, the big reason for the discrepancy is that 7.3 is unable to prune away evaluation of all those sub-selects in the view's target list, per my previous response. Up till just recently (post-7.3) the planner didn't bother to charge any evaluation cost for targetlist expressions, and so the estimated costs don't reflect the difference. (The reasoning behind that behavior was that the planner couldn't affect the evaluation costs of targetlist expressions by choosing a different plan, since the number of rows they'll be computed for will be the same in every correct plan. But now that we allow arbitrarily complex stuff in sub-selects, that reasoning doesn't hold water anymore --- it's important to propagate a good estimate of the cost up to the enclosing plan. So as of 7.4 we expend the cycles to add in tlist execution time estimates.) I am still interested in the apparent difference in the time taken for that bottom indexscan, though. The width difference that you noticed is because the unflattened view needs to fetch many more columns of the table than the flattened query needs. But the same number of rows get fetched, and approximately the same number of disk blocks ought to get read, so it's hard to see why there'd be such a large difference. regards, tom lane