Re: triple self-join crawling

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(at)crankycanuck(dot)ca
Unfortunately reformatting the Internet is a little more painful
than reformatting your hard drive when it gets out of whack.
--Scott Morris