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
