Re: Abnormal JSON query performance

On Wed, May 16, 2018 at 11:07:59AM -0400, Tom Lane 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.

Should this be a TODO item?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +