title: Is there anyway to improve this query computational cost?
body: <p>I'm using PostgreSQL 9.6.1 on my own machine for this database.</p>
  
  <p>I have this database of transactions. Entire database is around 100 million rows x 30 columns. Transactions span over the past four years.</p>
  
  <p>For this query, there are three relevant columns:</p>
  
  <ul>
  <li>transaction timestamp which is rounded to the nearest 15 minutes</li>
  <li>vendor ID</li>
  <li>transaction amount (revenue)</li>
  </ul>
  
  <p>I am interested in returning output of four columns such as the below image (sorry for the link - don't have enough rep to imbed images yet:</p>
  
  <p><a href="https://i.stack.imgur.com/f6adT.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/f6adT.png" alt="enter image description here"></a></p>
  
  <p>The output is the count of transactions during that specific timestamp, the count of unique active vendors in the past 60 minutes, and the hourly revenue over the past 60 minutes.</p>
  
  <p>Below is the code I used to try to accomplish this. </p>
  
  <pre><code>SELECT transaction_timestamp,
     COUNT(transaction_timestamp) AS "transaction_timestamp",
     (SELECT COUNT(DISTINCT vendor_id)
      FROM transactions_db
      WHERE transaction_timestamp BETWEEN t.transaction_timestamp - INTERVAL '60 MINUTES' AND t.transaction_timestamp
     ) AS "lag_60_transaction_count",
     (SELECT SUM(revenue) / COUNT(DISTINCT vendor_id)
      FROM transactions_db
      WHERE transaction_timestamp BETWEEN t.transaction_timestamp - INTERVAL '60 MINUTES' AND t.transaction_timestamp
     ) AS "rolling_hourly_rate"
  FROM transactions_db t
  GROUP BY transaction_timestamp
  ORDER BY transaction_timestamp;
  </code></pre>
  
  <p>Here is the EXPLAIN output:</p>
  
  <pre><code> GroupAggregate  (cost=21989857.85..681893649752.90 rows=108423 width=56)
     Group Key: t.transaction_timestamp
     -&gt;  Sort  (cost=21989857.85..22252785.49 rows=105171056 width=8)
           Sort Key: t.transaction_timestamp
           -&gt;  Index Only Scan using timestamp_vendor_revenue_idx on transactions_db t  (cost=0.57..3663118.41 rows=105171056 width=8)
     SubPlan 1
       -&gt;  Aggregate  (cost=3143836.32..3143836.33 rows=1 width=8)
             -&gt;  Index Only Scan using timestamp_vendor_revenue_idx on transactions_db  (cost=0.57..3142521.68 rows=525855 width=4)
                   Index Cond: ((transaction_timestamp &gt;= (t.transaction_timestamp - '01:00:00'::interval)) AND (transaction_timestamp &lt;= t.transaction_timestamp))
     SubPlan 2
       -&gt;  Aggregate  (cost=3145150.96..3145150.97 rows=1 width=32)
             -&gt;  Index Only Scan using timestamp_vendor_revenue_idx on transactions_db transactions_db_1  (cost=0.57..3142521.68 rows=525855 width=10)
                   Index Cond: ((transaction_timestamp &gt;= (t.transaction_timestamp - '01:00:00'::interval)) AND (transaction_timestamp &lt;= t.transaction_timestamp))
  </code></pre>
  
  <p>That being said, this query is taking an impossibly long time to run (8+ hours - ran it overnight and it was still running this morning).</p>
  
  <p>I have a composite index created on transaction_timestamp, vendor_id, and revenue but running time is still outrageously high.</p>
  
  <p>When I do run this query on a subset of the data (I have a sample table that contains one day of the data), the query returns in 2.1 seconds.</p>
  
  <p>I'm pretty much completely green to optimizing databases and queries, so the fact that I can return this query on one day of data in 2.1 seconds leads me to believe that there's something I can do to make this query run in a reasonable amount of time for the main database.</p>
  
  <p>Please let me know if there's any other information that I've left out. </p>
  
  <p>Sample data, query and output here: <a href="http://rextester.com/AOKNT5900" rel="nofollow noreferrer">http://rextester.com/AOKNT5900</a></p>