Re: IS NULL vs IS NOT NULL

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?