Re: [PERFORM] Re: querying with index on jsonb slower than standard column. Why?

On Sat, Dec 13, 2014 at 12:05 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
> On 12/12/2014 08:20 PM, Tom Lane wrote:
>>
>> We can't just add the operator and worry about usability later;
>> if we're thinking we might want to introduce such an automatic
>> transformation, we have to be sure the new operator is defined in a
>> way that allows the transformation to not change any semantics.
>> What that means in this case is that if (jsonb ->> 'foo')::numeric
>> would have succeeded, (jsonb ->># 'foo') has to succeed; which means
>> it'd better be willing to attempt conversion of string values to
>> numeric, not just throw an error on sight.
>>
>>
>
>
> Well, I'm not 100% convinced about the magic transformation being a good
> thing.
>
> Json numbers are distinct from strings, and part of the justification for
> this is to extract a numeric datum from jsonb exactly as stored, on
> performance grounds. So turning round now and making that turn a string into
> a number if possible seems to me to be going in the wrong direction.

It's still better than doing the conversion every time. The niceness
of that implementation aside, I don't see how it can be considered the
wrong direction.