Redshift could avoid full table scan using sortkey and joined table
<p>I have a very large table "event" in Redshift and a much smaller table "d_date" representing dates. Redshift will run a full table scan on "event" for the SQL below unless I uncomment the commented part. Table event has date_id as its sort key.</p>

<p>Why doesn't Redshift figure out it's much cheaper to first scan d_date and then restrict event table scan by matching values?</p>

<pre><code>select d_date.date_id, count(*)
from d_date
join event on d_date.date_id = event.date_id
where d_date.sqldate &gt; '2016-06-03'
/* without this the query will do a full table scan and run very slow */
/* and d_date.date_id &gt; 20160603 */
group by 1;
</code></pre>

<p>This is EXPLAIN output:</p>

<pre><code>QUERY PLAN
XN HashAggregate  (cost=19673968.12..19673971.77 rows=1460 width=4)
-&gt;  XN Hash Join DS_DIST_ALL_NONE  (cost=78.63..18758349.28 rows=183123769 width=4)
    Hash Cond: ("outer".date_id = "inner".date_id)
    -&gt;  XN Seq Scan on event  (cost=0.00..7523125.76 rows=752312576 width=4)
    -&gt;  XN Hash  (cost=74.98..74.98 rows=1460 width=4)
          -&gt;  XN Seq Scan on d_date  (cost=0.00..74.98 rows=1460 width=4)
                Filter: (sqldate &gt; '2016-06-03'::date)
</code></pre>

<p>With the part uncommented the table phase will look like this instead:</p>

<pre><code>    -&gt;  XN Seq Scan on event  (cost=0.00..928.32 rows=74266 width=4)
</code></pre>

<p>I have VACUUMed and ANALYZEd both tables and I have primary and foreign keys set up.</p>