public inbox for [email protected]  

help / color / mirror / Atom feed

From: Andrey Rachitskiy <[email protected]>
To: [email protected]
To: [email protected]
Subject: Re: BUG #19362: Extremely log processing of jsonb_path_exists_opr
Date: Tue, 23 Dec 2025 22:02:57 +0300
Message-ID: <[email protected]> (raw)


More examples to reproduce the problem:
```
–- Create a JSON array with nesting depth of 1000
CREATE TEMP TABLE test_json AS 
SELECT ('[' || repeat('[', 1000) || '0' || repeat(']', 1000) || ']')::jsonb AS data;
 
–- Query with 4 `.**` operators executes quickly
SELECT data @? '$.**.**.**.**' FROM test_json;
 ?column? 
----------
 t
(1 row)
Time: 0,550 ms
 
–- Same query with added `.*` in LAX mode hangs > 23 minutes (not the limit)
SELECT data @? '$.**.**.**.**.*' FROM test_json;
^CCancel request sent
ERROR:  canceling statement due to user request
Time: 1390914,431 ms (23:10,914)
```
 
Code Analysis (src/backend/utils/adt/jsonpath_exec.c)
Problem 1: Recursive .** Operator Execution
The executeAnyItem() function calls itself recursively for each nested level:
```
if (level < last && v.type == jbvBinary)
{     // Recursive call for each nested level    
      res = executeAnyItem(cxt, jsp, v.val.binary.data, found, level + 1, first, last, ignoreStructuralErrors, unwrapNext);
}
```
Each additional .** operator forces this recursion to repeat for every already discovered level, creating a multiplicative effect:
 
N levels × N levels × ... — repeated as many times as there are .** operators.
 
Problem 2: .* in LAX Mode Triggers Re-entry
When processing .* for arrays in LAX mode, the code calls executeItemUnwrapTargetArray(), which restarts the recursive traversal from the beginning (adding another full pass through all levels to the already explosive complexity):
```
return executeAnyItem(cxt, jsp, jb->val.binary.data, found,
                      1, 1, 1, false, unwrapElements);  // re-entry! 
```
 
Additional Issues:
Strict Mode Problems
```
-– Memory allocation error after ~5.5 seconds 
postgres=# SELECT data @? 'strict $.**.**.**.**.**'::jsonpath from test_json;
ERROR:  invalid memory alloc request size 1073741824
Time: 5537,986 ms (00:05,538)
 
-– Hangs > 32 minutes (not the limit)
SELECT data @? 'strict $.**.**.**.**.**.*'::jsonpath FROM test_json;
^CCancel request sent
ERROR:  canceling statement due to user request
Time: 1927249,549 ms (32:07,250)
```
Strict mode exhibits similar behavior to LAX mode, consuming significant CPU resources.
 
Affected Functions: The jsonb_path_query function is also susceptible to this performance issue.
 
-- 
regards, Andrey Rachitskiy

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