Thread: BUG #13868: Strange performance of type conversion in jsonb operations
BUG #13868: Strange performance of type conversion in jsonb operations
From
xtracoder@gmail.com
Date:
The following bug has been logged on the website: Bug reference: 13868 Logged by: Xtra Coder Email address: xtracoder@gmail.com PostgreSQL version: 9.5.0 Operating system: Windows Description: I suspect these 3 questions arise from some kind of bugs, because actual result seems somewhat weird to me: * why implicit type cast int=jsonb works, whereas explicit type cast via ::int or cast(jsonb as int) says: 'cannot cast type jsonb to integer' * why assignment int=text has same performance as int=int - the first one should have some significant performance penalty for parcing of text to int * why assignment int=jsonb->'int_field' is significantly slower than int=jsonb->>'int_field' - to my mind it should be opposite because value is already stored as int in jsonb To check if i'm missing something i've asked question at stackoverflow, but received now answer: http://stackoverflow.com/questions/34716098/strange-performance-of-postgresql-type-conversion-in-jsonb-operations Copy-pasting entire question here just for reference: - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - I'm thinking about implementation of 'global consts' via jsonb stored in a table. To check what can be expected in terms of performance I've created some simple perf test, which revealed same questions that I have no clear answer. - why implicit type cast `int=jsonb` works, whereas explicit type cast via `::int` or `cast(jsonb as int)` says: '_cannot cast type jsonb to integer_' - why assignment `int=text` has same performance as `int=int` - _the first one should have some significant performance penalty for parcing of text to int_ - why assignment `int=jsonb->'int_field'` is significantly slower than `int=jsonb->>'int_field'` - _to my mind it should be opposite because value is already stored as int in jsonb_ PS: extraction of JSONB from DB is rather quick - reading 10K jsonb takes 1 sec per 100K cycles. After that I will need rather excessive access to dictionary of `name->int` values and looking for the most performant implementation. The test: DO LANGUAGE plpgsql $$ DECLARE json_struct jsonb; json_primitive jsonb; str_val text; int_val int; int_val2 int; count int; BEGIN count = 0; json_struct = '{"val": 11}'; json_primitive = 11; str_val = 11; int_val = json_struct->'val'; raise notice 'int_val = %', int_val; int_val = json_struct->>'val'; raise notice 'int_val = %', int_val; LOOP --int_val = count; -- 90ms --int_val2 = int_val; -- 111ms = +21ms --int_val = json_primitive; -- 130ms = +40ms --int_val = json_struct->'val'; -- 160ms = +70ms --json_primitive = json_struct->'val'; -- 120ms = +30ms --int_val = str_val; -- 111ms = +21ms --int_val = json_struct->>'val'; -- 125ms = +35ms --int_val = cast(json_primitive as int); -- ERROR: cannot cast type jsonb to integer --int_val = json_primitive::int; -- ERROR: cannot cast type jsonb to integer --int_val = json_primitive::text::int; -- 140ms = +50ms count = count + 1; EXIT WHEN count > 100000; END LOOP; END; $$