From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Humair Mohammed <humairm(at)hotmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: |
Date: | 2010-11-16 07:12:03 |
Message-ID: | AANLkTin+MPhrYUzkCZjdAMLhF3KKZCCuvDm0=Npf9jz7@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
2010/11/15 Humair Mohammed <humairm(at)hotmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Louis-David Mitterrand | 2010-11-16 10:50:55 | best db schema for time series data? |
Previous Message | Mark Kirkwood | 2010-11-16 07:08:30 | Re: |