Re: Abnormal JSON query performance

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

Should this be a TODO item?

-- 
  Bruce Momjian  <bruce@momjian.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 +