public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andrey Borodin <[email protected]>
To: Andrey Rachitskiy <[email protected]>
Cc: [email protected]
Cc: [email protected]
Subject: Re: BUG #19362: Extremely log processing of jsonb_path_exists_opr
Date: Fri, 1 May 2026 17:20:00 +0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
> On 24 Dec 2025, at 00:02, Andrey Rachitskiy <[email protected]> wrote:
>
> Strict mode exhibits similar behavior to LAX mode, consuming significant CPU resources.
>
Hi!
Examples that you show represent an opportunity for a performance optimization,
but hardly constitute a bug.
But I think to showcase this opportunity it would be good to demonstrate more
realistic query.
I cannot imagine end user wanting a query like
SELECT data @? '$.**.**.**.**.*' FROM test_json;
or
select
'[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[0]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]]'::jsonb
@? '++$.**.**.**.**.*'::jsonpath;
Please, construct a more realistic example of what user might want, but not get
in reasonable time.
FWIW, I've asked LLM and it thinks that optimization is worth doing:
<LLM output>
The query pattern $.**[*] ? (@ == "x") or $.**.* ? (@ == "x") is something a
developer might write when searching for values at any depth in JSON data that
contains arrays (task lists, comment threads, nested categories). The intent
is clear: "find this value anywhere in the structure, including inside arrays."
The performance degradation from O(N) to O(N²) occurs at moderate nesting
depths that can appear in real data — 50-level comment threads, 30-level
org charts, etc.
More importantly, if the application passes user-provided jsonpath expressions
to @?, the $.**.**.**.**.* pattern becomes a DoS vector requiring no special
privileges — only the ability to submit a query.
A meaningful optimization would be to collapse consecutive .** operators
(.** {a,b} .** {c,d} → .** {a+c, b+d}) at parse or execution time, which
would make $.**.**.**.**.* behave identically to $.**.* in O(N²) time
rather than O(N⁵).
</LLM output>
But I think it's a matter for pgsql-hackers, not pgsql-bugs.
Best regards, Andrey Borodin.
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: BUG #19362: Extremely log processing of jsonb_path_exists_opr
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox