Re: Abnormal JSON query performance

Dmitry Dolgov <9erthalion6@gmail.com> writes:
>> On 16 May 2018 at 05:59, David G. Johnston <david.g.johnston@gmail.com> wrote:
>> On Tuesday, May 15, 2018, reader 1001 <007reader@gmail.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