Dotaz: Postgresql: Prosím pomoct s optimalizací dotazu

[Miroslav Pavelka]

Dobrý den, Mám tabulku minutových časových řad potřebuji z ní udělat víceminutové (15 minutové či hodinové časové řady)

Tabulky z minutovými časovými řadami mají následující strukturu:

CREATE TABLE admiralmarkets.m1_chfjpy
(
  datetime timestamp without time zone NOT NULL,
  open double precision,
  high double precision,
  low double precision,
  close double precision,
  volume integer,
  CONSTRAINT m1_chfjpy_pkey PRIMARY KEY (datetime)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE admiralmarkets.m1_chfjpy
  OWNER TO matlab;

No a ten můj dotaz který jsem vymyslel je následující:

SELECT
	w.time_in_hours,
	avg( case when r1=1 then open end ) as open,
	max(m2) as High,
	min(m1) as Low,
	avg( case when r2=1 then close  end) as Close,
	sum(w.volume) as volume,
	count(*) as bar_count,
	max(case when r1=1 then datetime end) as time_open,
	max(case when m2=High then datetime end) as time_high,
	max(case when m1=Low then datetime end) as time_low,
	max(case when r2=1 then datetime end ) as time_close
	--w.time_in_hours+1*interval '1 hour' as time_end
	--- skewness--
	--(sqrt(count(*)*(count(*)-1)))/(count(*)-2) as s0
FROM
	(
SELECT
datetime,
	High,
	Low,
	Open,
	Close,
	volume,
	datetime- extract (minute
FROM
	datetime) * INTERVAL '1 minute' as time_in_hours,
	
	min(low) over (partition BY datetime-extract (minute
FROM
	datetime) * INTERVAL '1 minute' ) as m1,
	max(high) over (partition BY datetime-extract (minute
FROM
	datetime) * INTERVAL '1 minute' ) as m2,
	rank() over (partition BY datetime-extract (minute
FROM
	datetime) * INTERVAL '1 minute'
ORDER BY
	datetime) as r1,
	rank() over (partition BY datetime-extract (minute
FROM
	datetime) * INTERVAL '1 minute'
ORDER BY
	datetime desc ) as r2
FROM
	admiralmarkets.m1_usdjpy
	
	) as w
GROUP BY 1 

Explain analyse dalo následující výsledek:

"GroupAggregate  (cost=576914.91..659539.99 rows=200 width=84) (actual time=4357.579..6267.656 rows=17391 loops=1)"
"  ->  WindowAgg  (cost=576914.91..605316.25 rows=1032776 width=44) (actual time=4357.530..5175.552 rows=1032776 loops=1)"
"        ->  Sort  (cost=576914.91..579496.85 rows=1032776 width=44) (actual time=4357.523..4566.744 rows=1032776 loops=1)"
"              Sort Key: ((m1_usdjpy.datetime - (date_part('minute'::text, m1_usdjpy.datetime) * '00:01:00'::interval))), m1_usdjpy.datetime"
"              Sort Method: external sort  Disk: 90824kB"
"              ->  WindowAgg  (cost=381803.08..410204.42 rows=1032776 width=44) (actual time=2841.311..3573.644 rows=1032776 loops=1)"
"                    ->  Sort  (cost=381803.08..384385.02 rows=1032776 width=44) (actual time=2841.305..3024.597 rows=1032776 loops=1)"
"                          Sort Key: ((m1_usdjpy.datetime - (date_part('minute'::text, m1_usdjpy.datetime) * '00:01:00'::interval))), m1_usdjpy.datetime"
"                          Sort Method: external sort  Disk: 82752kB"
"                          ->  WindowAgg  (cost=186691.25..215092.59 rows=1032776 width=44) (actual time=1334.691..2102.177 rows=1032776 loops=1)"
"                                ->  Sort  (cost=186691.25..189273.19 rows=1032776 width=44) (actual time=1334.668..1564.490 rows=1032776 loops=1)"
"                                      Sort Key: ((m1_usdjpy.datetime - (date_part('minute'::text, m1_usdjpy.datetime) * '00:01:00'::interval)))"
"                                      Sort Method: external merge  Disk: 62568kB"
"                                      ->  Seq Scan on m1_usdjpy  (cost=0.00..19980.76 rows=1032776 width=44) (actual time=0.039..534.610 rows=1032776 loops=1)"
"Total runtime: 6313.651 ms"

Nejsem databázový expert.., takže kdybyste to někdo uměl zjednodušit.. nebo vymyslet něco chytřejšího... Tak díky moc. M.P.

Odpovědi

[AraxoN]

Nemáš tam WHERE - to vždy potrebuješ výcuc zo všetkých dát? Milión záznamov (rows=1032776) po jednej minúte, to sú skoro 2 roky. Obvykle sa zobrazuje pár hodín, deň, možno mesiac. Na zobrazovanie dlhších období by som si spravil osobitné tabuľky - napríklad tabuľka po hodinách a ďalšia s dennými hodnotami (high, low, open, close, volume). Ak neprepisuješ históriu, tak Ti stačí len cronom pridávať sumárny záznam po skončení hodiny a po skončení dňa. Ten denný sumár môže byť kľudne vyrátaný z hodinových a hneď namiesto 1440 záznamov spracuvávaš len 24.

[Sid]

Vydal by som sa troska inym smerom : 1) Bud si robit agregacie rovno pri pridavani dat do nejakych dalsich tabuliek (tj definovat tabulky pre zadane rozsahy 15,1hod,atd) 2) v zavislosti od velkosti dat pripadne pouzit nejaku historical database napr. http://opentsdb.net/ (ale mozno je to ako ist s kanonom na vrabca)

[okbob]

Nevím jestli jsem pochopil zadání - ale tady není potřeba jít do window funkcí. Pokud potřebuji agregaci po 15 minutách, tak převedu čas na 15 min intervaly a podle nich agreguji

Pomůžu si funkcí, která mi timestamp zaokrouhluje na 15min:

CREATE OR REPLACE FUNCTION trunc_15min(timestamp) returns timestamp as $$
select to_timestamp(extract(epoch from $1)::integer/(15*60)*(15*60));
$$ language sql;

A pak agreguji obvyklým způsobem:

postgres=# select * from foo;
             t              | v  
----------------------------+----
 2015-03-24 17:32:19.318676 | 10
 2015-03-24 17:32:21.266594 | 20
 2015-03-24 17:32:23.55099  | 30
 2015-03-24 17:47:34.406007 | 30
 2015-03-24 18:02:38.235613 | 30
(5 rows)

Time: 0.409 ms
postgres=# select trunc_15min(t), sum(v), avg(v) from foo group by 1;
     trunc_15min     | sum |         avg         
---------------------+-----+---------------------
 2015-03-24 18:00:00 |  30 | 30.0000000000000000
 2015-03-24 17:30:00 |  60 | 20.0000000000000000
 2015-03-24 17:45:00 |  30 | 30.0000000000000000
(3 rows)

Time: 1.789 ms

[Miroslav Pavelka]

Obávám se že bez windows funkcí to nepůjde. Je pravda že minimum a maximum můžu získat prostým groub by, ale potřebuji ještě první hodnotu (open) a poslední hodnotu (close) v daném časovém intervalu.