Why does Redshift need to do a full table scan to find the max value of the DIST/SORT key?
<p>I'm doing simple tests on Redshift to try and speed up the insertion of data into a Redshift table.  One thing I noticed today is that doing something like this</p>

<pre><code>CREATE TABLE a (x int) DISTSTYLE key DISTKEY (x) SORTKEY (x);
INSERT INTO a (x) VALUES (1), (2), (3), (4);
VACUUM a; ANALYZE a;

EXPLAIN SELECT MAX(x) FROM a;
</code></pre>

<p>yields</p>

<pre><code>QUERY PLAN
XN Aggregate  (cost=0.05..0.05 rows=1 width=4)
  -&gt;  XN Seq Scan on a  (cost=0.00..0.04 rows=4 width=4)
</code></pre>

<p>I know this is only 4 rows, but it still shouldn't be doing a full table scan to find the max value of a pre-sorted column.  Isn't that metadata included in the work done by <code>ANALYZE</code>?</p>

<p>And just as a sanity check, the <code>EXPLAIN</code> for <code>SELECT x FROM a WHERE x &gt; 3</code> only scans 2 rows instead of the whole table.</p>

<p>Edit: I inserted 1,000,000 more rows into the table with random values from 1 to 10,000.  Did a vacuum and analyze.  The query plan still says it has to scan all 1,000,004 rows.</p>