Re: Forcing query to use an index

Greg Stark wrote:
>
> Jean-Luc Lachance <jllachan(at)nsd(dot)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