Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 6FEBC64FCD8 for ; Tue, 11 Nov 2008 19:34:47 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 85766-05 for ; Tue, 11 Nov 2008 19:34:44 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.176]) by mail.postgresql.org (Postfix) with ESMTP id 32EE764FC2D for ; Tue, 11 Nov 2008 19:34:43 -0400 (AST) Received: by wa-out-1112.google.com with SMTP id j4so63555wah.19 for ; Tue, 11 Nov 2008 15:34:42 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=GHCaAn6VSKQ4K+zrK5ZgMd5ojPZqSB8PAf5w+RxAfK4=; b=JE4psG+Jp9ltj4CpPF4iMtvTy4TUiTBbdu+HAZTEuXo66E9TFBQb1bUBZqXmHhQx3G EuHIFY6n7IrIK9sK0GSJGgTUyuaFUzg/i/XO+9k3wRBquVQoKTCPZfXhTwE8nx530ac6 3Ius27XzBVQzIZpCcTGuBwTOffR2huxgTUycQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=YYS7B3t6dP0puc8XlfHlaBjpU/c3ME5qD6BBTcHnVDX1kNYR1Wu9tEZH1aEvbtPMWD bYJpu7HVEldVFBGrM5+iXYHZphSjxugfbv6iJoNqC2Nk5T10sk9xKxDKmqq68SYEkFSP ZSjhikADM75bOTWt3rWdVFlvWByGqSFQxXCAc= Received: by 10.114.159.5 with SMTP id h5mr5763144wae.36.1226446482724; Tue, 11 Nov 2008 15:34:42 -0800 (PST) Received: by 10.114.39.11 with HTTP; Tue, 11 Nov 2008 15:34:42 -0800 (PST) Message-ID: Date: Tue, 11 Nov 2008 16:34:42 -0700 From: "Scott Marlowe" To: "Vladimir Sitnikov" Subject: Re: Using index for IS NULL query Cc: "Tomas Vondra" , Andrus , pgsql-performance@postgresql.org In-Reply-To: <1d709ecc0811111500i3c63e345qfa3be90264629256@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <4919F2C6.4000303@fuzzy.cz> <1d709ecc0811111500i3c63e345qfa3be90264629256@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200811/84 X-Sequence-Number: 31815 On Tue, Nov 11, 2008 at 4:00 PM, Vladimir Sitnikov wrote: > >> Yes, NULL values are not stored in the index, but you may create >> functional index on > > Are you sure NULL values are not stored? btree, gist and bitmap index and > search for NULL values. It's not that they're not stored, it's that before 8.3 pg didn't know how to compare to them I believe. The standard trick was to create a partial index with "where x is null" on the table / column. 8.3 knows how to compare them and doesn't need the partial index.