title: Redshift SELECT * performance versus COUNT(*) for non existent row
body: <p>I am confused about what Redshift is doing when I run 2 seemingly similar queries. Neither should return a result (querying a profile that doesn't exist). Specifically:</p>
  
  <pre><code>SELECT * FROM profile WHERE id = 'id_that_doesnt_exist' and project_id = 1;
    Execution time: 36.75s
  </code></pre>
  
  <p>versus</p>
  
  <pre><code>SELECT COUNT(*) FROM profile WHERE id = 'id_that_doesnt_exist' and project_id = 1;
    Execution time: 0.2s
  </code></pre>
  
  <p>Given that the table is sorted by <code>project_id</code> then <code>id</code> I would have thought this is just a key lookup. The <code>SELECT COUNT(*) ...</code> returns 0 results in 0.2sec which is about what I would expect. The <code>SELECT * ...</code> returns 0 results in 37.75sec. That's a huge difference for the same result and I don't understand why?</p>
  
  <p>If it helps schema as follows:</p>
  
  <pre><code>CREATE TABLE profile (
      project_id integer not null,
      id varchar(256) not null,
      created timestamp not null,
      /* ... approx 50 other columns here */
  )
  DISTKEY(id)
  SORTKEY(project_id, id);
  </code></pre>
  
  <p>Explain from <code>SELECT COUNT(*) ...</code></p>
  
  <pre><code>XN Aggregate  (cost=435.70..435.70 rows=1 width=0)
    -&gt;  XN Seq Scan on profile  (cost=0.00..435.70 rows=1 width=0)
          Filter: (((id)::text = 'id_that_doesnt_exist'::text) AND (project_id = 1))
  </code></pre>
  
  <p>Explain from <code>SELECT * ...</code></p>
  
  <pre><code>XN Seq Scan on profile  (cost=0.00..435.70 rows=1 width=7356)
      Filter: (((id)::text = 'id_that_doesnt_exist'::text) AND (project_id = 1))
  </code></pre>
  
  <p>Why is the non count much slower? Surely Redshift knows the row doesn't exist?</p>