Re: [PERFORM] Evaluation of PG performance vs MSDE/MSSQL 2000 (not 2005)

From: Kevin Grittner <Kevin.Grittner(at)wicourts.gov>
Date: Fri Dec 28 2007 - 10:05:36 EST

>>> On Tue, Dec 18, 2007 at 3:23 AM, in message <4767917E.9050206@enterprisedb.com>, Heikki Linnakangas <heikki@enterprisedb.com> wrote:

> 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";
>> ---------------------------------------------------

> 
> I'm sure MS SQL doesn't ignore those lines, but does 
> use a more clever plan.
 

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:  

update "_tbl_tmp2"
set "LongBackPeriod" = (select count ("EPeriod") from "_tbl_tmp1" where _tbl_tmp1"."Row" = "_tbl_tmp2"."Row")
from "_tbl_tmp1" tmp1
where "_tbl_tmp2"."Row" = tmp1."Row"  

PostgreSQL sees tmp2 as a second, independent reference to the updated table. This can be another big "gotcha" in migration.  

-Kevin  

---------------------------(end of broadcast)---------------------------

TIP 6: explain analyze is your friend Received on Fri Dec 28 10:07:45 2007