Jak nejlépe řešit agregaci databáze na PostgreSQL 8?

Honza Pazdziora adelton na fi.muni.cz
Pondělí Duben 24 15:51:59 CEST 2006


On Sun, Apr 23, 2006 at 10:15:50PM +0200, Tomáš Tomáš wrote:
> 
> Data se archivuji v Dlouhých časových řadách (v současnosti cca 5let zpět) a tak je požadavek data starší 30 dnu agregovat na hodinový průměr a ukládat do tabulky logh,
> data starší 180 dni agregovat na 24 hodinový průměr a ukládat do tabulky logd,
> 
> Doposud to mam napsané jako funkci, která se pouští v cronu 1x za 24hodin
> Zjisti jaká jsou nejstarší data v log, provede sum pro nejstarší časový údaj + 3600 výsledek insertne do logh a smaže z log
> 
> Jednak je to docela pomale a pak mi to nepřijde jako dostatečně databázové řešení, jak by jste to řešili vy?
> 

A je to pomalé proto, že se to pouští z cronu, nebo proto, že
jednotlivé selecty mají neoptimální výpočet?

> begin
> 
> while x < 24 loop
> 
> SELECT into t_min,t_max  min(that_time),max(that_time) from log;
> 
> IF (t_min+2592000)<=t_max THEN
> 
> SELECT into th  extract(epoch from date_trunc('hour',t_min::reltime));
> 
> INSERT INTO log_h SELECT name,SUM(mereni_d) AS mereni_d,
> SUM(mereni_u) AS mereni_u,th+3600 AS that_time FROM log WHERE
> that_time>=th AND that_time<=th+3600 GROUP BY name;

Proč tady děláte 24 selectů, když se to dá napsat jedním selectem
s group by to_char(cas, 'YYYY-MM-DD HH24') nebo něčím podobným, pro
that_time < půlnoc z dneška?

> DELETE FROM log WHERE that_time>=th AND that_time<=th+3599;

Tady je mi podezřelé to <= th + 3599, když nahoře se dělá <= th +
3600, ale podrobně jsem ten kód nezkoumal.

> SELECT into t_min,t_max  min(that_time),max(that_time) from log_h;
> 
> IF (t_min+5184000)<=t_max THEN
> 
> SELECT into th  extract(epoch from date_trunc('day',t_min::reltime));
> --RAISE NOTICE 'test%',th;
> INSERT INTO log_d SELECT name,SUM(mereni_d) AS mereni_d,
> SUM(mereni_u) AS mereni_u,th+86399 AS that_time FROM log_h WHERE
> that_time>=th AND that_time<=th+86399 GROUP BY name;
> 
> DELETE FROM log_h WHERE that_time>=th AND that_time<=th+86399;

Tady je mi podezřelé, že se agregace dnů dělá stejněkrát (24) jako
agregace přes jednotlivé záznamy, ale samozřejmě i zde je
nejrozumnější to sjet pomocí group by name,
to_char(cas, 'YYYY-MM-DD') a nedělat to v cyklu.

Když nic jiného, tak ušetříte počet execute.

-- 
------------------------------------------------------------------------
 Honza Pazdziora | adelton na fi.muni.cz | http://www.fi.muni.cz/~adelton/
 .project: Perl, mod_perl, DBI, Oracle, large Web systems, XML/XSL, ...
		Only self-confident people can be simple.


Další informace o konferenci Databases