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. > ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
