title: Redshift table design for efficiency
body: <p>I have a redshift cluster with a single dc1.large node. I've got data writing into it, on order of 50 million records a day, in the format of a timestamp, a user ID and an item ID. The item ID (varchar) is unique, the user ID (varchar) is not, and the timestamp (timestamp) is not.</p>
  
  <p>In my redshift DB of about 110m records, if I have a table with no sort key, it takes about 30 seconds to search for a single item ID.</p>
  
  <p>If I have a table with a sort key on item ID, I get a single item ID search time of about 14-16 seconds.</p>
  
  <p>If I have a table with an interleved sort key with all three columns, the single item ID search time is still 14-16 seconds.</p>
  
  <p>What I'm hoping to achieve is the ability to query for the records of thousands or tens of thousands of item IDs on order of a second. </p>
  
  <p>The query just looks like</p>
  
  <pre><code>select count(*) from rs_table where itemid = 'id123';
  </code></pre>
  
  <p>or</p>
  
  <pre><code>select count(*) from rs_table where itemid in ('id123','id124','id125');
  </code></pre>
  
  <p>This query comes back in 541ms</p>
  
  <pre><code>select count(*) from rs_table;
  </code></pre>
  
  <p>AWS documentation suggests that there is a compile time for queries the first time they're run, but I don't think that's what I'm seeing (and it would be not ideal if it was, since each unique set of 10,000 IDs might never be queried in exactly the same order again.</p>
  
  <p>I have to assume I'm doing something wrong with either the sort key design, the query, or some combination of the two - for only ~10g of table space, something like redshift shouldn't take this long to query, right?</p>