Unable to hit conditional expressional index
<p>I have a table with around 20 million records with an index over a <code>created_at</code> timestamp field. The size of the index is huge and even with an index scan, querying the table by that field is not fast enough (7 seconds). So I decided to play a bit with smaller expressional indexes. The thing is the planner never hits them and is going for a sequential scan instead. What am I doing wrong?</p>

<pre><code>db=# SELECT COUNT(*) FROM xxx;
 count
----------
 19527175
(1 row)
</code></pre>

<p>The conditional expressional index is defined as:</p>

<pre><code>CREATE INDEX xxx_idx ON xxx (DATE(created_at)) WHERE created_at &gt; '2016-01-01';
</code></pre>

<p>That fraction represents just 2% of the entire data.</p>

<pre><code>db=# SELECT COUNT(*) FROM xxx WHERE created_at &gt; '2016-01-01';
 count
---------
 335895
(1 row)
</code></pre>

<p>Yet, when trying to use the index by specifying the same <strong>condition</strong> and <strong>expression</strong>, a sequential scan is used instead.</p>

<pre><code>db=# EXPLAIN ANALYZE SELECT * FROM xxx WHERE DATE(created_at) &gt; '2016-01-01';
                              QUERY PLAN
-----------------------------------------------------------------------
 Seq Scan on xxx  (cost=0.00..1533675.37 rows=6509853 width=884) (actual time=23.104..120704.554 rows=319278 loops=1)
   Filter: (date(created_at) &gt; '2016-01-01'::date)
   Rows Removed by Filter: 19208882
 Planning time: 0.245 ms
 Execution time: 120748.839 ms
(5 rows)
</code></pre>