From f02a82b6adad1af75499c4ac7221bbd94e3c4fbf Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri, 6 Apr 2007 22:33:43 +0000
Subject: Make 'col IS NULL' clauses be indexable conditions.

Teodor Sigaev, with some kibitzing from Tom Lane.
---
 doc/src/sgml/catalogs.sgml         |  9 ++++++++-
 doc/src/sgml/indexam.sgml          |  7 +++++--
 doc/src/sgml/indices.sgml          | 14 +++++++++-----
 doc/src/sgml/ref/create_index.sgml |  8 +-------
 4 files changed, 23 insertions(+), 15 deletions(-)

(limited to 'doc/src')

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 492b06de0a2..8243dd8d046 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.149 2007/04/02 03:49:36 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.150 2007/04/06 22:33:41 tgl Exp $ -->
 <!--
  Documentation of the system catalogs, directed toward PostgreSQL developers
  -->
@@ -405,6 +405,13 @@
       <entry>Does the access method support null index entries?</entry>
      </row>
 
+     <row>
+      <entry><structfield>amsearchnulls</structfield></entry>
+      <entry><type>bool</type></entry>
+      <entry></entry>
+      <entry>Does the access method support IS NULL searches?</entry>
+     </row>
+
      <row>
       <entry><structfield>amstorage</structfield></entry>
       <entry><type>bool</type></entry>
diff --git a/doc/src/sgml/indexam.sgml b/doc/src/sgml/indexam.sgml
index 247f7f48cbe..8b246719ccf 100644
--- a/doc/src/sgml/indexam.sgml
+++ b/doc/src/sgml/indexam.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/indexam.sgml,v 2.22 2007/02/22 22:00:22 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/indexam.sgml,v 2.23 2007/04/06 22:33:41 tgl Exp $ -->
 
 <chapter id="indexam">
  <title>Index Access Method Interface Definition</title>
@@ -129,7 +129,10 @@
    It is, however, OK to omit rows where the first indexed column is null.
    Thus, <structfield>amindexnulls</structfield> should be set true only if the
    index access method indexes all rows, including arbitrary combinations of
-   null values.
+   null values.  An index access method that sets
+   <structfield>amindexnulls</structfield> may also set
+   <structfield>amsearchnulls</structfield>, indicating that it supports
+   <literal>IS NULL</> clauses as search conditions.
   </para>
 
  </sect1>
diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
index 0a6defbf629..2c238d34294 100644
--- a/doc/src/sgml/indices.sgml
+++ b/doc/src/sgml/indices.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/indices.sgml,v 1.70 2007/02/14 20:47:15 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/indices.sgml,v 1.71 2007/04/06 22:33:41 tgl Exp $ -->
 
 <chapter id="indexes">
  <title id="indexes-title">Indexes</title>
@@ -147,8 +147,8 @@ CREATE INDEX test1_id_index ON test1 (id);
 
    Constructs equivalent to combinations of these operators, such as
    <literal>BETWEEN</> and <literal>IN</>, can also be implemented with
-   a B-tree index search.  (But note that <literal>IS NULL</> is not
-   equivalent to <literal>=</> and is not indexable.)
+   a B-tree index search.  Also, an <literal>IS NULL</> condition on
+   an index column can be used with a B-tree index.
   </para>
 
   <para>
@@ -180,8 +180,9 @@ CREATE INDEX test1_id_index ON test1 (id);
    Hash indexes can only handle simple equality comparisons.
    The query planner will consider using a hash index whenever an
    indexed column is involved in a comparison using the
-   <literal>=</literal> operator.  The following command is used to
-   create a hash index:
+   <literal>=</literal> operator.  (But hash indexes do not support
+   <literal>IS NULL</> searches.)
+   The following command is used to create a hash index:
 <synopsis>
 CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> USING hash (<replaceable>column</replaceable>);
 </synopsis>
@@ -234,6 +235,8 @@ CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable>
 
    (See <xref linkend="functions-geometry"> for the meaning of
    these operators.)
+   Also, an <literal>IS NULL</> condition on
+   an index column can be used with a GiST index.
    Many other GiST operator
    classes are available in the <literal>contrib</> collection or as separate
    projects.  For more information see <xref linkend="GiST">.
@@ -266,6 +269,7 @@ CREATE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable>
 
    (See <xref linkend="functions-array"> for the meaning of
    these operators.)
+   GIN indexes cannot use <literal>IS NULL</> as a search condition.
    Other GIN operator classes are available in the <literal>contrib</>
    <literal>tsearch2</literal> and <literal>intarray</literal> modules.
    For more information see <xref linkend="GIN">.
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index 69ba5d7a7a5..feebc4c164a 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/create_index.sgml,v 1.61 2007/04/03 22:38:35 momjian Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/create_index.sgml,v 1.62 2007/04/06 22:33:41 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -434,12 +434,6 @@ Indexes:
    to remove an index.
   </para>
 
-  <para>
-   Indexes are not used for <literal>IS NULL</> clauses by default.
-   The best way to use indexes in such cases is to create a partial index
-   using an <literal>IS NULL</> predicate.
-  </para>
-
   <para>
    Prior releases of <productname>PostgreSQL</productname> also had an
    R-tree index method.  This method has been removed because
-- 
cgit v1.2.3