Re: Optimize JsonbContainerTypeName by reordering type checks - Mailing list pgsql-hackers

On Tue, 9 Sept 2025 at 01:28, Chao Li <li.evan.chao@gmail.com> wrote:
> With 3 runs average tps, this patch makes ~0.8% improvement for jsonb_typeof().
>
> My test script is attached, it can run against any database. And attached v2 patch file has a tiny change over v1.

I'd say this is wishful thinking. You're saying that with the overhead
of planning, executor startup, most of executor run and executor
shutdown and subsequent cleanup, you're expecting a speedup from
swapping the order of operations in this function when you're calling
it once!? That could be true if the function was very expensive, but
this is not an expensive function. It'll amount to a few dozen
instructions for the function out of several 10s of millions, when you
consider the cost of processing the entire query.

To try to see if your patch makes any difference, I modified your test
to call the function 1 million times:

SELECT jsonb_typeof(
    CASE x%10
        WHEN 0 THEN '{"key1": "value"}'::jsonb
        WHEN 1 THEN '{"key2": "value"}'::jsonb
        WHEN 2 THEN '{"key3": "value"}'::jsonb
        WHEN 3 THEN '[1, 2, 3]'::jsonb
        WHEN 4 THEN '[1, 2, 3]'::jsonb
        ELSE '123'::jsonb
    END
) from generate_Series(1,1000000) x

Ran it 10 times on my Zen4 laptop:

$ echo master && for i in {1..10}; do pgbench -n -f
test_jsonb_type.sql -T 10 postgres | grep latency; done
master
latency average = 209.944 ms
latency average = 210.247 ms
latency average = 210.412 ms
latency average = 210.665 ms
latency average = 210.312 ms
latency average = 210.254 ms
latency average = 210.188 ms
latency average = 210.384 ms
latency average = 210.238 ms
latency average = 210.947 ms

$ echo patched && for i in {1..10}; do pgbench -n -f
test_jsonb_type.sql -T 10 postgres | grep latency; done
patched
latency average = 217.047 ms
latency average = 219.044 ms
latency average = 217.396 ms
latency average = 217.775 ms
latency average = 218.465 ms
latency average = 218.515 ms
latency average = 217.576 ms
latency average = 217.880 ms
latency average = 217.580 ms
latency average = 218.497 ms

That's 3.5% slower.

You may find it better to learn about "perf record" or even "perf
top".  These are useful tools to see where CPU time is being spent. If
you have a query you'd like to make faster, you might get some
indication of something that can be done to improve the situation with
these tools. They show which functions the CPU is spending the most
time in. Here's the perf top output in master for the query I posted
above:

  15.19%  postgres          [.] ExecInterpExpr
   4.67%  postgres          [.] AllocSetReset
   4.34%  postgres          [.] ExecMakeTableFunctionResult
   4.23%  postgres          [.] palloc0
   4.02%  postgres          [.] BufFileWrite
   3.92%  postgres          [.] BufFileReadCommon
   3.84%  postgres          [.] ExecScan
   3.27%  postgres          [.] heap_form_minimal_tuple
   3.05%  postgres          [.] AllocSetAlloc
   2.73%  postgres          [.] JsonbIteratorNext
   2.56%  postgres          [.] heap_fill_tuple
   2.41%  postgres          [.] tts_minimal_getsomeattrs
   2.27%  libc.so.6         [.] __memmove_avx_unaligned_erms
   2.23%  postgres          [.] ExecStoreMinimalTuple
   1.83%  postgres          [.] JsonbExtractScalar
   1.60%  postgres          [.] fill_val

JsonbExtractScalar isn't taking up much of the time, so it probably
isn't a very interesting area to focus optimisation work for this
query. You could maybe get rid of the CASE statement to remove some of
the ExecInterpExpr overhead, but still have my doubts if you'll ever
see this taking up a meaningful percentage of the overall CPU time.

David