Re: [GENERAL] problem changing jsonb attribute to null value

[Dmitry Dolgov]

> On 15 November 2017 at 22:54, RODRIGUEZ CORTES MARIO IGNACIO <
ignacio.cor...@inegi.org.mx> wrote:
>
> I have a problem with a record in a jsonb type table, I'm trying to
> change the value of an attribute to null but it leaves me all the
> content in null and not just the value
>
> prueba=# select jsonb_set('{"v" : 0}'::jsonb, '{"v"}', to_jsonb(null));
> ERROR:  no se pudo determinar el tipo polimórfico porque el tipo de
> entrada es «unknown»
>
> defining null value as a text type:
>
> prueba=# select jsonb_set('{"v" : 0}'::jsonb, '{"v"}',
> to_jsonb(null::text));
>  jsonb_set
> ---
>
> (1 fila)
>
> it leaves the record in null, when I hope it leaves it with the null
> value in attribute "v": {"v": null}

I think something like this should work:

=# select jsonb_set('{"v" : 0}'::jsonb, '{"v"}', 'null'::jsonb);
  jsonb_set
-
 {"v": null}
(1 row)

is that what you want?

Re: [GENERAL] Error: "cached plan must not change result type"

[Dmitry Dolgov]

>On 9 Oct 2017 13:13, "Durumdara"  wrote:
>
> "cached plan must not change result type"

As far as I remember, this kind of errors you can get from a prepared
statement execution, when the result type of it was changed (as in your
case by adding a column to a table that provides the data for a prepared
statement).

Re: [GENERAL] performance considerations of jsonb vs separate rows

[Dmitry Dolgov]

> On 19 June 2017 at 21:29, Rob Nikander  wrote:
>
> I'm wondering about the tradeoffs, specifically: is it possible to update
one piece of a jsonb value without having to rewrite the entire field?
There are cases where that data field was getting pretty big (500kb). Would
you expect any obvious performance differences between these two options?

Unfortunately no, an entire jsonb field has to be written back even if
you've touched only one key.
>From my own benchmarks it looks like you'll scarcely notice this (e.g. in
comparison with MongoDB) only if you work
with small enough documents (about 2kb), and everything above this limit
more or less seriously hit the performance.
You can take a look at this presentation [1] from Oleg Bartunov, it
contains results of some benchmarks (from slide 44).

[1]: http://www.sai.msu.su/~megera/postgres/talks/jsonb-pgconf.us-2017.pdf

Re: [GENERAL] Insert performance and disk usage in JSON vs JSONB

[Dmitry Dolgov]

On 15 May 2017 at 19:25, Ronny Abraham  wrote:
>
> What’s interesting is that inserting to JSONB is slightly faster than
inserting to JSON.
>
> Maybe that’s because my JSON has a flat structure (no nesting), or maybe
I am doing something else wrong?

I assume it's because your json documents (10 fields) are not big enough.
If you'll try the same tests
with something like 1000 keys for each document, there should be noticeable
difference (at least in my
case it was like that).

Re: [GENERAL] MongoDB 3.2 beating Postgres 9.5.1?

[Dmitry Dolgov]

Hi, Paul

I agree with Oleg, EDB benchmarks are strange sometimes. I did the same
benchmarks several months ago. I never noticed the cache influence back
then, so I tried to reproduce your situation now (on a 5*10^6 records
although). I started to play with db cache (using `echo 3 >
/proc/sys/vm/drop_cache`), and I see difference in time execution for two
subsequent queries, but `explain` info are almost identical, e.g. `shared
hit & read`:

```
benchmark=# explain (buffers, analyze, verbose) select data from
json_tables where data @> '{"name": "AC3 Case Red"}';
 QUERY PLAN


 Bitmap Heap Scan on public.json_tables  (cost=102.74..19001.47 rows=4999
width=1257) (actual time=740.556..215956.655 rows=454546 loops=1)
   Output: data
   Recheck Cond: (json_tables.data @> '{"name": "AC3 Case Red"}'::jsonb)
   Rows Removed by Index Recheck: 2114606
   Heap Blocks: exact=31624 lossy=422922
   Buffers: shared hit=1371 read=41
   ->  Bitmap Index Scan on json_tables_idx  (cost=0.00..101.49 rows=4999
width=0) (actual time=731.010..731.010 rows=454547 loops=1)
 Index Cond: (json_tables.data @> '{"name": "AC3 Case Red"}'::jsonb)
 Buffers: shared hit=1371 read=1005
 Planning time: 6.352 ms
 Execution time: 216075.830 ms
(11 rows)

benchmark=# explain (buffers, analyze, verbose) select data from
json_tables where data @> '{"name": "AC3 Case Red"}';
QUERY PLAN

---
 Bitmap Heap Scan on public.json_tables  (cost=102.74..19001.47 rows=4999
width=1257) (actual time=222.476..10692.703 rows=454546 loops=1)
   Output: data
   Recheck Cond: (json_tables.data @> '{"name": "AC3 Case Red"}'::jsonb)
   Rows Removed by Index Recheck: 2114606
   Heap Blocks: exact=31624 lossy=422922
   Buffers: shared hit=1371 read=41
   ->  Bitmap Index Scan on json_tables_idx  (cost=0.00..101.49 rows=4999
width=0) (actual time=214.736..214.736 rows=454547 loops=1)
 Index Cond: (json_tables.data @> '{"name": "AC3 Case Red"}'::jsonb)
 Buffers: shared hit=1371 read=1005
 Planning time: 0.089 ms
 Execution time: 10767.739 ms
(11 rows)
```

But I see almost the same execution time from mongodb `explain` (216075ms
for pg and 177784ms for mongo, which isn't so much I think):

```
DBQuery.shellBatchSize = 100; db.json_tables.find({"name": "AC3
Case Red"}).explain(true)
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "benchmark.json_tables",
"indexFilterSet" : false,
"parsedQuery" : {
"name" : {
"$eq" : "AC3 Case Red"
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"name" : 1
},
"indexName" : "name_1",
"isMultiKey" : false,
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 1,
"direction" : "forward",
"indexBounds" : {
"name" : [
"[\"AC3 Case Red\", \"AC3
Case Red\"]"
]
}
}
},
"rejectedPlans" : [ ]
},
   "executionStats" : {
"executionSuccess" : true,
"nReturned" : 454546,
"executionTimeMillis" : 177784,
"totalKeysExamined" : 454546,
"totalDocsExamined" : 454546,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 454546,
"executionTimeMillisEstimate" : 175590,
"works" : 454547,
"advanced" : 454546,
"needTime" : 0,
"needYield" : 0,
"saveState" : 8638,
"restoreState" : 8638,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 454546,
"alreadyHasObj" : 0,
"inputStage" : {