Re:

From: Mark Kirkwood <mark(dot)kirkwood(at)catalyst(dot)net(dot)nz>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re:
Date: 2010-11-16 07:08:30
Message-ID: 4CE22DEE.6080405@catalyst.net.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

  • at 2010-11-15 20:14:26 from Humair Mohammed

Browse pgsql-performance by date

  From Date Subject
Next Message Pavel Stehule 2010-11-16 07:12:03 Re:
Previous Message Jayadevan M 2010-11-16 07:00:54 Re: