title: First call of query on big table is surprisingly slow
body: <p>I have a query that feels like it is taking more time then it should be. This only applies on the first query for a given set of parameters, so when cached there is no issue.</p>
  
  <p>I am not sure what to expect, however, given the setup and settings I was hoping someone could shed some light on a few questions and give some insight into what can be done to speed up the query. The table in question is fairly large and Postgres estimates around 155963000 in it (14 GB).</p>
  
  <h3>Query</h3>
  
  <pre><code>    select ts, sum(amp) as total_amp, sum(230 * factor) as wh
      from data_cbm_aggregation_15_min
      where virtual_id in (1818) and ts between '2015-02-01 00:00:00' and '2015-03-31 23:59:59'
      and deleted is null
      group by ts
      order by ts
  </code></pre>
  
  <p>When I started looking into this the query it took around 15 seconds, after some changes I have gotten it to around 10 seconds which still seems long for a simply query like this. Here are the results from <code>explain analyze</code>:  <a href="http://explain.depesz.com/s/97V1" rel="nofollow">http://explain.depesz.com/s/97V1</a>. Note the reason why <code>GroupAggregate</code> returns the same amount of rows is this example only has one <code>virtual_id</code> being used, but there can be more.</p>
  
  <h3>Table and index</h3>
  
  <p>Table being queried, it has values inserted into it every 15 minutes</p>
  
  <pre><code>CREATE TABLE data_cbm_aggregation_15_min (
    virtual_id integer NOT NULL,
    ts timestamp without time zone NOT NULL,
    amp real,
    recs smallint,
    min_amp real,
    max_amp real,
    deleted boolean,
    factor real DEFAULT 0.25,
    min_amp_ts timestamp without time zone,
    max_amp_ts timestamp without time zone
  )
  
  ALTER TABLE data_cbm_aggregation_15_min ALTER COLUMN virtual_id SET STATISTICS 1000;
  ALTER TABLE data_cbm_aggregation_15_min ALTER COLUMN ts SET STATISTICS 1000;
  </code></pre>
  
  <p>The index that is used in the query</p>
  
  <pre><code>CREATE UNIQUE INDEX idx_data_cbm_aggregation_15_min_virtual_id_ts
  ON data_cbm_aggregation_15_min USING btree (virtual_id, ts DESC);
  
  ALTER TABLE data_cbm_aggregation_15_min
  CLUSTER ON idx_data_cbm_aggregation_15_min_virtual_id_ts;
  </code></pre>
  
  <h3>Postgres settings</h3>
  
  <p>Other settings are default.</p>
  
  <pre><code>default_statistics_target = 100 
  maintenance_work_mem = 2GB 
  effective_cache_size = 11GB
  work_mem = 256MB
  shared_buffers = 3840MB
  random_page_cost = 1
  </code></pre>
  
  <h3>What I have tried</h3>
  
  <p>I have been following the Things to try before you post in <a href="https://wiki.postgresql.org/wiki/Slow_Query_Questions" rel="nofollow">https://wiki.postgresql.org/wiki/Slow_Query_Questions</a> and the results in a bit more detail were as follows:</p>
  
  <ol>
  <li>Fiddling with the Postgres settings, mostly lowering <code>random_page_cost</code> since the index scan, while it seems not too special is miles ahead of the bitmap heap scan it tried doing instead when the <code>random_page_cost</code> was higher.</li>
  <li>Adding increased statistics to the <code>virtual_id</code> and <code>ts</code> columns which the index and <code>WHERE</code> conditions are based on. The query planner's estimated row count was much closer to the actual row count after changing this.</li>
  <li>Clustering on <code>the idx_data_cbm_aggregation_15_min_virtual_id_ts</code> index did not seem to change much, not that I noticed.</li>
  <li>Running <code>VACUUM</code> manually did not change much, I am already running autovacuum so this was no surprise.</li>
  <li>Running <code>REINDEX</code> on the index shrunk it considerably (by almost 50%!) but it did not improve the speed by much.</li>
  </ol>