Re: Indexing null dates - Mailing list pgsql-general

John Sidney-Woollett wrote:
> Tom Lane said:
> > "John Sidney-Woollett" <johnsw@wardbrook.com> writes:
> >> [ needs to make this fast: ]
> >>   -- locate the next order
> >>   select WCCustOrderID into vCustOrderID
> >>   from CUSTOMER.WCCustOrderStatusLog
> >>   where WCOrderStatusID = pStatusID
> >>   and Acknowledged is null
> >>   and Processing is null
> >>   for update
> >>   limit 1;
> >
> >> 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!

Yes, nifty.  CREATE INDEX docs updated with:

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

Full patch attached.  Thanks.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/ref/create_index.sgml
===================================================================
RCS file: /cvsroot/pgsql-server/doc/src/sgml/ref/create_index.sgml,v
retrieving revision 1.44
diff -c -c -r1.44 create_index.sgml
*** doc/src/sgml/ref/create_index.sgml    29 Nov 2003 19:51:38 -0000    1.44
--- doc/src/sgml/ref/create_index.sgml    20 Apr 2004 00:54:29 -0000
***************
*** 66,72 ****
      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
      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
--- 66,72 ----
      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 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
***************
*** 77,85 ****
    </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
      aggregate expressions are also forbidden in <literal>WHERE</literal>.
      The same restrictions apply to index fields that are expressions.
    </para>
--- 77,93 ----
    </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 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>