title: Optimizing Postgres JSONB query with not null constraint
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: {
    "lastUpdatedTime": "2016-12-26T12:09:43.901Z",
    "UID": "2c5bb7fd-1a00-4988-8d92-ffaa52ebc20d",
    "data": {
      "country": "UK",
      "verified_at": "2017-01-01T23:49:10.217Z"
    }
  }
  </code></pre>
  
  <p>The data format cannot be changed since this is legacy information.</p>
  
  <p>I need to obtain all accounts where the country is <code>UK</code>, the <code>verified_at</code> value is not null and the <code>lastUpdatedTime</code> value is greater than some given value.</p>
  
  <p>So far, I have the following query:</p>
  
  <pre><code>SELECT * FROM "accounts"
  WHERE (data @&gt; '{ "data": { "country": "UK" } }')
  AND (data-&gt;'data' ? 'verified_at')
  AND ((data-&gt;'data' -&gt;&gt; 'verified_at') is not null)
  AND (data -&gt;&gt;'lastUpdatedTime' &gt; '2016-02-28T05:49:08.511846')
  ORDER BY data -&gt;&gt;'lastUpdatedTime' LIMIT 100 OFFSET 0;
  </code></pre>
  
  <p>And the following indexes:</p>
  
  <pre><code>"accounts_idxgin" gin (data)
  "accounts_idxgin_on_data" gin ((data -&gt; 'data'::text))
  </code></pre>
  
  <p>I've managed to get the query time down to about 1000 to 4000ms</p>
  
  <p>Here is the analyze from the query:</p>
  
  <pre><code> Bitmap Heap Scan on accounts  (cost=41.31..6934.50 rows=9 width=1719)
                                 (actual time=7.273..1067.657 rows=23190 loops=1)
     Recheck Cond: ((data -&gt; 'data'::text) ? 'verified_at'::text)
     Filter: ((((data -&gt; 'data'::text) -&gt;&gt; 'verified_at'::text) IS NOT NULL)
             AND ((data -&gt;&gt; 'lastUpdatedTime'::text) &gt; '2016-02-01 05:49:08.511846'::text)
             AND (((data -&gt; 'data'::text) -&gt;&gt; 'country'::text) = 'UK'::text))
     Rows Removed by Filter: 4
     Heap Blocks: exact=16039
     -&gt;  Bitmap Index Scan on accounts_idxgin_on_data  (cost=0.00..41.30 rows=1773 width=0)
         (actual time=4.618..4.618 rows=23194 loops=1)
           Index Cond: ((data -&gt; 'data'::text) ? 'verified_at'::text)
   Planning time: 0.448 ms
   Execution time: 1069.344 ms
  (9 rows)
  </code></pre>
  
  <p>I have the following questions</p>
  
  <ol>
  <li>Is there anything I can do to further speed up this query?</li>
  <li>What is the correct way to speed up a <code>field is not null</code> query with JSONB? I ended up using the existence operator with <code>(data-&gt;'data' ? 'verified_at')</code> to filter out a large number of non-matching records, because much of my data doesn't have <code>verified_at</code> as a top level key.   This increased the speed of the query, but I'm wondering if there's a general approach to optimizing this type of query.</li>
  <li>In order to use the existence operator with <code>(data-&gt;'data' ? 'verified_at')</code>, I needed to add another index on <code>((data -&gt; 'data'::text))</code>. I already had an index on <code>gin (data)</code>, but the existence operator didn't use this.  Why is that? I thought the existence and containment operators would use this index.</li>
  </ol>