From: Greg Stark <gsstark@mit.edu>
To: Jean-Luc Lachance <jllachan@nsd.ca>
Cc: 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: 04 Mar 2003 15:10:23 -0500
Message-ID: <87r89mki4w.fsf@stark.dyndns.tv> (raw)
In-Reply-To: <3E650416.26C51435@nsd.ca>
References: <9959CFC2-4DC7-11D7-90E0-000A27935D5A@nachbaur.com>
<200303031532.47899.josh@agliodbs.com>
<87d6l8hr18.fsf@stark.dyndns.tv>
<3E650416.26C51435@nsd.ca>
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
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: gsstark@mit.edu, jllachan@nsd.ca, mike@nachbaur.com
Subject: Re: Forcing query to use an index
In-Reply-To: <87r89mki4w.fsf@stark.dyndns.tv>
* 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