pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Nachbaur <mike@nachbaur.com>
To: pgsql-sql@postgresql.org
Subject: Date-range LEFT OUTER JOIN not using an index
Date: Wed, 19 Feb 2003 14:55:36 -0800
Message-ID: <42478CFD-445D-11D7-8077-000A27935D5A@nachbaur.com> (raw)

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




Message-ID: <42478CFD-445D-11D7-8077-000A27935D5A@nachbaur.com>
Permalink:  ../42478CFD-445D-11D7-8077-000A27935D5A@nachbaur.com/
Also on:    postgresql.org/message-id/42478CFD-445D-11D7-8077-000A27935D5A@nachbaur.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: mike@nachbaur.com
  Subject: Re: Date-range LEFT OUTER JOIN not using an index
  In-Reply-To: <42478CFD-445D-11D7-8077-000A27935D5A@nachbaur.com>
  References: <42478CFD-445D-11D7-8077-000A27935D5A@nachbaur.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox