Re: appendBinaryStringInfo stuff - Mailing list pgsql-hackers

On Tue, 20 Dec 2022 at 11:26, David Rowley <dgrowleyml@gmail.com> wrote:
> It would be good to see some measurements to find out how much adding
> the strlen calls back in would cost us.

I tried this out.  I'm not pretending I found the best test which
highlights how much the performance will change in a real-world case.
I just wanted to try to get an indication of if changing jsonb's
output function to make more use of appendStringInfoString instead of
appendBinaryStringInfo is likely to affect performance.

Also, in test 2, I picked a use case that makes quite a bit of use of
appendStringInfoString already and checked if inlining that function
would help improve things.  I imagine test 2 really is not
bottlenecked on appendStringInfoString enough to get a true idea of
how much inlining appendStringInfoString could really help (spoiler,
it helps quite a bit)

Test 1: See if using appendStringInfoString instead of
appendBinaryStringInfo hinders jsonb output performance.

setup:
create table jb (j jsonb);
insert into jb select row_to_json(pg_class) from pg_class;
vacuum freeze analyze jb;

bench.sql:
select sum(length(j::text)) from jb;

master (@3f28bd73):
$ pgbench -n -T 60 -f bench.sql -M prepared postgres | grep latency
latency average = 1.896 ms
latency average = 1.885 ms
latency average = 1.899 ms

  22.57%  postgres          [.] escape_json
  21.83%  postgres          [.] pg_utf_mblen
   9.23%  postgres          [.] JsonbIteratorNext.part.0
   7.12%  postgres          [.] AllocSetAlloc
   4.07%  postgres          [.] pg_mbstrlen_with_len
   3.71%  postgres          [.] JsonbToCStringWorker
   3.70%  postgres          [.] fillJsonbValue
   3.17%  postgres          [.] appendBinaryStringInfo
   2.95%  postgres          [.] enlargeStringInfo
   2.09%  postgres          [.] jsonb_put_escaped_value
   1.89%  postgres          [.] palloc

master + 0001-Use-appendStringInfoString-instead-of-appendBinarySt.patch

$ pgbench -n -T 60 -f bench.sql -M prepared postgres | grep latency
latency average = 1.912 ms
latency average = 1.912 ms
latency average = 1.912 ms (~1% slower)

  22.38%  postgres          [.] escape_json
  21.98%  postgres          [.] pg_utf_mblen
   9.07%  postgres          [.] JsonbIteratorNext.part.0
   5.93%  postgres          [.] AllocSetAlloc
   4.11%  postgres          [.] pg_mbstrlen_with_len
   3.87%  postgres          [.] fillJsonbValue
   3.66%  postgres          [.] JsonbToCStringWorker
   2.28%  postgres          [.] enlargeStringInfo
   2.15%  postgres          [.] appendStringInfoString
   1.98%  postgres          [.] jsonb_put_escaped_value
   1.92%  postgres          [.] palloc
   1.58%  postgres          [.] appendBinaryStringInfo
   1.42%  postgres          [.] pnstrdup

Test 2: Test if inlining appendStringInfoString helps

bench.sql:
select sum(length(pg_get_ruledef(oid))) from pg_rewrite;

master (@3f28bd73):
$ pgbench -n -T 60 -f bench.sql postgres | grep latency
latency average = 16.355 ms
latency average = 16.290 ms
latency average = 16.303 ms

static inline appendStringInfoString
$ pgbench -n -T 60 -f bench.sql postgres | grep latency
latency average = 15.690 ms
latency average = 15.575 ms
latency average = 15.604 ms (~4.4% faster)

David