Re: BUG #19362: Extremely log processing of jsonb_path_exists_opr - Mailing list pgsql-bugs

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