Re: Indexing null dates

John Sidney-Woollett wrote:
> Tom Lane said:
> > "John Sidney-Woollett" <johnsw(at)wardbrook(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073