Re: Forcing query to use an index

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