From: Jean-Luc Lachance <jllachan@nsd.ca>
To: Greg Stark <gsstark@mit.edu>
Cc: josh@agliodbs.com, Michael Nachbaur <mike@nachbaur.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Forcing query to use an index
Date: Tue, 04 Mar 2003 16:05:12 -0500
Message-ID: <3E651508.23686E7F@nsd.ca> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jllachan@nsd.ca, gsstark@mit.edu, mike@nachbaur.com
Subject: Re: Forcing query to use an index
In-Reply-To: <3E651508.23686E7F@nsd.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox