2010/11/15 Humair Mohammed <humairm@xxxxxxxxxxx>: > 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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
