Received: from localhost (unknown [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 2B8132E015C
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 28 Dec 2007 11:05:57 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024)
	with ESMTP id 65879-08 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 28 Dec 2007 11:05:44 -0400 (AST)
Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99])
	by postgresql.org (Postfix) with ESMTP id BF3222E0121
	for <pgsql-performance@postgresql.org>;
	Fri, 28 Dec 2007 11:05:50 -0400 (AST)
Received: from Courts-MTA by gwmta.wicourts.gov
	with Novell_GroupWise; Fri, 28 Dec 2007 09:05:48 -0600
Message-Id: <4774BC60.EE98.0025.0@wicourts.gov>
X-Mailer: Novell GroupWise Internet Agent 7.0.2 
Date: Fri, 28 Dec 2007 09:05:36 -0600
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Heikki Linnakangas" <heikki@enterprisedb.com>,
	"Robert Bernabe" <robert_bernabe@yahoo.com>
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>
	<4767917E.9050206@enterprisedb.com>
In-Reply-To: <4767917E.9050206@enterprisedb.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200712/421
X-Sequence-Number: 28237

>>> On Tue, Dec 18, 2007 at  3:23 AM, in message
<4767917E.9050206@enterprisedb.com>, Heikki Linnakangas
<heikki@enterprisedb.com> wrote:=20
> Robert Bernabe wrote:
>> In a nutshell it seems that MS SQL allows bad T-SQL code by optimizing =
and=20
>> ignoring redundant/useless from and where clauses in an update =
statement=20
>> whereas plpgsql will execute exactly what the code is asking it to =
do...
>>=20
>> We had several update instances in the T-SQL code that looked like this =
:
>>=20
>> update "_tbl_tmp2"
>> set "LongBackPeriod" =3D (select count ("EPeriod") from "_tbl_tmp1" =
where=20
> "_tbl_tmp1"."Row" =3D "_tbl_tmp2"."Row");
>> --------------------------------------------------
>> from "_tbl_tmp2" tmp2, "_tbl_tmp1" tmp1
>> where tmp2."Row" =3D tmp1."Row";
>> ---------------------------------------------------
>=20
> I'm sure MS SQL doesn't ignore those lines, but does=20
> use a more clever plan.
=20
Actually, this is what happens in the absence of a standard --
allowing a FROM clause on an UPDATE statement is an extension to
the standard.  MS SQL Server and PostgreSQL have both added such an
extension with identical syntax and differing semantics.  MS SQL
Server allows you to declare the updated table in the FROM clause
so that you can alias it; the first reference to the updated table
in the FROM clause is not taken as a separate reference, so the
above is interpreted exactly the same as:
=20
update "_tbl_tmp2"
set "LongBackPeriod" =3D (select count ("EPeriod") from "_tbl_tmp1" =
where=20
_tbl_tmp1"."Row" =3D "_tbl_tmp2"."Row")
from "_tbl_tmp1" tmp1
where "_tbl_tmp2"."Row" =3D tmp1."Row"
=20
PostgreSQL sees tmp2 as a second, independent reference to the
updated table.  This can be another big "gotcha" in migration.
=20
-Kevin
=20