Re: POC: PLpgSQL FOREACH IN JSON ARRAY

Hi

čt 23. 7. 2026 v 20:07 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:
>
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> > st 1. 7. 2026 v 23:40 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:
> >> * it's not clear to me that these routines have any business knowing
> >> about the "target type"; they certainly shouldn't contain comments
> >> alluding to PLpgSQL's conversion abilities. I think probably you
> >> just want them to pass back the data type they are producing and
> >> let PLpgSQL decide whether it wants to convert or not.
>
> > The iterator should know the target type for more reasons:
>
> I wasn't super convinced by these arguments, so today I tried to
> benchmark the performance question by asking Claude Code to do
> that. It came up with some interesting results, notably that this
> only matters for composite types not scalars. It also suggested
> that we could make the API cleaner with minimal performance
> loss by inventing a separate "coerce()" callback. As presented,
> its patch is a mess because it chose to allow run-time selection
> among several different ways, which of course we wouldn't do.
> But anyway, take a look at that option and see if it makes sense
> to you.
>
> (I should note that the benchmarking was done in a VM on my laptop.
> So it's hardly an industrial-strength setup, but since we only
> care about single-threaded performance here, I think the results
> are probably reasonably trustworthy. Feel free to reproduce for
> yourself though.)
>

The result on my notebook - lenovo T520, -O2, no assertions

===== PG_FOREACH_BENCH_MODE=0 =====
case min ms result
f_int / int100k int target, scalar elems 24.988 5000050000
f_num / int100k numeric target, scalar elems 33.752 5000050000
f_text / text100k text target, scalar elems 29.191 788895
f_jsonb / int100k jsonb target, scalar elems 33.547 100000
f_jsonb_obj / obj50k jsonb target, object elems 24.883 1250025000
f_comp / obj50k composite target, object elems 67.967 1250025000
f_arr_int / int100k control, pre-existing array 10.973 5000050000
f_sql_int / int100k control, FOR IN SELECT 60.821 5000050000

===== PG_FOREACH_BENCH_MODE=1 =====
case min ms result
f_int / int100k int target, scalar elems 47.854 5000050000
f_num / int100k numeric target, scalar elems 71.717 5000050000
f_text / text100k text target, scalar elems 51.489 988895
f_jsonb / int100k jsonb target, scalar elems 27.043 100000
f_jsonb_obj / obj50k jsonb target, object elems 20.252 1250025000
f_comp / obj50k composite target, object elems n/a ERROR:
cannot assign non-composite value to a record variable
f_arr_int / int100k control, pre-existing array 11.036 5000050000
f_sql_int / int100k control, FOR IN SELECT 61.008 5000050000

===== PG_FOREACH_BENCH_MODE=2 =====
case min ms result
f_int / int100k int target, scalar elems 24.370 5000050000
f_num / int100k numeric target, scalar elems 29.776 5000050000
f_text / text100k text target, scalar elems 21.440 788895
f_jsonb / int100k jsonb target, scalar elems 57.771 100000
f_jsonb_obj / obj50k jsonb target, object elems 19.961 1250025000
f_comp / obj50k composite target, object elems n/a ERROR:
cannot assign non-composite value to a record variable
f_arr_int / int100k control, pre-existing array 11.088 5000050000
f_sql_int / int100k control, FOR IN SELECT 60.062 5000050000

===== PG_FOREACH_BENCH_MODE=3 =====
case min ms result
f_int / int100k int target, scalar elems 24.492 5000050000
f_num / int100k numeric target, scalar elems 29.997 5000050000
f_text / text100k text target, scalar elems 31.293 788895
f_jsonb / int100k jsonb target, scalar elems 66.569 100000
f_jsonb_obj / obj50k jsonb target, object elems 25.780 1250025000
f_comp / obj50k composite target, object elems 72.403 1250025000
f_arr_int / int100k control, pre-existing array 11.008 5000050000
f_sql_int / int100k control, FOR IN SELECT 60.847 5000050000

I think the overhead of FOR IN SELECT depends on size of JSON array.
This is full query execution versus simple expr evaluation. Probably
for 1k or 10k arrays the FOR IN SELECT can be more significantly
slower than FOR IN ARRAY.

The main problem of modes different from zero is not a little bit
worse performance. Main problem is some casting artefacts. Any mode
has different artefacts, it is hard to choose some nice combination -
AI describes these artefacts too.

Modes 1 and 2 doesn't supports composite targets
Mode 1 for scalar has problem with numeric types - plpgsql coerce uses
only implicit casts, and then cast json string to varchar doesn't
remove quotes, and cast json number to numeric doesn't work for
decimal numbers
Mode 1 + coerce method can work (for scalars and composites) - there
is only overhead of one more packing and one more unpacking of jsonb
value
Mode 2 + corce method (for composites) has problem with strings when
target is json or jsonb - numbers and composites are ok

From mentioned list I think so only modes 0 and 1 + (coerce for all
types) is useable - other modes just cannot to do necessary
transformations for jsonb

The overhead of mode1 is in my run about 2x slower, and in your run
about 2x slower too. This is significant overhead, and at the end FOR
IN SELECT is faster :-/

Regards

Pavel

> regards, tom lane
>