Re: Forcing query to use an index

Micheal,

Issues with your query:
 LEFT OUTER JOIN Customer_Month_Summary AS CMS   ON ( C.ID = CMS.CustomerID    AND CMS.MonthStart = DATE '2003-02-01'
  ) 

Check out the thread: Re: [SQL] OUTER JOIN with filter
in today's list; this relates to your problem.  Then try your query as:
 LEFT OUTER JOIN (SELECT * FROM Customer_Month_Summary    WHRE CMS.MonthStart = DATE '2003-02-01'      ) CMS ON C.ID =
CMS.CustomerID

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'
ORC.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).

Finally:
     OR CMS.Package                             ~* 'kate'

Per the above, this is why Postgres cannot use an index on your table; that is
an unanchored text search which can *only* be indexed using FTS.

--
-Josh BerkusAglio Database SolutionsSan Francisco