Received: from maia.hub.org (maia-5.hub.org [200.46.204.29])
	by mail.postgresql.org (Postfix) with ESMTP id A4A6B1336B42
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 16 Nov 2010 03:08:46 -0400 (AST)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024)
	with ESMTP id 96252-03
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 16 Nov 2010 07:08:38 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail.catalyst.net.nz (bertrand.catalyst.net.nz [202.78.240.40])
	by mail.postgresql.org (Postfix) with ESMTP id 0E5E61335B36
	for <pgsql-performance@postgresql.org>;
	Tue, 16 Nov 2010 03:08:36 -0400 (AST)
Received: from localhost (localhost [127.0.0.1])
	by mail.catalyst.net.nz (Postfix) with ESMTP id 57D10331A2
	for <pgsql-performance@postgresql.org>;
	Tue, 16 Nov 2010 20:08:33 +1300 (NZDT)
X-Virus-Scanned: Debian amavisd-new at catalyst.net.nz
Received: from mail.catalyst.net.nz ([127.0.0.1])
	by localhost (bertrand.catalyst.net.nz [127.0.0.1]) (amavisd-new,
	port 10024)
	with ESMTP id B6+M3siw4CvQ for <pgsql-performance@postgresql.org>;
	Tue, 16 Nov 2010 20:08:32 +1300 (NZDT)
Received: from [192.168.1.11] (122-58-208-29.jetstream.xtra.co.nz
	[122.58.208.29]) (Authenticated sender: mark.kirkwood)
	by mail.catalyst.net.nz (Postfix) with ESMTPSA id 320BB32C1E
	for <pgsql-performance@postgresql.org>;
	Tue, 16 Nov 2010 20:08:32 +1300 (NZDT)
Message-ID: <4CE22DEE.6080405@catalyst.net.nz>
Date: Tue, 16 Nov 2010 20:08:30 +1300
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.15) Gecko/20101027 Thunderbird/3.0.10
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: 
References: <COL115-W5250BF8BDC50E0A64DABE4A8360@phx.gbl>
In-Reply-To: <COL115-W5250BF8BDC50E0A64DABE4A8360@phx.gbl>
Content-Type: multipart/alternative;
	boundary="------------050908070102050701080202"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01
X-Spam-Level: 
X-Archive-Number: 201011/255
X-Sequence-Number: 41279

This is a multi-part message in MIME format.
--------------050908070102050701080202
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 16/11/10 09:14, Humair Mohammed wrote:
> I have 2 tables with a 200,000 rows of data 3 character/string columns ID, Question and Response. The query below compares the data between the 2 tables based on ID and Question and if the Response does not match between the left table and the right table it identifies the ID's where there is a mismatch. Running the query in SQL Server 2008 using the ISNULL function take a few milliseconds. Running the same query in Postgresql takes over 70 seconds. The 2 queries are below:
> SQL Server 2008 R2 Queryselect t1.id from table1 t1 inner join table2 t2 on t1.id = t2.id and t1.question = t2.question and isnull(t1.response,'ISNULL')<>  isnull(t2.response,'ISNULL')
> Postgres 9.1 Queryselect t1.id from table1 t1 inner join table2 t2 on t1.id = t2.id and t1.question = t2.question and coalesce(t1.response,'ISNULL')<>  coalesce(t2.response,'ISNULL')
> What gives? 		 	   		
>    

Can we see the execution plans: (EXPLAIN <the query text here>) for 
Postgres and (however you get text based query plan from Sql Server), so 
we can see if there is any obvious differences in how things are done.

Also probably worthwhile is telling us the table definitions of the 
tables concerned.

For Postgres - did you run ANALYZE on the database concerned before 
running the queries? (optimizer stats are usually updated automatically, 
but if you were quick to run the queries after loading the data they 
might not have been).

regards

Mark

--------------050908070102050701080202
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html; charset=ISO-8859-1"
 http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#ffffff">
On 16/11/10 09:14, Humair Mohammed wrote:
<blockquote cite="mid:COL115-W5250BF8BDC50E0A64DABE4A8360@phx.gbl"
 type="cite">
  <pre wrap="">
I have 2 tables with a 200,000 rows of data 3 character/string columns ID, Question and Response. The query below compares the data between the 2 tables based on ID and Question and if the Response does not match between the left table and the right table it identifies the ID's where there is a mismatch. Running the query in SQL Server 2008 using the ISNULL function take a few milliseconds. Running the same query in Postgresql takes over 70 seconds. The 2 queries are below:
SQL Server 2008 R2 Queryselect t1.id from table1 t1 inner join table2 t2 on t1.id = t2.id and t1.question = t2.question and isnull(t1.response,'ISNULL') &lt;&gt; isnull(t2.response,'ISNULL')
Postgres 9.1 Queryselect t1.id from table1 t1 inner join table2 t2 on t1.id = t2.id and t1.question = t2.question and coalesce(t1.response,'ISNULL') &lt;&gt; coalesce(t2.response,'ISNULL')
What gives? 		 	   		  
  </pre>
</blockquote>
<font size="-1"><font face="Helvetica"><br>
Can we see the execution plans: (EXPLAIN &lt;the query text here&gt;)
for Postgres and (however you get text based query plan from Sql
Server), so we can see if there is any obvious differences in how
things are done.<br>
<br>
Also probably worthwhile is telling us the table definitions of the
tables concerned.<br>
<br>
For Postgres - did you run ANALYZE on the database concerned before
running the queries? (optimizer stats are usually updated
automatically, but if you were quick to run the queries after loading
the data they might not have been).<br>
<br>
regards<br>
<br>
Mark<br>
</font></font>
</body>
</html>

--------------050908070102050701080202--