Re: [PATCH] Optimize json_lex_string by batching character copying

From: Andrew Dunstan <andrew(at)dunslane(dot)net>
To: Andres Freund <andres(at)anarazel(dot)de>, Jelte Fennema <Jelte(dot)Fennema(at)microsoft(dot)com>
Cc: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>, Merlin Moncure <mmoncure(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Andrew Dunstan <andrew(dot)dunstan(at)2ndquadrant(dot)com>, Stephen Frost <sfrost(at)snowman(dot)net>, John Naylor <john(dot)naylor(at)enterprisedb(dot)com>
Subject: Re: [PATCH] Optimize json_lex_string by batching character copying
Date: 2022-07-13 15:03:43
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 2022-06-24 Fr 20:18, Andres Freund wrote:
> Hi,
> On 2022-06-24 08:47:09 +0000, Jelte Fennema wrote:
>> To test performance of this change I used COPY BINARY from a JSONB table
>> into another, containing fairly JSONB values of ~15kB.
> This will have a lot of other costs included (DML is expensive). I'd suggest
> storing the json in a text column and casting it to json[b], with a filter
> ontop of the json[b] result that cheaply filters it away. That should end up
> spending nearly all the time somewhere around json parsing.
> It's useful for things like this to include a way for others to use the same
> benchmark...
> I tried your patch with:
> DROP TABLE IF EXISTS json_as_text;
> CREATE TABLE json_as_text AS SELECT (SELECT json_agg(row_to_json(pd)) as t FROM pg_description pd) FROM generate_series(1, 100);
> VACUUM FREEZE json_as_text;
> SELECT 1 FROM json_as_text WHERE jsonb_typeof(t::jsonb) = 'not me';

I've been doing some other work related to json parsing and John
referred me to this. But it's actually not the best test for pure json
parsing - casting to jsonb involves some extra work besides pure
parsing. Instead I've been using this query with the same table, which
should be almost all json parsing:

select 1 from json_as_text where t::json is null;



Andrew Dunstan

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2022-07-13 15:11:34 Re: Bug: Reading from single byte character column type may cause out of bounds memory reads.
Previous Message Alvaro Herrera 2022-07-13 14:56:14 Re: make install-world fails sometimes in Mac M1