X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 234684760E7 for ; Wed, 19 Feb 2003 19:04:14 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 95319-01 for ; Wed, 19 Feb 2003 19:04:03 -0500 (EST) Received: from volt.beyondtheweb.com (unknown [64.141.17.13]) by postgresql.org (Postfix) with ESMTP id 8EAA54760F0 for ; Wed, 19 Feb 2003 17:55:50 -0500 (EST) Received: from nachbaur.com (h64-141-16-15.sunwave.net [64.141.16.15]) by volt.beyondtheweb.com (8.11.6/8.11.6) with ESMTP id h1JMtgB02126 for ; Wed, 19 Feb 2003 14:55:42 -0800 Date: Wed, 19 Feb 2003 14:55:36 -0800 Mime-Version: 1.0 (Apple Message framework v551) Content-Type: text/plain; charset=US-ASCII; format=flowed Subject: Date-range LEFT OUTER JOIN not using an index From: Michael Nachbaur To: pgsql-sql@postgresql.org Content-Transfer-Encoding: 7bit Message-Id: <42478CFD-445D-11D7-8077-000A27935D5A@nachbaur.com> X-Mailer: Apple Mail (2.551) X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/443 X-Sequence-Number: 11841 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 http://nachbaur.com/pgpkey.asc