Performance of JSON_TABLE vs jsonb_to_recordset
Hello hackers,
When playing with JSON_TABLE, I tried to replace tenk1 in regression tests
with a view based on JSON_TABLE, with the same content, and discovered
that for one sub-optimal query it's execution duration increased many-fold.
With the preparation script attached, I see the following durations
(for a build compiled by clang 18.1.3 with -O3):
explain (verbose, analyze)
select
(select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1)))
from tenk1 o;
-- original tenk1
Execution Time: 4769.481 ms
explain (verbose, analyze)
select
(select max((select i.unique2 from jsonb_rs_tenk1 i where i.unique1 = o.unique1)))
from jsonb_rs_tenk1 o;
-- Function Call: jsonb_to_recordset...
Execution Time: 6841.767 ms
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())
Just for fun I've tested also XMLTABLE with the similar content:
explain (verbose, analyze)
select
(select max((select i.unique2 from xml_tenk1 i where i.unique1 = o.unique1)))
from xml_tenk1 o;
-- Table Function Call: XMLTABLE...
Execution Time: 1235066.636 ms
Maybe it's worth to add a note to the JSON_TABLE() documentation saying that
jsonb_to_recordset is (inherently?) more performant when processing arrays
of flat structures for users not to re-discover this fact...
Best regards,
Alexander
Attachments:
Alexander Lakhin <exclusion@gmail.com> writes:
When playing with JSON_TABLE, I tried to replace tenk1 in regression tests
with a view based on JSON_TABLE, with the same content, and discovered
that for one sub-optimal query it's execution duration increased many-fold.
With the preparation script attached, I see the following durations
(for a build compiled by clang 18.1.3 with -O3):
explain (verbose, analyze)
select
(select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1)))
from tenk1 o;
-- original tenk1
Execution Time: 4769.481 ms
Hm, I get about 13 ms for that example. Do you have some really
expensive debugging infrastructure enabled, perhaps?
regards, tom lane
I wrote:
Alexander Lakhin <exclusion@gmail.com> writes:
explain (verbose, analyze)
select
(select max((select i.unique2 from tenk1 i where i.unique1 = o.unique1)))
from tenk1 o;
-- original tenk1
Execution Time: 4769.481 ms
Hm, I get about 13 ms for that example. Do you have some really
expensive debugging infrastructure enabled, perhaps?
Oh, never mind, now I see you are testing a version of the table
with no indexes, rather than the way it's set up in the regression
database. Apologies for the noise.
regards, tom lane
Alexander Lakhin <exclusion@gmail.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