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 | [EMAIL PROTECTED] Unfortunately reformatting the Internet is a little more painful than reformatting your hard drive when it gets out of whack. --Scott Morris ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
