X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2A998564CE for ; Fri, 25 Feb 2005 14:55:57 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 78153-08 for ; Fri, 25 Feb 2005 14:55:54 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id 8E7D9564BF for ; Fri, 25 Feb 2005 14:55:51 +0000 (GMT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 4DA1D356F3; Fri, 25 Feb 2005 06:56:10 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 4BF08356F1; Fri, 25 Feb 2005 06:56:10 -0800 (PST) Date: Fri, 25 Feb 2005 06:56:10 -0800 (PST) From: Stephan Szabo To: "Vig, Sandor (G/FI-2)" Cc: pgsql-performance@postgresql.org Subject: Re: IS NULL vs IS NOT NULL In-Reply-To: <977921B17B2F2048AA5AAE9B4CBB713E01423D42@huaudigs0035.audi.de> Message-ID: <20050225065133.B92053@megazone.bigpanda.com> References: <977921B17B2F2048AA5AAE9B4CBB713E01423D42@huaudigs0035.audi.de> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/361 X-Sequence-Number: 10690 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?