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