On 6/18/10, Stefan Manegold
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).
Current test table: CREATE TABLE test ( source INT, time BIGINT, value0 DOUBLE, value1 DOUBLE, value2 DOUBLE, value3 DOUBLE, value4 DOUBLE, value5 DOUBLE, value6 DOUBLE, value7 DOUBLE, value8 DOUBLE, value9 DOUBLE, CONSTRAINT pkey PRIMARY KEY (source, time) ); One unique tuple for each source/time combination. Currently testing with 30000 sources.
Also, you could profile the query by prefixing it with TRACE and send us the result for analysis.
http://pastebin.com/raw.php?i=6vSze9j7 The trace for the following query: TRACE SELECT (time - (time % 300)) AS timegroup, AVG(value0) AS value0 FROM dbbench WHERE source = 3000 GROUP BY timegroup;
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.
MonetDB server v5.18.5 (64-bit), based on kernel v1.36.5 (64-bit oids) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2010 MonetDB B.V., all rights reserved Visit http://monetdb.cwi.nl/ for further information Found 2.9GiB available memory, 4 available cpu cores Configured for prefix: /usr Libraries: libpcre: 7.8 2008-09-05 (compiled with 7.8) openssl: OpenSSL 0.9.8g 19 Oct 2007 (compiled with OpenSSL 0.9.8g 19 Oct 2007) libxml2: 2.7.5 (compiled with 2.7.5) Compiled by: root@ubuntu-karmic-64 (x86_64-pc-linux-gnu) Compilation: gcc -O2 -Wall -O2 -O6 -fomit-frame-pointer -finline-functions -falign-loops=4 -falign-jumps=4 -falign-functions=4 -fexpensive-optimizations -funroll-loops -frerun-cse-after-loop -frerun-loop-opt -ftree-vectorize Linking : ld -IPA -m elf_x86_64 -Wl,-Bsymbolic-functions