Message-ID: <3E650416.26C51435@nsd.ca>
Date: Tue, 04 Mar 2003 14:52:54 -0500
From: Jean-Luc Lachance <jllachan@nsd.ca>
MIME-Version: 1.0
To: Greg Stark <gsstark@mit.edu>
Cc: josh@agliodbs.com, Michael Nachbaur <mike@nachbaur.com>,
	pgsql-sql@postgresql.org
Subject: Re: Forcing query to use an index
References: <9959CFC2-4DC7-11D7-90E0-000A27935D5A@nachbaur.com>
	<200303031532.47899.josh@agliodbs.com> <87d6l8hr18.fsf@stark.dyndns.tv>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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.
>