Re: Abnormal JSON query performance

2018-05-16 17:07 GMT+02:00 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:

> 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.
>

Just note. If SQL/JSON will be implemented, then this discussion is
useless, because JSON_TABLE function allows to read more values per one
call.

Regards

Pavel

>
> regards, tom lane
>