title: Multiple ways to create index on a json field's nested property in PostgreSQL 9.3
body: <p>In PostgreSQL 9.3, there are multiple ways to build an expression, which points to a json field's nested property:</p>
  
  <pre><code>data-&gt;'foo'-&gt;&gt;'bar'
  data#&gt;&gt;'{foo,bar}'
  json_extract_path_text(data, 'foo', 'bar')
  </code></pre>
  
  <p>Therefore PostgreSQL only use these indexes, if the query's expression is an exact match with the index's expression.</p>
  
  <pre><code>CREATE TABLE json_test_index1(data json);
  CREATE TABLE json_test_index2(data json);
  CREATE TABLE json_test_index3(data json);
  
  CREATE INDEX ON json_test_index1((data-&gt;'foo'-&gt;&gt;'bar'));
  CREATE INDEX ON json_test_index2((data#&gt;&gt;'{foo,bar}'));
  CREATE INDEX ON json_test_index3((json_extract_path_text(data, 'foo', 'bar')));
  
  -- these queries use an index, while all other combinations not:
  
  EXPLAIN SELECT * FROM json_test_index1 WHERE data-&gt;'foo'-&gt;&gt;'bar' = 'baz';
  EXPLAIN SELECT * FROM json_test_index2 WHERE data#&gt;&gt;'{foo,bar}' = 'baz';
  EXPLAIN SELECT * FROM json_test_index3 WHERE json_extract_path_text(data, 'foo', 'bar') = 'baz';
  </code></pre>
  
  <p>My questions are:</p>
  
  <p>Is this behaviour intended? I thought the query optimizer should (at least) use the index with the <code>#&gt;&gt;</code> operator, when the query contains the appropriate call of <code>json_extract_path_text()</code> -- and vice versa.</p>
  
  <p>If I want to use more of these expressions in my application (not just one, f.ex. stick to the <code>-&gt;</code> &amp; <code>-&gt;&gt;</code> operators), what indexes should I build? (I hope, not all of them.)</p>
  
  <p>Are there any chance, that some future Postgres versions' optimizers will understand the equivalence of these expressions?</p>
  
  <p><strong>EDIT</strong>:</p>
  
  <p>When i create an additional operator for that:</p>
  
  <pre><code>CREATE OPERATOR ===&gt; (
      PROCEDURE = json_extract_path_text,
      LEFTARG = json,
      RIGHTARG = text[]
  );
  </code></pre>
  
  <p>This query (table from the previous example) still not uses its index:</p>
  
  <pre><code>EXPLAIN SELECT * FROM json_test_index3 WHERE data ===&gt; '{foo,bar}' = 'baz';
  </code></pre>
  
  <p>Bonus question:</p>
  
  <p>While Postgres expands the operators into function calls (behind the scenes), why this still not using its index?</p>