How to improve query performance on a single node in MonetDB?

4 Oct 2017

      Hello list,

recently I came across MonetDB as a columnar database and I have some
issues with getting it perform well. I posted my question on StackOverflow
https://stackoverflow.com/questions/46558842/how-to-improve-query-performanc...,
idealy post your answers there but, mailing it to the list is fine as well.

Thank you

Tomas
...
TL;DR
I've installed the latest (MonetDB 5 server v11v.27.5 "Jul2017-SP1") on
Windows 2012 Server and I'm trying to query large table 1,4 billion rows in
a reasonable time 2-3s.
Is this even possible with MonetDB? What could I do to improve the
performance?
Detailed description of what I've done so far:
Created table:
CREATE TABLE t939ba ( id INT, xa INT, xb INT, ya INT, yb INT, a1 TINYINT,
a2 TINYINT, a3 TINYINT, a4 TINYINT, a5 TINYINT, a6 TINYINT, a7 TINYINT, a8
TINYINT, a9 TINYINT);
Loaded the data:
COPY 1450000000 OFFSET 2 RECORDS INTO tbl FROM 'D:\\es_export\\file.csv'
USING DELIMITERS ',' NULL AS '' LOCKED;
Run the query:
SELECT COUNT(DISTINCT id) FROM tbl WHERE a1=22
AND xb>=143455 AND yb>=90911 AND xa<=143615 AND ya<=91007
AND a2 IN (2, 3, 4) AND a3 IN (0, 1, 2, 3, 4) AND a4 IN (0, 1, 2)
AND a5 IN (-1, 1, 2, 3, 4, 5, 6, 7) AND a6 IN (-1, 11, 12, 13, 14);
It took *14m 52s* to get the result.

Tomas Repik | Instarea

Lefteris

Stefan Manegold

tags

participants (3)