T E Schmitz wrote:
The following self join of a table containing 5800 records is crawling:
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
Things improved hugely when I changed the JOIN clauses:
LEFT OUTER JOIN history AS past_month ON (past_month.stock =
history.stock AND past_month.day >= (history.day - 30) AND
past_month.day < history.day)
LEFT OUTER JOIN history AS past_week ON (past_week.stock =
past_month.stock AND past_week.day =past_month.day AND past_week.day
>= (history.day - 7))
--
Regards,
Tarlika Elisabeth Schmitz
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
