From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: pgsql-performance@postgresql.org
Subject: Re:
Date: Tue, 16 Nov 2010 20:08:30 +1300
Message-ID: <4CE22DEE.6080405@catalyst.net.nz> (raw)
In-Reply-To: <COL115-W5250BF8BDC50E0A64DABE4A8360@phx.gbl>
References: <COL115-W5250BF8BDC50E0A64DABE4A8360@phx.gbl>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark.kirkwood@catalyst.net.nz
Subject: Re:
In-Reply-To: <4CE22DEE.6080405@catalyst.net.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox