title: Calculate number of concurrent events in SQL
body: <p>I have a table that holds phone calls, with the following fields:</p>
  
  <ul>
  <li>ID</li>
  <li>STARTTIME</li>
  <li>ENDTIME</li>
  <li>STATUS</li>
  <li>CALL_FROM</li>
  <li>CALL_TO</li>
  </ul>
  
  <p>There are 2,9 million records loaded into a local PostgreSQL database. I added indexes on ID (unique index), starttime and endtime.</p>
  
  <p>Searching on stackoverflow, I found some useful SQL and modified it to what I think logically should work. The problem is that the query runs for many hours and never returns:</p>
  
  <pre><code>SELECT T1.sid, count(*) as CountSimultaneous
  FROM calls_nov T1, calls_nov T2
  WHERE
       T1.StartTime between T2.StartTime and T2.EndTime
       and T1.StartTime between '2011-11-02' and '2011-11-03'
  GROUP BY
       T1.sid
  ORDER BY CountSimultaneous DESC;
  </code></pre>
  
  <p>Can someone please either suggest a way to fix the query/index so that it actually works or suggest another way to calculate concurrent calls?</p>
  
  <p>EDIT:</p>
  
  <p>Explain plan:</p>
  
  <pre><code>Sort  (cost=11796758237.81..11796758679.47 rows=176663 width=35)
    Sort Key: (count(*))
    -&gt;  GroupAggregate  (cost=0.00..11796738007.56 rows=176663 width=35)
          -&gt;  Nested Loop  (cost=0.00..11511290152.45 rows=57089217697 width=35)
  </code></pre>
  
  <p>Table creation script:</p>
  
  <pre><code>CREATE TABLE calls_nov (
    sid varchar,
    starttime timestamp, 
    endtime timestamp, 
    call_to varchar, 
    call_from varchar, 
    status varchar);
  </code></pre>
  
  <p>Index creation:</p>
  
  <pre><code>CREATE UNIQUE INDEX sid_unique_index on calls_nov (sid);
  
  CREATE INDEX starttime_index on calls_nov (starttime);
  
  CREATE INDEX endtime_index on calls_nov (endtime);
  </code></pre>