Re: Abnormal JSON query performance

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> ... My only gripe (such as it is) is
> the verbosity of the json api. Question: is there any technical reason as
> to why this can't be worked into a simple cast?

> jsonb_populate_record(null::myrowtype, jsonb_column) ->
> jsonb_column::myrowtype

Hmm, good question. I think it could work about the same at execution,
but I'm not sure whether the cast parsing machinery would need extension.
(The pg_cast entry would presumably have to have casttarget = record.
I'm not sure that a named record type would match that without an extra
hack, which is something that might or might not be a good idea in the
long run. There are no casts-to-polymorphic-types at present.)

Now, from the standpoint of system design, the key question is whether
we're sufficiently convinced that the semantics of jsonb_populate_record
are The Right Thing that we're willing to give it pride of place as the
jsonb->record cast function. As long as it's just a function, you can
put different semantics beside it easily --- just write another function.
But as far as a cast goes, there can be only one.

I would certainly not have had that much faith in jsonb_populate_record's
choices initially, but maybe now with nearly five years of field
experience we'd be good with making that commitment.

Another point is that the need to create a named composite type is
a usability strike against the cast syntax anyway. Personally,
if I'm going to write it as a lateral function call, I'd much
rather write out the fields I want in-line, a la

SELECT ... FROM ...some tables...,
jsonb_to_record(jsonbcol) AS j(id int, name text, price numeric)
WHERE ...

which is something you can do today.

regards, tom lane