title: Postgres JSONB timestamp query very slow compared to timestamp column query
body: <p>I've got a Postgres 9.4.4 database with 1.7 million records with the following information stored in a JSONB column called <code>data</code> in a table called <code>accounts</code>:</p>
  
  <pre><code>data: {
    "lastUpdated": "2016-12-26T12:09:43.901Z",
    "lastUpdatedTimestamp": "1482754183"
    }
  }
  </code></pre>
  
  <p>The actual JSONB column stores much more information, but I've omitted the irrelevant data.  The data format cannot be changed since this is legacy information.</p>
  
  <p>I'm trying to efficiently obtain a count of all records where the <code>lastUpdated</code> value is greater or equal to some reference time (I'll use <code>2015-12-01T10:10:10Z</code> in the following examples):</p>
  
  <pre><code>explain analyze SELECT count(*) FROM "accounts" 
  WHERE data-&gt;&gt;'lastUpdated' &gt;= '2015-12-01T10:10:10Z';
  </code></pre>
  
  <p>This takes over 22 seconds:</p>
  
  <pre><code>Aggregate  (cost=843795.05..843795.06 rows=1 width=0) (actual time=22292.584..22292.584 rows=1 loops=1)
     -&gt;  Seq Scan on accounts  (cost=0.00..842317.05 rows=591201 width=0)
         (actual time=1.410..22142.046 rows=1773603 loops=1)
           Filter: ((data -&gt;&gt; 'lastUpdated'::text) &gt;= '2015-12-01T10:10:10Z'::text)
   Planning time: 1.234 ms
   Execution time: 22292.671 ms
  </code></pre>
  
  <p>I've tried adding the following text index:</p>
  
  <pre><code>CREATE INDEX accounts_last_updated ON accounts ((data-&gt;&gt;'lastUpdated'));
  </code></pre>
  
  <p>But the query is still rather slow, at over 17 seconds: </p>
  
  <pre><code>Aggregate  (cost=815548.64..815548.65 rows=1 width=0) (actual time=17172.844..17172.845 rows=1 loops=1)
    -&gt;  Bitmap Heap Scan on accounts  (cost=18942.24..814070.64 rows=591201 width=0)
        (actual time=1605.454..17036.081 rows=1773603 loops=1)
          Recheck Cond: ((data -&gt;&gt; 'lastUpdated'::text) &gt;= '2015-12-01T10:10:10Z'::text)
          Heap Blocks: exact=28955 lossy=397518
          -&gt;  Bitmap Index Scan on accounts_last_updated  (cost=0.00..18794.44 rows=591201 width=0)
              (actual time=1596.645..1596.645 rows=1773603 loops=1)
                Index Cond: ((data -&gt;&gt; 'lastUpdated'::text) &gt;= '2015-12-01T10:10:10Z'::text)
  Planning time: 1.373 ms
  Execution time: 17172.974 ms
  </code></pre>
  
  <p>I've also tried following the directions in <a href="https://stackoverflow.com/questions/29757374/create-timestamp-index-from-json-on-postgresql">Create timestamp index from JSON on PostgreSQL</a> and have tried creating the following function and index:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION text_to_timestamp(text)
  RETURNS timestamp AS
  $$SELECT to_timestamp($1, 'YYYY-MM-DD HH24:MI:SS.MS')::timestamp; $$
  LANGUAGE sql IMMUTABLE;
  
  CREATE INDEX accounts_last_updated ON accounts     
  (text_to_timestamp(data-&gt;&gt;'lastUpdated'));
  </code></pre>
  
  <p>But this doesn't give me any improvement, in fact it was slower, taking over 24 seconds for the query, versus 22 seconds for the unindexed version:</p>
  
  <pre><code>explain analyze SELECT count(*) FROM "accounts" 
  WHERE text_to_timestamp(data-&gt;&gt;'lastUpdated') &gt;= '2015-12-01T10:10:10Z';
  
  Aggregate  (cost=1287195.80..1287195.81 rows=1 width=0) (actual time=24143.150..24143.150 rows=1 loops=1)
    -&gt;  Seq Scan on accounts  (cost=0.00..1285717.79 rows=591201 width=0)
        (actual time=4.044..23971.723 rows=1773603 loops=1)
          Filter: (text_to_timestamp((data -&gt;&gt; 'lastUpdated'::text)) &gt;= '2015-12-01 10:10:10'::timestamp without time zone)
  Planning time: 1.107 ms
  Execution time: 24143.183 ms
  </code></pre>
  
  <p>In one last act of desperation, I decided to add another timestamp column and update it to contain the same values as <code>data-&gt;&gt;'lastUpdated'</code>:</p>
  
  <pre><code>alter table accounts add column updated_at timestamp;
  update accounts set updated_at = text_to_timestamp(data-&gt;&gt;'lastUpdated');
  create index accounts_updated_at on accounts(updated_at);
  </code></pre>
  
  <p>This has given me by far the best performance:</p>
  
  <pre><code>explain analyze SELECT count(*) FROM "accounts" where updated_at &gt;= '2015-12-01T10:10:10Z';
  
  Aggregate  (cost=54936.49..54936.50 rows=1 width=0) (actual time=676.955..676.955 rows=1 loops=1)
    -&gt;  Index Only Scan using accounts_updated_at on accounts
        (cost=0.43..50502.48 rows=1773603 width=0) (actual time=0.026..552.442 rows=1773603 loops=1)
          Index Cond: (updated_at &gt;= '2015-12-01 10:10:10'::timestamp without time zone)
          Heap Fetches: 0
  Planning time: 4.643 ms
  Execution time: 678.962 ms
  </code></pre>
  
  <p>However, I'd very much like to avoid adding another column just to improve the speed of ths one query.</p>
  
  <p><strong>This leaves me with the following question:</strong> is there any way to improve the performance of my JSONB query so it can be as efficient as the individual column query (the last query where I used <code>updated_at</code> instead of <code>data-&gt;&gt;'lastUpdated'</code>)?  As it stands, it takes from 17 seconds to 24 seconds for me to query the JSONB data using <code>data-&gt;&gt;'lastUpdated'</code>, while it takes only 678 ms to query the <code>updated_at</code> column.  It doesn't make sense that the JSONB query would be so much slower. I was hoping that by using the <code>text_to_timestamp</code> function that it would improve the performance, but it hasn't been the case (or I'm doing something wrong).</p>