title: PostgreSQL Indexing Run Time
body: <p>Supposing I have this table with index.</p>
  
  <pre><code> Create Table sample
   (
      table_date timestamp without timezone,
      description
   );
    CREATE INDEX dailyinv_index
    ON sample
    USING btree
    (date(table_date));
  </code></pre>
  
  <p>And It has a 33 million rows.</p>
  
  <p>Why is it that running this query</p>
  
  <pre><code>   select count(*) from sample where date(table_date) = '8/30/2017' and   desc = 'desc1'
  </code></pre>
  
  <p>yields a result @ 12ms</p>
  
  <p>Using PostgreSQL to explain the query plan. this is what it does.</p>
  
  <pre><code>    Aggregate  (cost=288678.55..288678.56 rows=1 width=0)
        -&gt;Bitmap Heap Scan on sample (cost=3119.63..288647.57 rows=12393 width=0)
             Recheck Cond: (date(table_date) = '2017-08-30'::date)
             Filter: ((description)::text = 'desc1'::text)
                 -&gt;  Bitmap Index Scan on dailyinv_index  (cost=0.00..3116.54 rows=168529 width=0)
                       Index Cond: (date(table_date) = '2017-08-30'::date)
  </code></pre>
  
  <p>but this one</p>
  
  <pre><code>   select date(table_date) from sample where date(table_date)&lt;='8/30/2017' order by table_date desc limit 1
  </code></pre>
  
  <p>yields result after 11,460 ms?</p>
  
  <p>Query Plan</p>
  
  <pre><code>   Limit  (cost=798243.52..798243.52 rows=1 width=8)
       -&gt;  Sort  (cost=798243.52..826331.69 rows=11235271 width=8)
               Sort Key: table_date
                    -&gt;  Bitmap Heap Scan on sample  (cost=210305.92..742067.16 rows=11235271 width=8)
                          Recheck Cond: (date(table_date) &lt;= '2017-08-30'::date)
                               -&gt;  Bitmap Index Scan on dailyinv_index  (cost=0.00..207497.10 rows=11235271 width=0)
                                      Index Cond: (date(table_date) &lt;= '2017-08-30'::date)
  </code></pre>
  
  <p>PostgreSQL Version: 9.4</p>
  
  <p>Maybe Im doing the indexing wrong or I dont know. Really not familiar with indexing. Any help would be great. Thanks a lot!</p>