Message-ID: <3E651508.23686E7F@nsd.ca>
Date: Tue, 04 Mar 2003 16:05:12 -0500
From: Jean-Luc Lachance <jllachan@nsd.ca>
MIME-Version: 1.0
To: Greg Stark <gsstark@mit.edu>
Cc: 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>
	<87r89mki4w.fsf@stark.dyndns.tv>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Greg Stark wrote:
> 
> 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".

I agree.

> 
> > 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.

I know it is quite far in the futur, but it remind me too much of he Y2K
problem.
One of my customers buried in their code 99-12-31 as an undefined
date...

> 
> > 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.

What? 

Select f from t where f is null or f < '2003-03-04';
is the same as
Select f from t where f is null
union
select f from t where f > '2003-03-03';

Surely the optimizer will know to use the index. Will it not???

JLL

> 
> 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