Skip site navigation (1) Skip section navigation (2)

Re:

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 (view raw or flat)
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.

In response to

  • at 2010-11-15 20:14:26 from Humair Mohammed

Responses

pgsql-performance by date

Next:From: Louis-David MitterrandDate: 2010-11-16 10:50:55
Subject: best db schema for time series data?
Previous:From: Mark KirkwoodDate: 2010-11-16 07:08:30
Subject: Re:

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group