To: Jean-Luc Lachance <jllachan@nsd.ca>
Cc: Greg Stark <gsstark@MIT.EDU>, josh@agliodbs.com,
	Michael Nachbaur <mike@nachbaur.com>, pgsql-sql@postgresql.org
Subject: Re: Forcing query to use an index
References: <9959CFC2-4DC7-11D7-90E0-000A27935D5A@nachbaur.com>
	<200303031532.47899.josh@agliodbs.com>
	<87d6l8hr18.fsf@stark.dyndns.tv> <3E650416.26C51435@nsd.ca>
In-Reply-To: <3E650416.26C51435@nsd.ca>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 04 Mar 2003 15:10:23 -0500
Message-ID: <87r89mki4w.fsf@stark.dyndns.tv>
Lines: 42
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Jean-Luc Lachance <jllachan@nsd.ca> writes:

> I beg to differ.
> 
> A NULL field means not set.

The best description for what NULL means is "unknown".

> Having to use work around because the database does not index null is
> one thing, but making it a general rule is not.

My natural inclination is to use exactly the representation he used. 
I've done so numerous times in the past. But using NULL sort of means "we
don't know when this account might have been deactivated" which is why it
leads to all these awkward OR clauses in his queries.

I admit using 9999-01-01 as a date gives me the willies. But it does match
with the way the field is used and it produces nice clean index range lookups.

> Having NULL indexed would also speed up things when "is null" is part af
> the query.

No, it wouldn't. Not in his query. His query had 
 "disabled IS NULL OR disabled < ?"
Even if "IS NULL" was indexable this still wouldn't be an indexable clause.

Another option would be to use a functional index. 

create function disabled_as_of(timestamp with time zone) as
 'select coalesce($1,''9999-01-01'')' language sql immutable

Then index disabled_as_of(disabled) and access it with
 "disabled_as_of(disabled) > current_time"

(or perhaps it would be clearer as "NOT disabled_as_of(disabled) < current_time")

Of course all this is just extra busywork to keep the ugly data representation
out of the data model and hidden in the functional index. And it's not really
very well hidden either.

-- 
greg