From: T E Schmitz <mailreg@numerixtechnology.de>
Cc: pgsql-sql@postgresql.org
Subject: Re: triple self-join crawling
Date: Mon, 19 Mar 2007 08:22:32 +0000
Message-ID: <45FE4848.5040407@numerixtechnology.de> (raw)
In-Reply-To: <45FD9840.4040305@numerixtechnology.de>
References: <45FD9840.4040305@numerixtechnology.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mailreg@numerixtechnology.de
Subject: Re: triple self-join crawling
In-Reply-To: <45FE4848.5040407@numerixtechnology.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox