Message-ID: <45FE4848.5040407@numerixtechnology.de>
Date: Mon, 19 Mar 2007 08:22:32 +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
CC: pgsql-sql@postgresql.org
Subject: Re: triple self-join crawling
References: <45FD9840.4040305@numerixtechnology.de>
In-Reply-To: <45FD9840.4040305@numerixtechnology.de>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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