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

Re: IS NULL vs IS NOT NULL

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: "Vig, Sandor (G/FI-2)" <Sandor(dot)Vig(at)audi(dot)hu>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: IS NULL vs IS NOT NULL
Date: 2005-02-25 14:56:10
Message-ID: 20050225065133.B92053@megazone.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Fri, 25 Feb 2005, Vig, Sandor (G/FI-2) wrote:

> Hi,
>
> Another "funny" thing: I have a query which runs
> on (Linux) PostgreSQL 7.4.x under 10 sec. I tried
> to run it on (Windows) PostgreSQL 8.0 yesterday.
> It didn't finished at all! (I shoot it down after 10 minutes)
> I made various tests and I figured out something interesting:
> The same query with:
> 	A, "history.undo_action_id > 0" runs in 10 sec.
> 	B, "history.undo_action_id is not null" runs in 10 sec.
> 	C, "history.undo_action_id is null" runs forever (?!)
> I used EXPLAIN but I couldn't figure out what the problem was.

EXPLAIN ANALYZE would be more useful.  My first guess would be that the IS
NULL is returning many more than the estimated 1 row and as such a nested
loop is a bad plan.  How many history rows match type_id=6 and
undo_action_id is null?



In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2005-02-25 16:05:10
Subject: Re: Possible interesting extra information for explain analyze?
Previous:From: JeffDate: 2005-02-25 13:49:23
Subject: Possible interesting extra information for explain analyze?

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