Re: Forcing query to use an index

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