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

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 Berkus
Aglio Database Solutions
San Francisco