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
