Home Explore Blog CI



postgresql

8th chunk of `doc/src/sgml/json.sgml`
bf7e2f9486d33ba77d0bc27f3bcb3272e79bc9c4fa87115f0000000100000fa3
 this
    query.  But note that such an index will store copies of every key and
    value in the <structfield>jdoc</structfield> column, whereas the expression index
    of the previous example stores only data found under
    the <literal>tags</literal> key.  While the simple-index approach is far more
    flexible (since it supports queries about any key), targeted expression
    indexes are likely to be smaller and faster to search than a simple
    index.
  </para>

  <para>
    GIN indexes also support the <literal>@?</literal>
    and <literal>@@</literal> operators, which
    perform <type>jsonpath</type> matching.  Examples are
<programlisting>
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @? '$.tags[*] ? (@ == "qui")';
</programlisting>
<programlisting>
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';
</programlisting>
    For these operators, a GIN index extracts clauses of the form
    <literal><replaceable>accessors_chain</replaceable>
    == <replaceable>constant</replaceable></literal> out of
    the <type>jsonpath</type> pattern, and does the index search based on
    the keys and values mentioned in these clauses.  The accessors chain
    may include <literal>.<replaceable>key</replaceable></literal>,
    <literal>[*]</literal>,
    and <literal>[<replaceable>index</replaceable>]</literal> accessors.
    The <literal>jsonb_ops</literal> operator class also
    supports <literal>.*</literal> and <literal>.**</literal> accessors,
    but the <literal>jsonb_path_ops</literal> operator class does not.
  </para>

  <para>
    Although the <literal>jsonb_path_ops</literal> operator class supports
    only queries with the <literal>@&gt;</literal>, <literal>@?</literal>
    and <literal>@@</literal> operators, it has notable
    performance advantages over the default operator
    class <literal>jsonb_ops</literal>.  A <literal>jsonb_path_ops</literal>
    index is usually much smaller than a <literal>jsonb_ops</literal>
    index over the same data, and the specificity of searches is better,
    particularly when queries contain keys that appear frequently in the
    data.  Therefore search operations typically perform better
    than with the default operator class.
  </para>

  <para>
    The technical difference between a <literal>jsonb_ops</literal>
    and a <literal>jsonb_path_ops</literal> GIN index is that the former
    creates independent index items for each key and value in the data,
    while the latter creates index items only for each value in the
    data.
    <footnote>
     <para>
      For this purpose, the term <quote>value</quote> includes array elements,
      though JSON terminology sometimes considers array elements distinct
      from values within objects.
     </para>
    </footnote>
    Basically, each <literal>jsonb_path_ops</literal> index item is
    a hash of the value and the key(s) leading to it; for example to index
    <literal>{"foo": {"bar": "baz"}}</literal>, a single index item would
    be created incorporating all three of <literal>foo</literal>, <literal>bar</literal>,
    and <literal>baz</literal> into the hash value.  Thus a containment query
    looking for this structure would result in an extremely specific index
    search; but there is no way at all to find out whether <literal>foo</literal>
    appears as a key.  On the other hand, a <literal>jsonb_ops</literal>
    index would create three index items representing <literal>foo</literal>,
    <literal>bar</literal>, and <literal>baz</literal> separately; then to do the
    containment query, it would look for rows containing all three of
    these items.  While GIN indexes can perform such an AND search fairly
    efficiently, it will still be less specific and slower than the
    equivalent <literal>jsonb_path_ops</literal> search, especially if
    there are a very large number of rows containing any single one of the
    three index items.
  </para>

  <para>
    A disadvantage

Title: GIN Indexes for JSONB Data: Operator Classes and Performance
Summary
GIN indexes support jsonpath matching with the @? and @@ operators, and the jsonb_path_ops operator class offers better performance and smaller index size compared to the default jsonb_ops class, but with limitations in query support, making it suitable for specific use cases where containment queries are common.