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
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