From 6ef77149f7ed29d65096b126d9b062dc110b5b74 Mon Sep 17 00:00:00 2001
From: Bruce Momjian <bruce@momjian.us>
Date: Tue, 20 Apr 2004 01:00:26 +0000
Subject: [PATCH] > >> My question is whether postgres can index null values,
 and if not, do I > >> have to accept a full table scan when locating records.
 > > > > It indexes them, but "is null" is not an indexable operator, so you >
 > can't directly solve the above with a 3-column index.  What you can do > >
 instead is use a partial index, for instance > > > > create index i on
 CUSTOMER.WCCustOrderStatusLog (WCOrderStatusID) > > where Acknowledged is
 null and Processing is null; > > That's a very nifty trick and exactly the
 sort of answer I was after!

Add CREATE INDEX doc mention of using partial indexes for IS NULL
indexing;  idea from Tom.
---
 doc/src/sgml/ref/create_index.sgml | 16 ++++++++++++----
 1 file changed, 12 insertions(+), 4 deletions(-)

diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index abd0987b15..34e12c6a5d 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.44 2003/11/29 19:51:38 pgsql Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/create_index.sgml,v 1.45 2004/04/20 01:00:26 momjian Exp $
 PostgreSQL documentation
 -->
 
@@ -66,7 +66,7 @@ CREATE [ UNIQUE ] INDEX <replaceable class="parameter">name</replaceable> ON <re
     When the <literal>WHERE</literal> clause is present, a
     <firstterm>partial index</firstterm> is created.
     A partial index is an index that contains entries for only a portion of
-    a table, usually a portion that is somehow more interesting than the
+    a table, usually a portion that is more useful for indexing than the
     rest of the table. For example, if you have a table that contains both
     billed and unbilled orders where the unbilled orders take up a small
     fraction of the total table and yet that is an often used section, you
@@ -76,10 +76,18 @@ CREATE [ UNIQUE ] INDEX <replaceable class="parameter">name</replaceable> ON <re
     table.
   </para>
 
+  <para>
+   <literal>NULL</> values are not indexed by default.  The best way
+   to index <literal>NULL</> values is to create a partial index using
+   an <literal>IS NULL</> comparison.  <literal>IS NULL</> is more
+   of a function call than a value comparison, and this is why a partial
+   index works.
+  </para>
+
   <para>
     The expression used in the <literal>WHERE</literal> clause may refer
-    only to columns of the underlying table (but it can use all columns,
-    not only the one(s) being indexed).  Presently, subqueries and
+    only to columns of the underlying table, but it can use all columns,
+    not just the ones being indexed.  Presently, subqueries and
     aggregate expressions are also forbidden in <literal>WHERE</literal>.
     The same restrictions apply to index fields that are expressions.
   </para>
-- 
2.40.0