Hi Gustav, it would be great if you could provide us with the detailed schema of your table (e.g., your complete CREATE TABLE statement) --- or at least the columns in voved in this query, and (if known) some basic properties of the data (e.g., number of tuples and whether attribute values are sorted or unique). Also, you could profile the query by prefixing it with TRACE and send us the result for analysis. Finally, whih version of MonetDB are you using and on what kind of platform (OS & HW) --- the output of `mserver5 --version` should answer these quetions. Thanks, Stefan On Fri, Jun 18, 2010 at 10:56:21AM +0200, Gustav Koller wrote:
Hello,
I've some time series data (time + a few value columns), now I want to generate average values for each hour.
Currently I'm using the following query:
SELECT (time - (time % 3600)) AS timegroup, AVG(value0) AS avgvalue0 FROM dbbench WHERE time > x AND time <= y GROUP BY timegroup;
I've a few GB of data and the query is running for several seconds. Does anyone have any recommendations on how to improve the query for faster execution time?
Greetings Gustav
------------------------------------------------------------------------------ ThinkGeek and WIRED's GeekDad team up for the Ultimate GeekDad Father's Day Giveaway. ONE MASSIVE PRIZE to the lucky parental unit. See the prize list and enter to win: http://p.sf.net/sfu/thinkgeek-promo _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
-- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4199 |