title: Slow SQL transaction getting latest timestamp rows from table in Postgres
body: <p>I am having trouble with a slow transaction in Postgres trying to retrieve the latest prices of the catalogproducts that have a buy that is greater than their sell.  It is a rather large table at this point, over 2 million rows.  I have this for historical purposes.  What I am currently using is:  </p>
  
  <pre><code>select * from ta_price a
    join (
     select catalogproduct_id, max(timestamp) ts
       from ta_price
      group by catalogproduct_id
         ) b on a.catalogproduct_id = b.catalogproduct_id
            and a.timestamp = b.ts
            AND buy &gt; sell;
  </code></pre>
  
  <p>catalogproduct_id is a Foreign Key to catalogproduct table.</p>
  
  <p>Out of 2201760 total rows, it selects 2296 rows.  The total runtime  is 181,792.705 ms.  </p>
  
  <p>Any insight on how to improve this?</p>
  
  <p><strong>Edit:</strong></p>
  
  <p>I am blown away by all the answers!  I want to also qualify this question more under the realm of the Django ORM.  I am struggling to incorporate a composite key (or the like) on this table (using catalogproduct_id and timestamp).  I have a primary key that is an autoincrementing index, which I guess is as good as not having none at all.</p>
  
  <p><strong>Edit 2:</strong>
  After adding a partial index that @Erwin suggested, 
  <code>CREATE INDEX my_partial_idx ON ta_price (catalogproduct_id, timestamp)
   WHERE buy &gt; sell;</code>, I am using the query from @wildplasser for around 10-12 second query time.  For further clarification, my table is snapshots of prices (buy and sell) of products over time.  At any given time, I want to know what products currently (as of their latest snapshot time) have a <code>buy &gt; sell</code>.</p>