From: T E Schmitz <mailreg@numerixtechnology.de>
To: pgsql-sql@postgresql.org
Subject: triple self-join crawling
Date: Sun, 18 Mar 2007 19:51:28 +0000
Message-ID: <45FD9840.4040305@numerixtechnology.de> (raw)
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
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: <45FD9840.4040305@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