Re: Performance of JSON_TABLE vs jsonb_to_recordset

Alexander Lakhin <exclusion(at)gmail(dot)com> writes:
> explain (verbose, analyze)
> select
> (select max((select i.unique2 from jsontable_tenk1 i where i.unique1 = o.unique1)))
> from jsontable_tenk1 o;
> -- Table Function Call: JSON_TABLE...
> Execution Time: 288310.131 ms
> (with 63% of time spent inside ExecEvalJsonExprPath())

Yeah, I looked at this with perf too, and what I'm seeing is

- 55.87% ExecEvalJsonExprPath
- 39.30% JsonPathValue
- 37.63% executeJsonPath
- 34.87% executeItem (inlined)
- executeItemOptUnwrapTarget
- 32.39% executeNextItem
- 31.02% executeItem (inlined)
- 30.90% executeItemOptUnwrapTarget
- 26.81% getKeyJsonValueFromContainer
14.35% getJsonbOffset (inlined)
- 4.90% lengthCompareJsonbString (inlined)
3.19% __memcmp_avx2_movbe
- 2.32% palloc
1.67% AllocSetAlloc
0.93% fillJsonbValue
1.18% executeNextItem
0.51% findJsonbValueFromContainer
- 1.04% jspGetNext
0.72% jspInitByBuffer
- 1.46% check_stack_depth
stack_is_too_deep (inlined)
0.61% jspInitByBuffer
- 9.82% ExecGetJsonValueItemString (inlined)
- 8.68% DirectFunctionCall1Coll
- 8.07% numeric_out
- 6.15% get_str_from_var
- 2.07% palloc
- 1.80% AllocSetAlloc
0.72% AllocSetAllocChunkFromBlock (inlined)
1.28% init_var_from_num
- 1.61% namein
0.90% __strlen_avx2
0.52% palloc0
- 0.74% int4in
0.69% pg_strtoint32_safe

Depressingly small amount of useful work being done there compared
to the management overhead. Seems like some micro-optimization
in this area could be a useful project for v18.

regards, tom lane