Date-range LEFT OUTER JOIN not using an index - Mailing list pgsql-sql

Hello all,

I am trying to build an SQL query to perform a pretty complicated 
search in my customer database, and my indexes aren't being touched.  
I'd like to use the indexes as much as possible since this search is 
going to be run several hundred times per day, and it needs to be as 
responsive as possible (< 500ms).  This is running on a dual proc PIII 
(soon to be on a dual Xeon 2Ghz).

I have an index that I defined thus:

CREATE INDEX Customer_Month_Summary_Time_idx ON 
Customer_Month_Summary(CustomerID, TimeStart, TimeEnd);

and am trying to perform a join across two tables, like (snippet):
  FROM Customer AS C  LEFT OUTER JOIN Customer_Month_Summary AS CMS    ON ( C.ID = CMS.CustomerID     AND (
CMS.TimeStart>= DATE '2003-02-01'       AND CMS.TimeStart < DATE '2003-02-28' + INTERVAL '1 day 5 
 
minutes'       AND ( CMS.TimeEnd >= DATE '2003-02-28' + INTERVAL '1 day 5 
minutes'          OR CMS.TimeEnd IS NULL           )         )      OR ( CMS.TimeStart IS NULL )       )

When I build a simple query using this snippet (which is a pared-down 
version of my much larger query), and use the query analyzer, this is 
what it gives me:

Nested Loop  (cost=0.00..11698437.67 rows=2666 width=24)  ->  Seq Scan on customer c  (cost=0.00..89.66 rows=2666
width=4) ->  Seq Scan on customer_month_summary cms  (cost=0.00..4192.23 
 
rows=6023 width=20)

I don't understand why it's not using my indexes, but am further 
baffled that by making small changes in my overall query, it'll 
alternately use a sequence scan or an index for one of the joins, but 
not for all of them.  What seems even more strange is a permutation of 
the above query used the index for the customers table, but now it's 
back to using the sequence scan once again.

Essentially I have 3-5 thousand records in these tables, and joining 
them with a sequence scan is absolute murder on my database server.  
I've used PostgreSQL for about 2 years (and Oracle for even longer), 
but this is the most complicated query I've ever written, and am having 
a little difficulty.  My ultimate goal is to left-outer-join to 4 other 
tables to be able to search on additional data if it's available, but 
at this rate, things aren't going so well.

If there are any resources out there on optimizing queries, I'd 
appreciate it.  I have a feeling my overall problem is that I do not 
sufficiently understand how the Postgres query analyzer works, and 
don't realize the significance of the changes I'm making to my 
statement.

Any help you can provide in getting PostgreSQL to use my indexes would 
be much appreciated.

--man
Michael A Nachbaur <mike@nachbaur.com>
http://nachbaur.com/pgpkey.asc