| 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 |
| 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.. |