Indexing null dates

["John Sidney-Woollett" <johnsw@wardbrook.com>]

Hi

I'm building a web app where changes to customer orders are logged in the
following table, and I worried about the time that it will take to locate
records that need further processing/actioning. Here's the table:

create table CUSTOMER.WCCustOrderStatusLog (
  WCCustOrderID      integer,
  WCOrderStatusID    integer,
  -- date/time at which some process acknowledged the status
  -- change, and took the appropriate action, like raising
  -- an e-mail confirmation
  Acknowledged       timestamp,
  -- stamped with now() when the e-mailer process begins to process
  -- the order/email message. When complete Processing goes back
  -- to null and Acknowledged is stamped with now()
  Processing         timestamp,
  -- date status change occured
  LastUpdated        timestamp,
  primary key (WCCustOrderID, WCOrderStatusID)
) without oids;

I need a separate e-mailing process to locate orders (using the above
table) that have WCOrderStatusID = (4,5 or 99) AND a null value for
"Acknowledged" and "Processing"

I have a function which provides the next order number for processing:

CREATE OR REPLACE FUNCTION CUSTOMER.GetNextCustEmailAck(integer) RETURNS
integer AS '
  -- locates the next order number that requires an e-mail confirmation
  -- to be sent - this generally occurs as the order passes from
  -- one status to the next. These status charges are recorded
  -- within the WCCustOrderStatusLog table
DECLARE
  pStatusID     ALIAS FOR $1;

  vCustOrderID  integer := null;
BEGIN
  -- 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;

  if (vCustOrderID is not null) then
    -- mark the record as being processed
    update CUSTOMER.WCCustOrderStatusLog
      set Processing = now()
      where WCCustOrderID = vCustOrderID
      and WCOrderStatusID = pStatusID;
  end if;

  -- return -1 to indicate failure to locate order
  if (vCustOrderID is null) then
    return -1;
  end if;

  -- return the order number
  return vCustOrderID;
END;
' LANGUAGE 'plpgsql';

My question is whether postgres can index null values, and if not, do I
have to accept a full table scan when locating records. Or can I mitigate
this by the use either of date values that signify null, but are non-null
(eg 1-Jan-1970). Or am I better off adding extra flag fields (integer)
which always have a Y/N (1,0) value corresponding to whether the
appropriate date field is null or not, and then use these to locate the
records.

eg

create table CUSTOMER.WCCustOrderStatusLog (
  WCCustOrderID      integer,
  WCOrderStatusID    integer,
  -- New flag field
  AcknowledgedIsNull integer,
  Acknowledged       timestamp,
  -- New flag field
  ProcessingIsNull   integer,
  Processing         timestamp,
  -- date status change occured
  LastUpdated        timestamp,
  primary key (WCCustOrderID, WCOrderStatusID)
) without oids;

Also will adding an index to WCOrderStatusID reduce the cost of the
sequential scan?

Or is there some other strategy that would be better?

Thanks.

John Sidney-Woollett

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend