doc: explain what can restrict an ltree GiST index search for lquery

From: Andrea Esposito <and1989(at)gmail(dot)com>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Subject: doc: explain what can restrict an ltree GiST index search for lquery
Date: 2026-11-18 21:37:49
Message-ID: CAJxyBiDPLg28mtmYNP2s+O+T7n+ePJNr_bfre4CALRL=dvZHCw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-docs

Hi,

The ltree documentation lists ~ among the operators supported by
gist_ltree_ops, but it does not say which parts of an lquery pattern
can actually narrow the index scan.

For example, path ~ 'Top.Science.*{1,2}' examines exactly the same
index entries as path ~ 'Top.Science.*': the "one or two more levels"
constraint plays no part in the index search and is only applied to
each leaf entry. A pattern that does not start with a plain label,
such as '*.Science.*', gets no range restriction at all and can only
be narrowed by the signature.

On a 200,000-row test table on master (random paths 'Top.<category>'
plus 0-5 levels of labels n0..n30, 8 categories; gist_ltree_ops with
the default siglen, 2807 index pages), with enable_seqscan and
enable_indexscan off, EXPLAIN (ANALYZE, BUFFERS) shows:

-> Bitmap Index Scan on t_gist (actual rows=5852.00 loops=1)
Index Cond: (path ~ 'Top.Science.*{1,2}'::lquery)
Index Searches: 1
Buffers: shared hit=236

-> Bitmap Index Scan on t_gist (actual rows=14319.00 loops=1)
Index Cond: (path ~ 'Top.Science.*'::lquery)
Index Searches: 1
Buffers: shared hit=236

'Top.Science.*{4}' (2808 rows) also read 236 index buffers. In that
data set '*.Science.*' read 236 buffers as well, here thanks to the
signature alone.

This follows from the code in contrib/ltree/ltree_gist.c:

- for strategies 12/13 (ltree ~ lquery, lquery ~ ltree),
ltree_consistent() calls ltq_regex() on leaf entries and
gist_qe() && gist_between() on inner entries;

- gist_qe() only looks at levels with numvar > 0 whose flags allow a
signature lookup (LQL_CANLOOKSIGN); '*' levels have numvar == 0 and
are skipped, so their {n,m} quantifier is never considered;

- gist_between() range-compares the leading lquery.firstgood levels
against the inner key's lower/upper bound paths; parse_lquery()
counts a level in firstgood only if it has a single variant and no
flags, and only while no earlier level failed that test, so
firstgood is 0 for '*.Science.*';

- hashing() builds the signature from the label names alone, with no
level position, so the signature cannot express where a label
occurs or how deep a path is.

Since ltree_consistent() always sets *recheck = false, EXPLAIN shows
no "Rows Removed by Index Recheck" for these scans; the extra work is
only visible in the index buffer counts. That is part of why I think
it is worth stating in the docs.

The attached patch is doc-only, against master, and adds one paragraph
to the gist_ltree_ops item in the "Indexes" section of ltree.sgml. The
behavior is long-standing, so the note would apply to all supported
branches if the committer wants to back-patch it. I am happy to
reword it.

The same holds for ? (lquery[]): arrq_cons() applies gist_qe() &&
gist_between() to each array element. For @ (ltxtquery), gist_qtxt()
uses only the signature. I kept the patch focused on lquery; I can
extend the note to those operators if reviewers think it is useful.

Regards,
Andrea Esposito

Attachment Content-Type Size
v1-0001-doc-explain-what-can-restrict-an-ltree-GiST-index.patch application/octet-stream 2.4 KB

Browse pgsql-docs by date

  From Date Subject
Next Message Matemática A3K 2026-11-19 04:04:30 Re: Introductory docs-hacking task: Example markup
Previous Message Daniel Gustafsson 2026-11-17 20:57:54 Re: Good beginner doc-hacking task: clear-text vs. cleartext vs. clear text vs. plaintext etc..