Indexing null dates

From: "John Sidney-Woollett" <johnsw(at)wardbrook(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Indexing null dates
Date: 2004-04-16 13:51:09
Message-ID: 2827.192.168.0.64.1082123469.squirrel@mercury.wardbrook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2004-04-16 14:13:41 Re: PLpgSQL-Problem
Previous Message Claudio Cicali 2004-04-16 12:38:33 Mixed UTF8 / Latin1 database