title: Improve slow query involving date intervals overlapping
body: <p>I have a query with this look:</p>
  <pre><code>select {{AggregateFunctionsInvokations}}
  from {{LargeTable}}
  where {{DateIntervalOverlappingFiltering}}
  </code></pre>
  <ul>
  <li>The table will have more than 100.000.000 rows.</li>
  <li>The target filtering date interval (variable in each issued query) will be, in each case, from 1 day to 10 months.</li>
  </ul>
  <p>My objective is that those kind of queries not take more than 10 seconds, in the worst case. How to achieve it? It's possible?</p>
  <p>Following I include the SQL code of the test case performed.</p>
  <pre><code>create schema tmp3;
  
  create table tmp3.Info1(
      emissionId text not null,
      userId text not null,
      minInstant timestamp not null,  
      maxInstant timestamp not null
  );
  
  insert into tmp3.Info1(
      emissionId,
      userId,
      minInstant,
      maxInstant
  )
  select
      (floor(random() * (4000 - 22 + 1)) + 22)::text,
      (floor(random() * (250000 - 33 + 1)) + 33)::text,
      min(least(X.a, X.b)),
      max(greatest(X.a, X.b))
  from (
      select
          '2015-07-10 20:00:00'::timestamp + random() * ('2016-08-02 16:00:00'::timestamp - '2015-07-10 20:00:00'::timestamp) as a,
          '2015-07-10 20:00:00'::timestamp + random() * ('2016-08-02 16:00:00'::timestamp - '2015-07-10 20:00:00'::timestamp) as b
      from generate_series(1,45000000)
  ) X
  group by 1, 2;
  </code></pre>
  <p>Index needed to ensure to have unique pairs emission+user. (Note: now, for this test, is not needed because the previous &quot;insert&quot; sentence ensure uniqueness, but will be needed for the &quot;normal&quot; operative of my application).</p>
  <pre><code>create unique index Info1_UQ001 on tmp3.Info1 using btree (
      emissionId asc,
      userId asc
  );
  </code></pre>
  <p>Covering indexes bounded to get the below query take the less possible time. Which one will use the Postgres planner?</p>
  <pre><code>create index Info1_IX001 on tmp3.Info1 using btree (minInstant asc, maxInstant asc, userId);
  create index Info1_IX002 on tmp3.Info1 using btree (minInstant asc, maxInstant desc, userId);
  create index Info1_IX003 on tmp3.Info1 using btree (minInstant desc, maxInstant asc, userId);
  create index Info1_IX004 on tmp3.Info1 using btree (minInstant desc, maxInstant desc, userId);
  </code></pre>
  <p>VACUUM reclaims storage occupied by dead tuples.
  ANALYZE collects statistics about the contents of tables in the database [...] Subsequently, the query planner
  uses these statistics to help determine the most efficient execution plans for queries.</p>
  <pre><code>vacuum analyze tmp3.Info1
  </code></pre>
  <p>RESULTS:</p>
  <pre><code>--*
  --Range: 2015-12-10...2016-01-25
  --Execution plan performed: &quot;Seq Scan&quot; over &quot;tmp3.Info1&quot; table.
  --Total execution time: 5 minutes with 16 seconds.
  --*
  --Range: 2015-09-10...2015-09-21
  --Execution plan performed: &quot;Seq Scan&quot; over &quot;tmp3.Info1&quot; table.
  --Total execution time: 2 minutes with 47 seconds
  
  select
      min(extract(epoch from (X.maxInstant - X.minInstant))) as minSessionTime,
      round(avg(extract(epoch from (X.maxInstant - X.minInstant)))) as avgSessionTime,
      max(extract(epoch from (X.maxInstant - X.minInstant))) as maxSessionTime,
      count(distinct X.userId) as numUsers
  from tmp3.Info1 X
  where
      --http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap/325964#325964
      --(StartA &lt;= EndB) and (EndA &gt;= StartB)
      --min: &quot;2015-07-10 20:00:00&quot;
      --max: &quot;2016-08-02 15:59:59.624544&quot;
      --(X.minInstant &lt;= @EndB)
      --and
      --(X.maxInstant &gt;= @StartB)    
      X.minInstant &lt;= '2016-01-25'::timestamp
      and
      X.maxInstant &gt;= '2015-12-10'::timestamp
  </code></pre>