[SQL] triple self-join crawling

[T E Schmitz]

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