Date: Mon, 19 Mar 2007 04:12:28 -0400
From: Andrew Sullivan <ajs@crankycanuck.ca>
To: pgsql-sql@postgresql.org
Subject: Re: triple self-join crawling
Message-ID: <20070319081228.GA21894@phlogiston.dyndns.org>
References: <45FD9840.4040305@numerixtechnology.de>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <45FD9840.4040305@numerixtechnology.de>
User-Agent: Mutt/1.5.9i

Define "crawling".  Also, please post EXPLAIN and, if feasible,
EXPLAIN ANALYSE output for your case.

A

On Sun, Mar 18, 2007 at 07:51:28PM +0000, T E Schmitz wrote:
> The following self join of a table containing 5800 records is crawling:
> 
> CREATE TABLE history
> (
>     stock VARCHAR(30) NOT NULL,
>     day date NOT NULL,
>     open NUMERIC (6,1) NOT NULL,
>     high NUMERIC (6,1) NOT NULL,
>     low NUMERIC (6,1) NOT NULL,
>     close NUMERIC (6,1) NOT NULL,
>     volume NUMERIC (12) NOT NULL,
>     PRIMARY KEY (stock,day)
> );
> 
> 
> SELECT
>     history.stock, history.day, history.high, history.low,
>     MAX(past_week.high) AS week_high,
>     MAX(past_month.high) AS month_high
> FROM history
> INNER JOIN history AS past_month ON (past_month.stock = history.stock 
> AND past_month.day < history.day AND past_month.day >= (history.day - 30))
> INNER JOIN history AS past_week  ON (past_week.stock   = 
> past_month.stock AND past_week.day < history.day AND past_week.day >= 
> (history.day - 7))
> GROUP BY  history.stock, history.day, history.high, history.low
> ORDER BY history.stock, history.day DESC
> 
> 
> How can I speed this up?
> 
> 
> -- 
> 
> 
> Regards,
> 
> Tarlika Elisabeth Schmitz
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>               http://www.postgresql.org/docs/faq

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
Unfortunately reformatting the Internet is a little more painful 
than reformatting your hard drive when it gets out of whack.
		--Scott Morris