Re: Forcing query to use an index

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.

> This may make better use of your index, because the planner will have a more 
> accurate estimate of the number of rows returned from the outer join.
> 
> AND:
> 
>    AND ( C.Accountnum                            ~* 'kate'
>       OR C.Firstname                             ~* 'kate'
>       OR C.Lastname                              ~* 'kate'
>       OR C.Organization                          ~* 'kate'
>       OR C.Address                               ~* 'kate'
>       OR C.Postal                                ~* 'kate'
>       OR C.City                                  ~* 'kate'
>       OR EA.Name || '@' || JoinDomain(EA.Domain) ~* 'kate'
> 
> This set of expressions has "seq scan" written all over it.   I hihgly suggest 
> that you try to find a way to turn these into anchored text searches, perhaps 
> using functional indexes on lower(column).

If you really need to find substring matches everywhere you might want to look
into the full text search module in contrib/tsearch. I haven't started using
it yet but I expect I will have to when I get to that part of my project. 

> Finally:
> 
>       OR CMS.Package                             ~* 'kate'

*confusion*. Oooh, Yeah, this one is a big problem. It means it's not clear
which end of the join to start with. Maybe it would be better to separate this
into two separate queries, give the user the option to search for a user
"kate" or a package "kate" but not both simultaneously.

-- 
greg