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 C8BCDDB5C4 for ; Mon, 31 Oct 2005 20:13:25 -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 17376-03 for ; Tue, 1 Nov 2005 00:13:22 +0000 (GMT) Received: from mail.envyfinancial.com (unknown [206.248.142.186]) by svr1.postgresql.org (Postfix) with ESMTP id 9F2F6DB5C3 for ; Mon, 31 Oct 2005 20:13:23 -0400 (AST) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail.envyfinancial.com (Postfix) with ESMTP id 15F1623453; Mon, 31 Oct 2005 18:57:10 -0500 (EST) Received: from mail.envyfinancial.com ([127.0.0.1]) by localhost (mark.mielke.cc [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 06426-05; Mon, 31 Oct 2005 18:57:09 -0500 (EST) Received: by mail.envyfinancial.com (Postfix, from userid 500) id C185F23436; Mon, 31 Oct 2005 18:57:09 -0500 (EST) Date: Mon, 31 Oct 2005 18:57:09 -0500 From: mark@mark.mielke.cc To: Merlin Moncure Cc: pgsql-performance@postgresql.org Subject: Re: 8.x index insert performance Message-ID: <20051031235709.GA6393@mark.mielke.cc> References: <6EE64EF3AB31D5448D0007DD34EEB3417DD730@Herge.rcsinc.local> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB3417DD730@Herge.rcsinc.local> User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: amavisd-new at mail.envyfinancial.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.379 required=5 tests=[AWL=-0.171, NO_REAL_NAME=0.55] X-Spam-Score: 0.379 X-Spam-Level: X-Archive-Number: 200510/487 X-Sequence-Number: 15255 On Mon, Oct 31, 2005 at 03:27:31PM -0500, Merlin Moncure wrote: > > On Mon, Oct 31, 2005 at 12:32:03PM -0500, Merlin Moncure wrote: > > > if that index is causing the problem, you may want to consider setting > > > up partial index to exclude null values. > > Hey all. > > Pardon my ignorance. :-) > > I've been trying to figure out whether null values are indexed or not from > > the documentation. I was under the impression, that null values are not > > stored in the index. Occassionally, though, I then see a suggestion such > > as the above, that seems to indicate to me that null values *are* stored > > in the index, allowing for the 'exclude null values' to have effect? > > Which is it? :-) > I think I'm the ignorant one...do explain on any lookup on an indexed > field where the field value is null and you get a seqscan. Nahhh... I think the documentation could use more explicit or obvious explanation. Or, I could have checked the source code to see. In any case, I expect we aren't the only ones that lacked confidence. Tom was kind enough to point out that null values are stored. I expect that the seqscan is used if the null values are not selective enough, the same as any other value that isn't selective enough. Now we can both have a little more confidence! :-) Cheers, mark -- mark@mielke.cc / markm@ncf.ca / markm@nortel.com __________________________ . . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/