From: T E Schmitz <mailreg@numerixtechnology.de>
To: Andrew Sullivan <ajs@crankycanuck.ca>
Cc: pgsql-sql@postgresql.org
Subject: Re: triple self-join crawling
Date: Mon, 19 Mar 2007 16:09:43 +0000
Message-ID: <45FEB5C7.5010406@numerixtechnology.de> (raw)
In-Reply-To: <20070319081228.GA21894@phlogiston.dyndns.org>
References: <45FD9840.4040305@numerixtechnology.de>
<20070319081228.GA21894@phlogiston.dyndns.org>
Andrew Sullivan wrote:
> Define "crawling". Also, please post EXPLAIN and, if feasible,
Total runtime: 191430.537 ms
> EXPLAIN ANALYSE output for your case.
>
> 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:
>>
>>
>>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
QUERY PLAN
GroupAggregate (cost=1372725715.71..1414901439.85 rows=5801 width=56)
(actual time=185248.481..191393.056 rows=5800 loops=1)
-> Sort (cost=1372725715.71..1378750806.73 rows=2410036407
width=56) (actual time=185247.212..187763.008 rows=584203 loops=1)
Sort Key: history.stock, history."day", history.high, history.low
-> Nested Loop (cost=0.00..132245248.41 rows=2410036407
width=56) (actual time=0.124..148136.464 rows=584203 loops=1)
Join Filter: (("inner".stock)::text = ("outer".stock)::text)
-> Nested Loop (cost=0.00..555931.84 rows=3739067
width=53) (actual time=0.087..137531.941 rows=28147 loops=1)
Join Filter: (("outer"."day" >= ("inner"."day" -
7)) AND (("outer".stock)::text = ("inner".stock)::text))
-> Index Scan using history_pkey on history
past_week (cost=0.00..266.62 rows=5801 width=23) (actual
time=0.034..43.147 rows=5801 loops=1)
-> Index Scan using idx_history_day on history
(cost=0.00..57.11 rows=1934 width=34) (actual time=0.021..12.709
rows=2900 loops=5801)
Index Cond: ("outer"."day" < history."day")
-> Index Scan using idx_history_day on history
past_month (cost=0.00..22.32 rows=645 width=23) (actual
time=0.019..0.170 rows=21 loops=28147)
Index Cond: ((past_month."day" < "outer"."day") AND
(past_month."day" >= ("outer"."day" - 30)))
Total runtime: 191430.537 ms
--
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, ajs@crankycanuck.ca
Subject: Re: triple self-join crawling
In-Reply-To: <45FEB5C7.5010406@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