jsonb_set performance degradation / multiple jsonb_set on multiple documents

From: Alexandru Lazarev <alexandru(dot)lazarev(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>, pgsql-performance(at)postgresql(dot)org
Subject: jsonb_set performance degradation / multiple jsonb_set on multiple documents
Date: 2019-03-15 16:02:02
Message-ID: CAL93h0EKPubhA0f3FhXyB8P2LaY11zBEo3aU+=GGF96QDWfnBQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-performance

Hi PostgreSQL Community.

I tried to rewrite some plv8 stored procedures, which process in bulk JSONB
documents, to PL/pgSQL.
A SP usually has to delete/update/add multiple key with the same document
and do it for multiple documents (~40K) in loop.

When updating a single key PL/pgSQL wins against plv8, but when I need to
update multiple keys with *jsonb_set*, timing increase linearly with number
of *jsonb_set*s and takes longer than similar SP in PLV8.
Below are test-cases I've used.

*QUESTION:* Is it expected behavior or I do something wrong or there are
some better approaches or we can treat datum as object?

test case:
PG 9.6, CentOS 7

CREATE TABLE public.configurationj2b
(
id integer NOT NULL PRIMARY KEY,
config jsonb NOT NULL
);
Each jsonb column has 3 top keys, and one of top-key ('data') has another
700-900 key-value pairs e.g. {"OID1":"Value1"}

PL/pgSQL SP
CREATE OR REPLACE FUNCTION public.process_jsonb()
RETURNS void AS
$BODY$
DECLARE
r integer;
cfg jsonb;
BEGIN
RAISE NOTICE 'start';
FOR r IN
SELECT id as device_id FROM devices
LOOP
select config into cfg from configurationj2b c where c.id = r;
--select jsonb one by one

-- MULTIPLE KEYs, Conditional Busiines Logic (BL) updates
* cfg := jsonb_set(cfg, '{data,OID1}', '"pl/pgsql1"');*

* IF cfg@>'{"data" : { "OID1":"pl/pgsql1"} }' THEN cfg :=
jsonb_set(cfg, '{data,OID2}', '"pl/pgsql2"'); END IF; IF
cfg@>'{"data" : { "OID2":"pl/pgsql2"} }' THEN cfg := jsonb_set(cfg,
'{data,OID3}', '"pl/pgsql3"'); END IF; IF cfg@>'{"data" : {
"OID3":"pl/pgsql3"} }' THEN cfg := jsonb_set(cfg, '{data,OID4}',
'"pl/pgsql4"'); END IF; IF cfg@>'{"data" : { "OID4":"pl/pgsql4"} }'
THEN cfg := jsonb_set(cfg, '{data,OID5}', '"pl/pgsql5"'); END IF;*

update configurationj2b c set config = cfg where c.id = r;

END LOOP;
RAISE NOTICE 'end';
RETURN;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

or in pseudo-code I would have

for-each child_jsonb do
begin
foreach (key-value in parent_jsonb) do
begin
* child_jsonb := jsonb_set(child_jsonb , '{key}', '"value"');*
end
update *child_jsonb * in db;
end;

plv8 snippet:
$BODY$var ids = plv8.execute('select id from devices');

var CFG_TABLE_NAME = 'configurationj2b';
var selPlan = plv8.prepare( "select c.config from " + CFG_TABLE_NAME + " c
where c.id = $1", ['int'] );
var updPlan = plv8.prepare( 'update ' + CFG_TABLE_NAME + ' set config = $1
where id = $2', ['json','int'] )

try {

for (var i = 0; i < ids.length; i++) {
var db_cfg = selPlan.execute([ids[i].id]);
var cfg = db_cfg[0].config;
var cfg_data = cfg['data'];
* cfg_data['OID1'] = 'plv8_01';*

* if (cfg_data['OID1'] == 'plv8_01') { cfg_data['OID2'] =
'plv8_02' }; if (cfg_data['OID2'] == 'plv8_02') {
cfg_data['OID3'] = 'plv8_03' } if (cfg_data['OID3'] ==
'plv8_03') { cfg_data['OID4'] = 'plv8_04' } if
(cfg_data['OID4'] == 'plv8_04') { cfg_data['OID5'] =
'plv8_05' }*

updPlan.execute([cfg, ids[i].id]);
plv8.elog(NOTICE, "UPDATED = " + ids[i].id);
}

} finally {
selPlan.free();
updPlan.free();
}

return;$BODY$

but for now plv8 has other issues related to resource consumption.

So could I get similar performance in PL/pgSQL?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michel Pelletier 2019-03-15 16:35:13 Re: jsonb_set performance degradation / multiple jsonb_set on multiple documents
Previous Message Gunther 2019-03-15 15:59:48 Re: Facing issue in using special characters

Browse pgsql-performance by date

  From Date Subject
Next Message Michel Pelletier 2019-03-15 16:35:13 Re: jsonb_set performance degradation / multiple jsonb_set on multiple documents
Previous Message Gunther 2019-03-15 15:59:48 Re: Facing issue in using special characters