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 14:52:54 -0500
Message-ID: <3E650416.26C51435@nsd.ca> (raw)
References: <9959CFC2-4DC7-11D7-90E0-000A27935D5A@nachbaur.com>
<200303031532.47899.josh@agliodbs.com>
<87d6l8hr18.fsf@stark.dyndns.tv>
I beg to differ.
A NULL field means not set.
Having to use work around because the database does not index null is
one thing, but making it a general rule is not.
Having NULL indexed would also speed up things when "is null" is part af
the query.
Until then...
JLL
Greg Stark wrote:
>
> One suggestion I'll make about your data model -- I'm not sure it would
> actually help this query, but might help elsewhere:
>
> WHERE ( C.Disabled > '2003-02-28'
> OR C.Disabled IS NULL
> )
>
> Don't use NULL values like this. Most databases don't index NULLs (Oracle) or
> even if they do, don't make "IS NULL" an indexable operation (postgres).
> There's been some talk of changing this in postgres but even then, it wouldn't
> be able to use an index for an OR clause like this.
>
> If you used a very large date, like 9999-01-01 as your "not deactivated" value
> then the constraint would be C.disabled > '2003-02-28' and postgres could use
> an index on "disabled".
>
> Alternatively if you have a disabled_flag and disabled_date then you could
> have an index on disabled_flag,disabled_date and uhm, there should be a way to
> use that index though I'm not seeing it right now.
>
> This won't matter at first when 99% of your customers are active. And ideally
> in this query you find some way to use an index to find "kate" rather than
> doing a fully table scan. But later when 90% of the clients are disabled, then
> in a bigger batch job where you actually want to process every active record
> it could prevent postgres from having to dig through a table full of old
> inactive records.
>
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: <3E650416.26C51435@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