title: Postgres - data manipulation on a single large table
body: <p>I have the following postgres table with about <strong>1 million rows</strong>.</p>
  
  <p><strong>id | underlying | option_symbol | option_type | expiration_date | strike | mid |</strong></p>
  
  <p>I need to perform the following in under a few seconds :</p>
  
  <ol>
  <li>Get all rows with option_type=”foo” and expiration_date > “2017-12-01”</li>
  <li>Get all rows with option_type=”bar” and expiration_date > “2017-12-01”</li>
  <li>For each row of query #1, loop over resulting rows of query #2 with the same underlying and expiration_date, and calculate a “spread” derived from values of strike#1 and strike#2.</li>
  <li>Sort this resulting list of calculated “spreads” and get the best result for each symbol.</li>
  </ol>
  
  <p><strong>how do I perform the "in query" calculations (spread_profit, spread_distance, spread_max_loss, spread_profit_ratio) in the example below?</strong></p>
  
  <p>Example:</p>
  
  <pre><code>SELECT a.underlying, 
         a.expiration_date, 
         (a.strike - b.strike) as spread_profit, 
         (a.mid - b.mid) as spread_distance, 
         (spread_distance - spread_profit) as spread_max_loss,
         (spread_profit / spread_max_loss) as spread_profit_ratio 
  FROM option_data a
     JOIN option_data b ON a.underlying = b.underlying AND a.expiration_date = b.expiration_date
  WHERE a.option_type = 'foo' 
    AND b.option_type = 'bar' 
    AND a.expiration_date &lt;= '2017-12-01' 
    AND b.expiration_date &lt;= '2017-12-01' 
  GROUP BY a.underlying, a.expiration_date 
  ORDER BY spread_profit_ratio DESC
  </code></pre>