Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 633B16502E9
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 11 Nov 2008 19:00:19 -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 79489-07
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue, 11 Nov 2008 19:00:15 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from rn-out-0910.google.com (rn-out-0910.google.com
 [64.233.170.189])
	by mail.postgresql.org (Postfix) with ESMTP id 24187650252
	for <pgsql-performance@postgresql.org>;
	Tue, 11 Nov 2008 19:00:15 -0400 (AST)
Received: by rn-out-0910.google.com with SMTP id k40so150570rnd.0
	for <pgsql-performance@postgresql.org>;
	Tue, 11 Nov 2008 15:00:14 -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:references;
	bh=tObBZstDpSzYwkzWl8yaBS9dHDvPeGIrWbqAjbkRgpk=;
	b=wqYXNB2ujdMTVWQ0lt0+odA2E5wxpbQTG2d4Z9nj959/jXWNm61eHNf19FKN/suOgN
	sh5dUeM8zuvlWX8hX3ZGAl53bwgOdwb/pIZWjaT4E73jL0gZLGsDJHW31FjlkHbWNTf9
	xWnjk72sciHNnoL93vT7JIOvEMbFJp3RKsSI4=
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:references;
	b=aRBPFmyeoxyYqtcu+Wld4e+ouWsPfCNUqLarQ/jM0ytH8O+exQvZu7ylGsOY4Yw4SZ
	TTgg5Spru5NldbpdoqTriTgoHPW6DJIy+v/yHzy9CEdC8POiDwEQ/cbHUeOvmhrQ6pix
	mKrAH5AIUx26+UJ1+3KSnrHg1/L7lXMFFIW78=
Received: by 10.142.177.7 with SMTP id z7mr3210272wfe.132.1226444413524;
	Tue, 11 Nov 2008 15:00:13 -0800 (PST)
Received: by 10.142.79.21 with HTTP; Tue, 11 Nov 2008 15:00:13 -0800 (PST)
Message-ID: <1d709ecc0811111500i3c63e345qfa3be90264629256@mail.gmail.com>
Date: Tue, 11 Nov 2008 15:00:13 -0800
From: "Vladimir Sitnikov" <sitnikov.vladimir@gmail.com>
To: "Tomas Vondra" <tv@fuzzy.cz>
Subject: Re: Using index for IS NULL query
Cc: Andrus <kobruleht2@hot.ee>, pgsql-performance@postgresql.org
In-Reply-To: <4919F2C6.4000303@fuzzy.cz>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_6405_12946071.1226444413500"
References: <gfcprb$m40$1@news.hub.org> <4919F2C6.4000303@fuzzy.cz>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5
	tests=HTML_MESSAGE=0.001
X-Spam-Level: 
X-Archive-Number: 200811/83
X-Sequence-Number: 31814

------=_Part_6405_12946071.1226444413500
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

> 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.

select amname, amindexnulls, amsearchnulls from pg_am;

 amname | amindexnulls | amsearchnulls
--------+--------------+---------------
 btree  | t            | t
 hash   | f            | f
 gist   | t            | t
 gin    | f            | f
 bitmap | t            | t
(5 rows)


Sincerely yours,
Vladimir Sitnikov

------=_Part_6405_12946071.1226444413500
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<br><div class="gmail_quote"><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">Yes, NULL values are not stored in the index, but you may create functional index on<br>

</blockquote><div>Are you sure NULL values are not stored? btree, gist and bitmap index and search for NULL values.<br><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">select amname, amindexnulls, amsearchnulls from pg_am;</span><br>
<br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">&nbsp;amname | amindexnulls | amsearchnulls </span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">--------+--------------+---------------</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">&nbsp;btree&nbsp; | <span style="background-color: rgb(255, 204, 0);">t</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | <span style="background-color: rgb(255, 204, 0);">t</span></span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">&nbsp;hash&nbsp;&nbsp; | f&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | f</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">&nbsp;gist&nbsp;&nbsp; | <span style="background-color: rgb(255, 204, 0);">t</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | <span style="background-color: rgb(255, 204, 0);">t</span></span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">&nbsp;gin&nbsp;&nbsp;&nbsp; | f&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | f</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;">&nbsp;bitmap | <span style="background-color: rgb(255, 204, 0);">t</span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | <span style="background-color: rgb(255, 204, 0);">t</span></span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">(5 rows)</span><br style="font-family: courier new,monospace;"><br><br>Sincerely yours,<br>Vladimir Sitnikov<br></div></div>

------=_Part_6405_12946071.1226444413500--