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>
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
