Re: Jsonb extraction very slow

Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> writes:
> On 8/11/16 8:45 AM, Tom Lane wrote:
>> What were you doing to "get ten keys out"? If those were ten separate
>> JSON operators, they'd likely have done ten separate decompressions.
>> You'd have saved something by having the TOAST data already fetched into
>> shared buffers, but it'd still hardly be free.

> Multiple -> or ->> operators, but all operating on the same field (which
> I thought would mean a single datum that would end up detoasted?).

No, that's going to work as I said. It'd be a useful thing to be able to
amortize the decompression work across multiple references to the field,
but currently there's no way to do that.

[ thinks for a bit... ] In principle we could have the planner notice
whether there are multiple references to the same Var of a varlena type,
and then cue the executor to do a pre-emptive detoasting of that field
of the input tuple slot. But it would be hard to avoid introducing some
regressions along with the benefits, I'm afraid.

> Some of these would have been nested ->/->>.

In a chain of functions only the first one would be paying the overhead
we're talking about here; though I'm not sure how efficient the case is
overall in JSONB.

regards, tom lane