X-Original-To: pgsql-performance@postgresql.org Received: from localhost (unknown [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 57A03925098 for ; Tue, 20 May 2003 12:40:42 -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 15310-10 for ; Tue, 20 May 2003 12:40:35 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by developer.postgresql.org (Postfix) with ESMTP id 2C16592508E for ; Tue, 20 May 2003 12:40:35 -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 h4KGeMU6029700; Tue, 20 May 2003 12:40:22 -0400 (EDT) To: Victor Yegorov Cc: Eugene Fokin , pgsql-performance@postgresql.org Subject: Re: 7.3.2 vs 7.1.2 In-reply-to: <20030520140713.GN1976@nordlb.lv> References: <20030520122838.GA28654@solvo.ru> <20030520130913.GM1976@nordlb.lv> <20030520132842.GA29262@solvo.ru> <20030520140713.GN1976@nordlb.lv> Comments: In-reply-to Victor Yegorov message dated "Tue, 20 May 2003 17:07:13 +0300" Date: Tue, 20 May 2003 12:40:21 -0400 Message-ID: <29699.1053448821@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/202 X-Sequence-Number: 1981 Victor Yegorov writes: > As you can see, in 7.2.1 index scan on loads (load_rcn_id_idx) takes > 0.04..786.13, but in 7.3.2 - 0.07..4486.76. That seems very odd, doesn't it? Is it reproducible? I'm wondering if the 7.2 table was clustered on the index while the 7.3 wasn't. Most of the cost differential, however, is coming from the fact that 7.3 doesn't flatten the view and thus fails to realize that it doesn't need to evaluate any of the view's targetlist expressions. Note the lack of any "SubPlans" in the 7.2 plan, whereas they're accounting for a good deal of time in the 7.3 plan. The reason that the view isn't flattened is that pulling up targetlists containing sub-selects turned out to break some obscure cases involving join alias variables, and by the time we discovered this (after 7.3 release) there was no practical way to fix it except to disable the optimization. It's fixed properly in CVS tip (7.4 branch) but 7.3.* is just going to be slow on such cases; the fix is much too complex to risk back-porting. I'm not sure whether selecting the count from this view is really all that important to Eugene, but if it is he could make an alternate view that has the same FROM clause and nothing interesting in its select list, and then count that. regards, tom lane