Horton

Re: Making jsonb_agg() faster

Chao Li <li.evan.chao@gmail.com>

View thread
From: Chao Li <li.evan.chao@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: jian he <jian.universality@gmail.com>, pgsql-hackers@lists.postgresql.org
Date: 2025-11-03T13:38:46Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Fix jsonb_object_agg crash after eliminating null-valued pairs.

  2. Micro-optimize datatype conversions in datum_to_jsonb_internal.

  3. Remove fundamentally-redundant processing in jsonb_agg() et al.

  4. Revise APIs for pushJsonbValue() and associated routines.

  5. Add a macro for the declared typlen of type timetz.


> On Nov 3, 2025, at 04:56, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 
> PFA v3, rebased over 8a27d418f, no substantive changes whatever.
> 
> regards, tom lane
> 

I am on vacation this week, I only find a hour in the evening and did an eyeball review without actually tracing and testing this patch set. Overall, the changes are solid and look good to me. Only a few small comments on 0001:

1 -  jsonpath_exec.c
```
@@ -2874,8 +2874,7 @@ executeKeyValueMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
 	{
 		JsonBaseObjectInfo baseObject;
 		JsonbValue	obj;
-		JsonbParseState *ps;
-		JsonbValue *keyval;
+		JsonbInState ps;
 		Jsonb	   *jsonb;
 
 		if (tok != WJB_KEY)
@@ -2889,7 +2888,8 @@ executeKeyValueMethod(JsonPathExecContext *cxt, JsonPathItem *jsp,
 		tok = JsonbIteratorNext(&it, &val, true);
 		Assert(tok == WJB_VALUE);
 
-		ps = NULL;
+		memset(&ps, 0, sizeof(ps));
+
```

In elsewhere of the patch, you all use “JsonbInState ps = {0}” to do the initialization, only this place uses memset(). Can we keep consistent and use {0} here also. I see there is a “continue” and a “break” before the memset(), maybe you want to avoid unnecessary initialization. I guess that is a tiny saving, but if you think that saving is worthwhile, I’m fine with keeping the current code.

2 - jsonb_util.c
```
+				case TIMETZOID:
+					/* pass-by-reference */
+					oldcontext = MemoryContextSwitchTo(outcontext);
+					v->val.datetime.value = datumCopy(v->val.datetime.value,
+													  false, 12);
```

Instead of hard-coding 12, can we use "sizeof(TimeTzADT)” for better readability?

3 - jsonb_plperl.c
```
+	{
+		/* XXX Ugly hack */
+		jsonb_state->result = palloc(sizeof(JsonbValue));
+		memcpy(jsonb_state->result, &out, sizeof(JsonbValue));
+	}
```
And
```
+	else
+	{
+		/* XXX Ugly hack */
+		jsonb_state->result = out;
+	}
```

You left two “ugly hack” comments. Maybe add a short description for why they are hack and what can be improved in the future.

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