Message-ID: <45FD9840.4040305@numerixtechnology.de>
Date: Sun, 18 Mar 2007 19:51:28 +0000
From: T E Schmitz <mailreg@numerixtechnology.de>
Reply-To: mailreg@numerixtechnology.de
User-Agent: Debian Thunderbird 1.0.7 (X11/20051017)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: triple self-join crawling
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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