Hi čt 23. 7. 2026 v 20:07 odesílatel Tom Lane <[email protected]> napsal: > > Pavel Stehule <[email protected]> writes: > > st 1. 7. 2026 v 23:40 odesílatel Tom Lane <[email protected]> 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 >
