Re:

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux