title: Simple aggregating query very slow in PostgreSql, any way to improve?
body: <p>HI</p>
  
  <p>I have a table which holds files and their types such as</p>
  
  <pre><code>CREATE TABLE files (
      id          SERIAL PRIMARY KEY, 
      name        VARCHAR(255),
      filetype    VARCHAR(255),
      ...
  );
  </code></pre>
  
  <p>and another table for holding file properties such as</p>
  
  <pre><code>CREATE TABLE properties (
      id          SERIAL PRIMARY KEY, 
      file_id     INTEGER CONSTRAINT fk_files REFERENCES files(id),
      size        INTEGER,
      ... // other property fields
  );
  </code></pre>
  
  <p>The file_id field has an index.</p>
  
  <p>The file table has around 800k lines, and the properties table around 200k (not all files necessarily have/need a properties).</p>
  
  <p>I want to do aggregating queries, for example find the average size and standard deviation for all file types. But it's very slow - around 70 seconds for the latter query. I understand it needs a sequential scan, but still it seems too much.
  Here's the query</p>
  
  <pre><code>SELECT f.filetype, avg(size), stddev(size) FROM files as f, properties as pr 
   WHERE f.id = pr.file_id GROUP BY f.filetype;
  </code></pre>
  
  <p>and the explain</p>
  
  <pre><code> HashAggregate  (cost=140292.20..140293.94 rows=116 width=13) (actual time=74013.621..74013.954 rows=110 loops=1)
     -&gt;  Hash Join  (cost=6780.19..138945.47 rows=179564 width=13) (actual time=1520.104..73156.531 rows=179499 loops=1)
           Hash Cond: (f.id = pr.file_id)
           -&gt;  Seq Scan on files f  (cost=0.00..108365.41 rows=1140941 width=9) (actual time=0.998..62569.628 rows=805270 loops=1)
           -&gt;  Hash  (cost=3658.64..3658.64 rows=179564 width=12) (actual time=1131.053..1131.053 rows=179499 loops=1)
                 -&gt;  Seq Scan on properties pr  (cost=0.00..3658.64 rows=179564 width=12) (actual time=0.753..557.171 rows=179574 loops=1)
  Total runtime: 74014.520 ms
  </code></pre>
  
  <p>Any ideas why it is so slow/how to make it faster?</p>