title: How to improve query performance on a single node in MonetDB?
body: <p>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.</p>
  
  <p>Is this even possible with MonetDB? What could I do to improve the performance?</p>
  
  <p>Detailed description of what I've done so far:</p>
  
  <ol>
  <li><p>Created table:</p>
  
  <pre><code>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);
  </code></pre></li>
  <li><p>Loaded the data:</p>
  
  <pre><code>COPY 1450000000 OFFSET 2 RECORDS INTO tbl FROM 'D:\\es_export\\file.csv'
  USING DELIMITERS ',' NULL AS '' LOCKED;
  </code></pre></li>
  <li><p>Run the query:</p>
  
  <pre><code>SELECT COUNT(DISTINCT id) FROM tbl WHERE a1=22
  AND xb&gt;=143455 AND yb&gt;=90911 AND xa&lt;=143615 AND ya&lt;=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);
  </code></pre></li>
  </ol>
  
  <p>When I run the query for the 1st time it took (<em>14m 52s</em>), 2nd run of the same query took (<em>3m 23s</em>), 3rd consecutive run of the same query took (<em>14s</em>) and a slightly rearranged query took (<em>3m 11s</em>).</p>