Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\))
Subject: Re: Avoiding memory leakage in jsonpath evaluation
From: Chao Li <li.evan.chao@gmail.com>
In-Reply-To: <B1A514E3-9E5A-4DA7-BD8D-88D27066C833@gmail.com>
Date: Wed, 18 Mar 2026 15:57:53 +0800
Cc: pgsql-hackers@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <D311D367-8313-4288-8272-CFAD67ED7CEA@gmail.com>
References: <569394.1773783211@sss.pgh.pa.us>
 <B1A514E3-9E5A-4DA7-BD8D-88D27066C833@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/D311D367-8313-4288-8272-CFAD67ED7CEA%40gmail.com>
Precedence: bulk


> On Mar 18, 2026, at 15:52, Chao Li <li.evan.chao@gmail.com> wrote:
>=20
>=20
>=20
>> On Mar 18, 2026, at 05:33, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>=20
>> I got an off-list report that a query like this consumes
>> an unreasonable amount of memory:
>>=20
>> SELECT jsonb_path_query((SELECT jsonb_agg(i) FROM =
generate_series(1,10000) i),
>>                       '$[*] ? (@ < $)');
>>=20
>> For me, that eats about 6GB by the time it's done executing.
>> If that doesn't seem like a lot to you, just add another zero to the
>> generate_series call, and then it'll be more like 600GB, because the
>> leakage is O(N^2).
>>=20
>> Admittedly, this isn't an especially useful query: its runtime is
>> also O(N^2), because that path expression basically requires us to
>> compare every element of the input JSON array to every other element.
>> But it's not cool that it leaks so much memory while at it.
>>=20
>> I poked into this and found that the leakage is entirely composed of
>> "JsonValueList"s that are built during path evaluation and then just
>> left to rot until the end of jsonb_path_query().  We can fix it by
>> being careful to free those lists on the way out of each jsonpath
>> evaluation function that creates one.  However, just doing that would
>> mean adding pfree overhead on top of palloc overhead, so I went a bit
>> further and reimplemented JsonValueList to be more compact and =
cheaper
>> to allocate/free.  The attached seems to be a bit faster than the
>> existing code as well as not leaking so much memory.  See the draft
>> commit message for more details.
>>=20
>> regards, tom lane
>=20
> This patch looks like a big win. It not only saves memory, but also =
makes the query much faster.
>=20
> I tested the query on my MacBook M4, increasing the iteration count =
from 10000 to 50000.
>=20
> Current master (3b4c2b9db25):
> ```
> evantest=3D# SELECT jsonb_path_query((SELECT jsonb_agg(i) FROM =
generate_series(1,50000) i), '$[*] ? (@ < $)');
> Time: 208581.771 ms (03:28.582)
> evantest=3D# SELECT jsonb_path_query((SELECT jsonb_agg(i) FROM =
generate_series(1,50000) i), '$[*] ? (@ < $)');
> Time: 217269.595 ms (03:37.270)
> ```
>=20
> With the patch:
> ```
> evantest=3D# SELECT jsonb_path_query((SELECT jsonb_agg(i) FROM =
generate_series(1,50000) i), '$[*] ? (@ < $)');
> Time: 18674.580 ms (00:18.675)
> evantest=3D# SELECT jsonb_path_query((SELECT jsonb_agg(i) FROM =
generate_series(1,50000) i), '$[*] ? (@ < $)');
> Time: 18889.329 ms (00:18.889)
> ```
>=20
> My observations were:
>=20
> * Before the patch, the backend process memory usage fluctuated =
between roughly 50GB and 145GB, while CPU usage stayed around 30%.
> * With the patch, the backend process memory usage stayed stable at =
around 30MB, while CPU usage stayed around 100%.
>=20
> After reviewing the patch, I thought JsonValueListLength() might be =
worth optimizing, since it is O(n). I tried adding an ntotal_items field =
to JsonValueList to track the total number of items, similar to the last =
pointer that is only meaningful in the base chunk. But that did not help =
in my test, and I realized JsonValueListLength() is not on the hottest =
path, so I dropped that idea.
>=20
> =46rom the MacOS Instruments tool, the most expensive parts seem to be =
fillJsonbValue, JsonbIteratorNext, cmp_var_common, and cmp_numerics. But =
those look like separate topics.
>=20
> Overall, this looks like a solid patch.
>=20

Forgot to mention that, to run the tests, I turned off debug and =
assertion, and compiled with -O2.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/