Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id E68312E0044 for ; Tue, 18 Dec 2007 05:23:26 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 56626-09 for ; Tue, 18 Dec 2007 05:23:18 -0400 (AST) Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.184]) by postgresql.org (Postfix) with ESMTP id 84AE92E36E2 for ; Tue, 18 Dec 2007 05:23:22 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id f5so1157468nfh.23 for ; Tue, 18 Dec 2007 01:23:20 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender; bh=p0QdFmfxDKq9m7JC7r+YMCaP3giVQobPwGLpv1QX+1A=; b=ntPPUFPt0ELSBL6QHnwcjsTCBMiYYdYnphypsVhQs1g443l9s35kC9wmiN0H+UVeSsnxbExMZ+MF/y9tVJ6jMRPswsBm9NmmKfdfpy+mFCoIACJhJMD5pF5lzGJC433t/CfSIQlx7JL5eRNiqR8wn3i/9lmyDO48hATRtM11qJg= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender; b=Wd6OdYqLjDfgscmJms4cYKLdmS8vBSF7RsWm+YcayAZ030CZwS3kR+kv8ICK4/dNLz6RZvMAp7OlPHM53UAsRu2ovxfEVINfix+IbU3tuOzz0nHZCDq+rQC9Pbdv671smmUuNK2on53dRl5zGS7dmM1SGP+3hfPHocrcQOHhTE8= Received: by 10.78.147.6 with SMTP id u6mr9724935hud.78.1197969800291; Tue, 18 Dec 2007 01:23:20 -0800 (PST) Received: from ?192.168.1.33? ( [86.31.56.43]) by mx.google.com with ESMTPS id h6sm1857696nfh.2007.12.18.01.23.12 (version=SSLv3 cipher=RC4-MD5); Tue, 18 Dec 2007 01:23:16 -0800 (PST) Message-ID: <4767917E.9050206@enterprisedb.com> Date: Tue, 18 Dec 2007 09:23:10 +0000 From: Heikki Linnakangas User-Agent: Icedove 1.5.0.14pre (X11/20071020) MIME-Version: 1.0 To: Robert Bernabe CC: pgsql-performance@postgresql.org Subject: Re: Evaluation of PG performance vs MSDE/MSSQL 2000 (not 2005) References: <712109.35451.qm@web51610.mail.re2.yahoo.com> In-Reply-To: <712109.35451.qm@web51610.mail.re2.yahoo.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200712/265 X-Sequence-Number: 28081 Robert Bernabe wrote: > In a nutshell it seems that MS SQL allows bad T-SQL code by optimizing and ignoring redundant/useless from and where clauses in an update statement whereas plpgsql will execute exactly what the code is asking it to do... > > We had several update instances in the T-SQL code that looked like this : > > update "_tbl_tmp2" > set "LongBackPeriod" = (select count ("EPeriod") from "_tbl_tmp1" where "_tbl_tmp1"."Row" = "_tbl_tmp2"."Row"); > -------------------------------------------------- > from "_tbl_tmp2" tmp2, "_tbl_tmp1" tmp1 > where tmp2."Row" = tmp1."Row"; > --------------------------------------------------- Those lines are not totally useless from DB point of view. If there is no rows that match the join, the WHERE clause will be false, and no rows will be updated. So I'm sure MS SQL doesn't ignore those lines, but does use a more clever plan. Perhaps it stops processing the join as soon as is finds a match, while we perform the whole join, for example. > In T-SQL, the performance is the same whether the last two lines are there or not... > > In plpgsql, this is not the case the from and where clauses are not necessary and probably creates an internal (rather useless and time consuming) inner join in plpgsql which accounts for the original performance issue. You can check the access plan with EXPLAIN. > I'm happy (actually ecstatic) to report that Win2kPro + PG performance is slightly faster than Win2kPro + MSSQL/MSDE. > > Linux(FC7) + PG 8.x performance seems to be 3x faster than Win2KPro + MSSQL/MSDE for our stored functions. > > Thanks for all the help! Am a believer now. :) Nice to hear :). -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com