Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\))
Subject: Re: BUG #19362: Extremely log processing of jsonb_path_exists_opr
From: Andrey Borodin <x4mmm@yandex-team.ru>
In-Reply-To: <1766516577.178080141@f533.i.mail.ru>
Date: Fri, 1 May 2026 17:20:00 +0500
Cc: pgsql-bugs@lists.postgresql.org,
 dhyan@nataraj.su
Content-Transfer-Encoding: quoted-printable
Message-Id: <DD2A3250-D456-4871-A245-9E851BC59B66@yandex-team.ru>
References: <1766516577.178080141@f533.i.mail.ru>
To: Andrey Rachitskiy <therealgofman@mail.ru>
Archived-At: <https://www.postgresql.org/message-id/DD2A3250-D456-4871-A245-9E851BC59B66%40yandex-team.ru>
Precedence: bulk


> On 24 Dec 2025, at 00:02, Andrey Rachitskiy <therealgofman@mail.ru> =
wrote:
>=20
> Strict mode exhibits similar behavior to LAX mode, consuming =
significant CPU resources.
>=20

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 $.**[*] ? (@ =3D=3D "x") or $.**.*  ? (@ =3D=3D "x") =
is something a=20
developer might write when searching for values at any depth in JSON =
data that=20
contains arrays (task lists, comment threads, nested categories). The =
intent=20
is clear: "find this value anywhere in the structure, including inside =
arrays."
The performance degradation from O(N) to O(N=C2=B2) occurs at moderate =
nesting=20
depths that can appear in real data =E2=80=94 50-level comment threads, =
30-level=20
org charts, etc.
More importantly, if the application passes user-provided jsonpath =
expressions=20
to @?, the $.**.**.**.**.*  pattern becomes a DoS vector requiring no =
special=20
privileges =E2=80=94 only the ability to submit a query.
A meaningful optimization would be to collapse consecutive .** operators=20=

(.** {a,b} .** {c,d} =E2=86=92 .** {a+c, b+d}) at parse or execution =
time, which=20
would make $.**.**.**.**.*  behave identically to $.**.*  in O(N=C2=B2) =
time=20
rather than O(N=E2=81=B5).

</LLM output>

But I think it's a matter for pgsql-hackers, not pgsql-bugs.


Best regards, Andrey Borodin.=