X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6B3B9D8AD1 for ; Wed, 2 Nov 2005 13:26:39 -0400 (AST) 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 41856-02 for ; Wed, 2 Nov 2005 17:26:37 +0000 (GMT) Received: from stark.xeocode.com (stark.xeocode.com [216.58.44.227]) by svr1.postgresql.org (Postfix) with ESMTP id C56F9D86AD for ; Wed, 2 Nov 2005 13:26:37 -0400 (AST) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1EXMNV-0003Xi-00; Wed, 02 Nov 2005 12:26:25 -0500 To: "Merlin Moncure" Cc: "Scott Marlowe" , Subject: Re: 8.x index insert performance References: <6EE64EF3AB31D5448D0007DD34EEB3417DD732@Herge.rcsinc.local> In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB3417DD732@Herge.rcsinc.local> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 02 Nov 2005 12:26:25 -0500 Message-ID: <87oe53hsfy.fsf@stark.xeocode.com> Lines: 21 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.01 required=5 tests=[AWL=0.010] X-Spam-Score: 0.01 X-Spam-Level: X-Archive-Number: 200511/35 X-Sequence-Number: 15292 "Merlin Moncure" writes: > > select * from sometable where somefield IS NULL won't work because IS > is > > not a nomally indexible operator. > > Ah, I didn't know that. So there is no real reason not to exclude null > values from all your indexes :). Reading Tom's recent comments > everything is clear now. There are other reasons. If you want a query like SELECT * FROM tab ORDER BY col LIMIT 10 to use an index on col then it can't exclude NULLs or else it wouldn't be useful. (Oracle actually has this problem, you frequently have to add WHERE col IS NOT NULL" in order to let it use an index.) -- greg