A couple of weeks ago I wrote about wanting a jsonb delete operator in 9.4, and yesterday evening I decided to have a go at writing some functions in C.
In the end all I actually did yesterday was make a mess and read a lot of existing code, but persisting this evening I’ve managed to put together some functions that appear to work. I’m not confident to say they’re efficient (or even correct; and they certainly shouldn’t be put on production systems), but I thought it’d be useful to benchmark them.
I’ve also added in a concatenate operator after reading Matthew Schinckel’s post.
First install the C shared library:
# make install
Then install the functions and operators, these functions are named jsonb_delete and jsonb_concat:
test=# \i jsonb_opx.sql CREATE FUNCTION COMMENT CREATE OPERATOR COMMENT CREATE FUNCTION COMMENT CREATE OPERATOR COMMENT CREATE FUNCTION COMMENT CREATE OPERATOR COMMENT CREATE FUNCTION COMMENT CREATE OPERATOR COMMENT
Then install the SQL versions for comparison, these functions are named jsonb_delete_left and jsonb_concat_left:
test=# \i jsonb_opx_sql_comparison.sql CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION
Test 1 – deleteing a key
This is actually an SQL wrapper to the C function for (jsonb, text[]), breaking that up
to have a separate version would be more efficient, but that should be a trivial task.
test=# \timing on
Timing is on.
test=# SELECT '{"a": 1, "b": 2, "c": 3}'::jsonb - 'b'::text;
?column?
------------------
{"a": 1, "c": 3}
(1 row)
Time: 7.099 ms
The above is hitting the C function; from this point onwards I’ll just hit the functions directly:
test=# SELECT jsonb_delete('{"a": 1, "b": 2, "c": 3}'::jsonb, 'b');
jsonb_delete
------------------
{"a": 1, "c": 3}
(1 row)
Time: 6.220 ms
Now the original SQL version:
test=# SELECT jsonb_delete_left('{"a": 1, "b": 2, "c": 3}'::jsonb, 'b');
jsonb_delete_left
-------------------
{"a": 1, "c": 3}
(1 row)
Time: 14.570 ms
Now to benchmark for a large quantity of rows:
test=# EXPLAIN ANALYZE SELECT jsonb_delete(('{"a":' || x || ', "b":' || x*2 || ', "c":' || x*x || '}')::jsonb, 'b')
FROM generate_series(1,10000) x;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Function Scan on generate_series x (cost=0.00..300.00 rows=1000 width=4) (actual time=10.407..263.489 rows=10000 loops=1)
Planning time: 0.335 ms
Execution time: 290.192 ms
(3 rows)
Time: 293.254 ms
test=# EXPLAIN ANALYZE SELECT jsonb_delete_left(('{"a":' || x || ', "b":' || x*2 || ', "c":' || x*x || '}')::jsonb, 'b')
FROM generate_series(1,10000) x;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Function Scan on generate_series x (cost=0.00..300.00 rows=1000 width=4) (actual time=15.165..767.706 rows=10000 loops=1)
Planning time: 0.785 ms
Execution time: 803.258 ms
(3 rows)
Time: 809.088 ms
Whilst processing 1 row really doesn’t show any improvement (the timings for both varied in the 2~10ms range), with 10,000 rows the C version is just over twice as quick.
If these times stick out as particularly dire, it’s probably just because the machine I’m testing on is very old.
Test 2 – deleting multiple keys
test=# SELECT jsonb_delete('{"a": 1, "b": 2, "c": 3}'::jsonb, ARRAY['a','b']);
jsonb_delete
--------------
{"c": 3}
(1 row)
Time: 3.482 ms
test=# SELECT jsonb_delete_left('{"a": 1, "b": 2, "c": 3}'::jsonb, ARRAY['a','b']);
jsonb_delete_left
-------------------
{"c": 3}
(1 row)
Time: 3.613 ms
test=# EXPLAIN ANALYZE SELECT jsonb_delete(('{"a":' || x || ', "b":' || x*2 || ', "c":' || x*x || '}')::jsonb, ARRAY['a','b'])
FROM generate_series(1,10000) x;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Function Scan on generate_series x (cost=0.00..52.50 rows=1000 width=4) (actual time=5.805..177.507 rows=10000 loops=1)
Planning time: 1.646 ms
Execution time: 209.137 ms
(3 rows)
Time: 213.507 ms
test=# EXPLAIN ANALYZE SELECT jsonb_delete_left(('{"a":' || x || ', "b":' || x*2 || ', "c":' || x*x || '}')::jsonb, ARRAY['a','b'])
FROM generate_series(1,10000) x;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Function Scan on generate_series x (cost=0.00..300.00 rows=1000 width=4) (actual time=15.805..757.500 rows=10000 loops=1)
Planning time: 0.595 ms
Execution time: 789.272 ms
(3 rows)
Time: 793.229 ms
Results are similar; we’re essentially hitting the same C function.
Test 3 – Deleting matching jsonb key/value pairs
The C version of this function essentially loops round the left jsonb value looking up keys
in the right jsonb value. If it finds a matching key it does a string based comparison on
the values (treating nested jsonb as a string too) and if the values match as well then the key/value pair is removed.
test=# SELECT jsonb_delete('{"a": 1, "b": 2, "c": 3}'::jsonb, '{"a": 4, "b": 2}'::jsonb);
jsonb_delete
------------------
{"a": 1, "c": 3}
(1 row)
Time: 3.114 ms
test=# SELECT jsonb_delete_left('{"a": 1, "b": 2, "c": 3}'::jsonb, '{"a": 4, "b": 2}'::jsonb);
jsonb_delete_left
-------------------
{"a": 1, "c": 3}
(1 row)
Time: 6.899 ms
test=# EXPLAIN ANALYZE SELECT jsonb_delete(('{"a":' || x || ', "b":' || x*2 || ', "c":' || x*x || '}')::jsonb,
('{"a":' || x || ', "d":' || x*2 || ', "c":' || x*10 || '}')::jsonb)
FROM generate_series(1,10000) x;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Function Scan on generate_series x (cost=0.00..92.50 rows=1000 width=4) (actual time=8.452..238.210 rows=10000 loops=1)
Planning time: 0.428 ms
Execution time: 266.358 ms
(3 rows)
Time: 270.161 ms
test=# EXPLAIN ANALYZE SELECT jsonb_delete_left(('{"a":' || x || ', "b":' || x*2 || ', "c":' || x*x || '}')::jsonb,
('{"a":' || x || ', "d":' || x*2 || ', "c":' || x*10 || '}')::jsonb)
FROM generate_series(1,10000) x;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Function Scan on generate_series x (cost=0.00..340.00 rows=1000 width=4) (actual time=11.833..1206.990 rows=10000 loops=1)
Planning time: 0.759 ms
Execution time: 1248.481 ms
(3 rows)
Time: 1253.392 ms
There’s a bigger improvement here; it’s about 4.5 times quicker.
Test 4 – concatenation
The C function for this is essentially a cut n shut job on both jsonb values, blindly
pushing all the values onto the return value and leaving the lower level jsonb api
to do the deduplication:
test=# SELECT jsonb_concat('{"a": 1, "b": 2, "c": 3}'::jsonb, '{"a": 4, "d": 4, "z": 26}'::jsonb);
jsonb_concat
-------------------------------------------
{"a": 4, "b": 2, "c": 3, "d": 4, "z": 26}
(1 row)
Time: 3.028 ms
test=# SELECT jsonb_concat_left('{"a": 1, "b": 2, "c": 3}'::jsonb, '{"a": 4, "d": 4, "z": 26}'::jsonb);
jsonb_concat_left
-------------------------------------------
{"a": 4, "b": 2, "c": 3, "d": 4, "z": 26}
(1 row)
Time: 4.731 ms
Again nothing to get excited about. Testing on a larger quantity of rows shows a similar improvement to the jsonb -jsonb delete operator/function above:
test=# EXPLAIN ANALYZE SELECT jsonb_concat(('{"a":' || x || ', "b":' || x*2 || ', "c":' || x*x || '}')::jsonb,
('{"a":' || x || ', "d":' || x*2 || ', "c":' || x*10 || '}')::jsonb)
FROM generate_series(1,10000) x;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Function Scan on generate_series x (cost=0.00..92.50 rows=1000 width=4) (actual time=12.481..255.910 rows=10000 loops=1)
Planning time: 0.599 ms
Execution time: 285.357 ms
(3 rows)
Time: 288.615 ms
test=# EXPLAIN ANALYZE SELECT jsonb_concat_left(('{"a":' || x || ', "b":' || x*2 || ', "c":' || x*x || '}')::jsonb,
('{"a":' || x || ', "d":' || x*2 || ', "c":' || x*10 || '}')::jsonb)
FROM generate_series(1,10000) x;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Function Scan on generate_series x (cost=0.00..340.00 rows=1000 width=4) (actual time=13.931..1051.100 rows=10000 loops=1)
Planning time: 5.160 ms
Execution time: 1091.596 ms
(3 rows)
Time: 1103.165 ms
So in conclusion the results are nothing earth shattering, but there is a small improvement. Essentially all these functions are doing is iterating around the jsonb and building new return values; it’d be nice to see what someone more familiar with the jsonb internals at a lower level could come up with.