Re: Abnormal JSON query performance

through my testing, I confirmed Tom's guess - performance benefit of going
beyond the first level keys is negligible. Getting top level key's JSON
part of the document and use ->>, -> operators for extracting data works
very well, even if I had to go down multiple levels of hierarchy. Thanks a
lot!

On Wed, May 16, 2018 at 8:07 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Dmitry Dolgov <9erthalion6(at)gmail(dot)com> writes:
> >> On 16 May 2018 at 05:59, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com>
> wrote:
> >> On Tuesday, May 15, 2018, reader 1001 <007reader(at)gmail(dot)com> wrote:
> >>> My question remains for hierarchical keys in a JSON document. If I
> have a
> >>> document like below, I clearly can extract key1 using the described
> rowtype
> >>> definition. How can I specify selected keys deeper in the document,
> e.g.
> >>> key3 and key5?
>
> >> I believe you would need a type for each subtree and apply the function
> >> multiple times with the result of one feeding the next.
>
> > Yes, you need to defined a type for each subtree, but as far as I can
> > tell it's not necessary to apply the function multiple times,
> > `jsonb_populate_record` can work with nested types, so it's enough
> > just to have every new type included in the previous one.
>
> FWIW, I really doubt that there's much performance win from going further
> than the first-level keys. I suspect most of the cost that the OP is
> seeing comes from fetching the large JSONB document out of toast storage
> multiple times. Fetching it just in a single jsonb_populate_record()
> call will fix that. So I'd just return the top-level field(s) as jsonb
> column(s) and use the normal -> or ->> operators to go further down.
>
> The vague ideas that I've had about fixing this type of problem
> automatically mostly center around detecting the need for duplicate
> toast fetches and doing that just once. For data types having "expanded"
> forms, it's tempting to consider also expanding them during the fetch,
> but that's less clearly a win.
>
> regards, tom lane
>