Re: Updating jsonb rows

Here are some final result timings on Temp Tables vs JSONB. _25_sec means
.25 seconds. This is the result of ~2,300 executions of my function using
various input parameters.
[image: Inline image 1]

Basically what it shows is that even though the average response time for
Temp Tables is superior, JSONB wins in consistency. The Temp Tables
approach has some horrible outliers.

The max response time for Temp Tables was over 10.8 seconds, while the max
for JSONB was 1.2 seconds.

Obviously, nobody should make any generalizations from my very specific
results, but if you need to get off of Temp Tables, going to JSONB is worth
considering.

Mike

On Mon, Aug 14, 2017 at 12:33 PM, Michael Moore <michaeljmoore(at)gmail(dot)com>
wrote:

> Probably the solution is "don't try to use JSON as temp tables for
> anything but very small datasets". As you know, I'm converting an
> application that uses temp tables to NOT use temp tables because it needs
> to run in a read-only database. JSONB seemed to be the way to go because
> you can almost treat them the same as temp tables. I think in the end, it
> will be "fast enough", but just not as fast as temp tables. Thanks for your
> help!
> Mike
>
>
> On Mon, Aug 14, 2017 at 11:12 AM, David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
>> On Mon, Aug 14, 2017 at 10:35 AM, Michael Moore <michaeljmoore(at)gmail(dot)com>
>> wrote:
>>
>>> Basically what I am doing is trying to simulate a SQL UPDATE statement.
>>> currently I am doing this, and* it works, but it is slow*, so I am
>>> looking for a better way.
>>>
>>
>> ​I don't presently know a better solution in PostgreSQL.
>>
>> Dave
>> ​
>>
>
>