Thread

[PATCH] Collapse consecutive .** accessors for jsonpath exists queries

Andrey Rachitskiy <pl0h0yp1@gmail.com> — 2026-07-06T18:50:21Z

Hi, Hackers!

When a jsonpath expression contains multiple consecutive .** (jpiAny)
accessors, each one triggers a full subtree traversal in
executeAnyItem().  A chain of k such accessors can cost O(N^k) on a
document with N nodes, even though for existence semantics it is
equivalent to a single .** with merged level bounds.


Background
----------

We originally reported this as BUG #19362 [1], with a follow-up
analysis [2].  The original report used a fuzzer-generated sql
statement; the follow-up gave a clearer reproduction. For example, on
a JSON array nested 1000 levels deep:
  SELECT data @? '$.**.**.**.**' FROM test_json;     -- ~0.5 ms
  SELECT data @? '$.**.**.**.**.*' FROM test_json;   -- >23 min
  (cancelled)

Andrey Borodin replied that this looks like a performance optimization
opportunity rather than a bug, and asked for a more realistic example of
what a user might want but not get in reasonable time [3].  We agree,
and are sending this to pgsql-hackers.

A developer searching semi-structured JSON (nested categories, comment
threads, task lists) might use @? / jsonb_path_exists with paths such as
$.**.b ? (@ > 0) — "does key b exist anywhere, with this predicate?"
Templates, copy-paste, or auto-generated paths can accidentally
accumulate redundant .** segments (.**.**.b), which is semantically the
same as $.**.b for existence checks but much slower.

If an application accepts user-supplied jsonpath for @?, a path with
many consecutive .** operators also becomes an easy DoS vector: no
special privileges beyond the ability to run a query.


Proposal
--------

Collapse consecutive jpiAny nodes at execution time for existence
queries (@? and jsonb_path_exists): merge level bounds and perform a
single executeAnyItem() pass (.** {a,b} .** {c,d} -> .** {a+c,b+d}).

For existence checks this bounds the cost of a k-deep .** chain to
O(N) instead of O(N^k), so paths that previously hung the backend for
many minutes on the BUG #19362 reproduction now finish in under a
millisecond. That closes a practical DoS vector for applications that
pass user-supplied jsonpath to @?.

This patch intentionally does NOT change jsonb_path_query or @@: those
functions collect item sequences, and consecutive .** affects result
multiplicity (e.g. lax $.**.** vs $.**).  The optimization is gated on
existsOnly (result == NULL in executeJsonPath()).

jsonb_jsonpath regress tests are included.

Backpatch-through: 15.
Separate patches for REL_15_STABLE through master are attached.

[1] https://postgr.es/m/19362-9e824863c543cafa@postgresql.org
[2] https://postgr.es/m/1766516577.178080141@f533.i.mail.ru
[3]
https://postgr.es/m/DD2A3250-D456-4871-A245-9E851BC59B66@yandex-team.ru

Author: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Reported-by: Nikolay Shaplov <dhyan@nataraj.su>
Tested-on: REL_15_STABLE .. master

-- 
Regards,
Andrey Rachitskiy