Re: Jsonb extraction very slow

On 8/16/16 10:19 AM, Tom Lane wrote:
> [ 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.


I suspect that the ExtendedObject stuff makes this even more
appealing... it would certainly be nice if we only needed to pay the
expansion cost once (assuming no one dirtied the expanded object). I
certainly think there's more need for this kind of thing as the use of
JSON expands.

Perhaps that's part of what Robert was suggesting recently with moving
datums around the executor instead of tuples.

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

I've since heard that chaining -> is a really bad idea compared to #>,
which is unfortunately because -> is the normal idiom in other languages
(and what I suspect everyone will use by default). I've wondered if an
expanded object version of json might be expanding only top-level keys
(and maybe only as needed), and then -> is actually just a pointer to
the originally expanded data. A chained -> then wouldn't need to
duplicate everything... and in fact might be able to do it's expansion
in the original object so that subsequent references to that key
wouldn't need to re-expand it. I don't think the current EO framework
supports that, but it doesn't seem impossible to add...
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461