DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=obu1pf3LwOdjtaJGCugmQc4TUWsl69yYPdgoKbSF30d8JUWgOfHUzOMSV2rtGC7fHq5WStCQeL1OdfDDiMBvQyiwkWIYP2LG7dU4mAQXGsfP+uF9KKdZmPM+uOVzC/6rV8CG7oVs5AOCmJkL4thZtNw6NS1O2qVb4pJ5dYtWx2c=
	;
Message-ID: <20060825204057.31139.qmail@web31810.mail.mud.yahoo.com>
Date: Fri, 25 Aug 2006 13:40:56 -0700 (PDT)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: Best way to do this query..
To: Henry Ortega <juandelacruz@gmail.com>, pgsql-sql@postgresql.org
In-Reply-To: <2bffcc330608251316g6ed00710u6e43b243800c6087@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

> I have the following:
> 
> name      effective         tstamp                                   rate
> John       01-01-2006     2005-12-07 13:39:07.614945    115.00
> John       01-16-2006     2006-01-07 13:39:07.614945    125.00
> John       01-16-2006     2006-01-09 15:13:04.416935    1885.00
> 
> I want the output to be:
> name      effective         end_date        rate
> John       01-01-2006     01-15-2006    115.00
> John       01-16-2006                         1885.00
> 
> What is the best way to do this? This is on a huge table and what I
> have right now is quite slow. Any ideas?

I would assume that your data does not change after it is logged.  If this is the case, maybe this
part of your data model would actually be a good canidate for an OLAP data model.

If this is not the direction your are enterested in moving,  you could also reduce your query
processing time by limiting the data ranges for your existing query to something for reasonable.

Regards,

Richard Broersma Jr.