Postgres Pro
Downloads
Home   >   mailing lists

Thread: jsonb value retrieval performance

jsonb value retrieval performance

From
Tom Smith
Date:
Hello:

Does JSONB storage has some internal indexing(like hasmap)
to fast look up a value given a key?
I have a jsonb doc with two level keys
(parentKey: {childKey:value}}
there are maybe 2000 parent keys per doc and 100 child keys per parent key

and I am trying to get value via  jsonb->parentKey->childKey
it seems it is very slow. 
Would it be actually faster to use top level key only and parse it at client side?

Thanks



Re: jsonb value retrieval performance

From
Teodor Sigaev
Date:
> and I am trying to get value via  jsonb->parentKey->childKey
> it seems it is very slow.
> Would it be actually faster to use top level key only and parse it at client side?

Suppose, most time is spent for decompressing huge value, not for actual search
inside jsonb. If so, we need to implement some search method which decompress
some chunks of jsonb.


Could you send to me an example of that jsonb?



--
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
                                                    WWW: http://www.sigaev.ru/

Re: jsonb value retrieval performance

From
Teodor Sigaev
Date:
> Suppose, most time is spent for decompressing huge value, not for actual search
> inside jsonb. If so, we need to implement some search method which decompress
> some chunks of jsonb.
On artificial example:
%SAMP IMAGE      FUNCTION             CALLERS
  92.9 postgres   pglz_decompress      toast_decompress_datum


--
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
                                                    WWW: http://www.sigaev.ru/

Re: jsonb value retrieval performance

From
Tom Smith
Date:
It can be any jsonb so I am asking a general question of the implementaion
for each jsonb storage (not about GIN or table wide indexing, but only within
a single jsonb item in a single row.

A sample would be like (no quotes)

{
a1: {b1:v1, b2:v2, b100:v100}

a3000: {c1:x1, c2: x2. c200: v200
}

you get the idea.  I wonder how postgresql does it when try

jsonb->a1->b1,

 does it read in the whole jsonb tree structure in memory
and get to v1  or it has some optimization so only get v1 instead
of reading in the whole structure.









On Tue, Sep 8, 2015 at 11:58 AM, Teodor Sigaev <teodor@sigaev.ru> wrote:
and I am trying to get value via  jsonb->parentKey->childKey
it seems it is very slow.
Would it be actually faster to use top level key only and parse it at client side?

 Suppose, most time is spent for decompressing huge value, not for actual search inside jsonb. If so, we need to implement some search method which decompress some chunks of jsonb.


Could you send to me an example of that jsonb?



--
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
                                                   WWW: http://www.sigaev.ru/

Re: jsonb value retrieval performance

From
Teodor Sigaev
Date:
>   does it read in the whole jsonb tree structure in memory
> and get to v1  or it has some optimization so only get v1 instead
> of reading in the whole structure.

it reads, untoasts and uncompresses whole value and then executes search. An
idea to fix that is a reading jsonb value by only needed chunks.


--
Teodor Sigaev                                   E-mail: teodor@sigaev.ru
                                                    WWW: http://www.sigaev.ru/