Home Explore Blog CI



postgresql

8th chunk of `doc/src/sgml/ltree.sgml`
40914fe70498aa0efc35f9c124c044e9cd698addee0cfc6c0000000100000fa3
 <type>ltree</type>.
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm><primary>ltree2text</primary></indexterm>
        <function>ltree2text</function> ( <type>ltree</type> )
        <returnvalue>text</returnvalue>
       </para>
       <para>
        Casts <type>ltree</type> to <type>text</type>.
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <indexterm><primary>lca</primary></indexterm>
        <function>lca</function> ( <type>ltree</type> <optional>, <type>ltree</type> <optional>, ... </optional></optional> )
        <returnvalue>ltree</returnvalue>
       </para>
       <para>
        Computes longest common ancestor of paths
        (up to 8 arguments are supported).
       </para>
       <para>
        <literal>lca('1.2.3', '1.2.3.4.5.6')</literal>
        <returnvalue>1.2</returnvalue>
       </para></entry>
      </row>

      <row>
       <entry role="func_table_entry"><para role="func_signature">
        <function>lca</function> ( <type>ltree[]</type> )
        <returnvalue>ltree</returnvalue>
       </para>
       <para>
        Computes longest common ancestor of paths in array.
       </para>
       <para>
        <literal>lca(array['1.2.3'::ltree,'1.2.3.4'])</literal>
        <returnvalue>1.2</returnvalue>
       </para></entry>
      </row>
     </tbody>
    </tgroup>
  </table>
 </sect2>

 <sect2 id="ltree-indexes">
  <title>Indexes</title>
  <para>
   <filename>ltree</filename> supports several types of indexes that can speed
   up the indicated operators:
  </para>

  <itemizedlist>
   <listitem>
    <para>
     B-tree index over <type>ltree</type>:
     <literal>&lt;</literal>, <literal>&lt;=</literal>, <literal>=</literal>,
     <literal>&gt;=</literal>, <literal>&gt;</literal>
    </para>
   </listitem>
   <listitem>
    <para>
     Hash index over <type>ltree</type>:
     <literal>=</literal>
    </para>
   </listitem>

   <listitem>
    <para>
     GiST index over <type>ltree</type> (<literal>gist_ltree_ops</literal>
     opclass):
     <literal>&lt;</literal>, <literal>&lt;=</literal>, <literal>=</literal>,
     <literal>&gt;=</literal>, <literal>&gt;</literal>,
     <literal>@&gt;</literal>, <literal>&lt;@</literal>,
     <literal>@</literal>, <literal>~</literal>, <literal>?</literal>
    </para>
    <para>
     <literal>gist_ltree_ops</literal> GiST opclass approximates a set of
     path labels as a bitmap signature.  Its optional integer parameter
     <literal>siglen</literal> determines the
     signature length in bytes.  The default signature length is 8 bytes.
     The length must be a positive multiple of <type>int</type> alignment
     (4 bytes on most machines)) up to 2024.  Longer
     signatures lead to a more precise search (scanning a smaller fraction of the index and
     fewer heap pages), at the cost of a larger index.
    </para>
    <para>
     Example of creating such an index with the default signature length of 8 bytes:
    </para>
<programlisting>
CREATE INDEX path_gist_idx ON test USING GIST (path);
</programlisting>
    <para>
     Example of creating such an index with a signature length of 100 bytes:
    </para>
<programlisting>
CREATE INDEX path_gist_idx ON test USING GIST (path gist_ltree_ops(siglen=100));
</programlisting>
   </listitem>
   <listitem>
    <para>
     GiST index over <type>ltree[]</type> (<literal>gist__ltree_ops</literal>
     opclass):
     <literal>ltree[] &lt;@ ltree</literal>, <literal>ltree @&gt; ltree[]</literal>,
     <literal>@</literal>, <literal>~</literal>, <literal>?</literal>
    </para>
    <para>
     <literal>gist__ltree_ops</literal> GiST opclass works similarly to
     <literal>gist_ltree_ops</literal> and also takes signature length as
     a parameter.  The default value of <literal>siglen</literal> in
      <literal>gist__ltree_ops</literal> is 28 bytes.
    </para>
    <para>

Title: ltree Indexes
Summary
The ltree data type supports various indexes, including B-tree, hash, and GiST indexes, which can speed up operations such as less than, greater than, and equality checks, as well as path containment and similarity searches, with options for customizing the signature length for GiST indexes to balance precision and index size.