2010/11/15 Humair Mohammed <humairm@hotmail.com>:
> 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 Query
> select 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 Query
> select 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?
I think, so must problem can be in ugly predicate
coalesce(t1.response,'ISNULL') <>
> coalesce(t2.response,'ISNULL')
try use a IS DISTINCT OF operator
... AND t1.response IS DISTINCT t2.response
Regards
Pavel Stehule
p.s. don't use a coalesce in WHERE clause if it is possible.