Attached set of patches with some jsonb optimizations that were made during
comparison of performance of ordinal jsonb operators and jsonpath operators.
1. Optimize JsonbExtractScalar(): It is better to use getIthJsonbValueFromContainer(cont, 0) instead of JsonIterator to get 0th element of raw-scalar pseudoarray. JsonbExtractScalar() is used in jsonb casts, so they speed up a bit.
2. Optimize operator #>>, jsonb_each_text(), jsonb_array_elements_text(): These functions have direct conversion (JsonbValue => text) only for jbvString scalars, but indirect conversion of other types of scalars (JsonbValue => jsonb => text) is obviously too slow. Extracted common subroutine JsonbValueAsText() and used in all suitable places.
3. Optimize JsonbContainer type recognition in get_jsonb_path_all(): Fetching of the first token from JsonbIterator is replaced with lightweight JsonbContainerIsXxx() macros.
4. Extract findJsonbKeyInObject(): Extracted findJsonbKeyInObject() from findJsonbValueFromContainer(), which is slightly easier to use (key string and its length is passed instead of filled string JsonbValue).
5. Optimize resulting value allocation in findJsonbValueFromContainer() and getIthJsonbValueFromContainer(): Added ability to pass stack-allocated JsonbValue that will be filled with the result of operation instead of returning unconditionally palloc()ated JsonbValue.
Patches #4 and #5 are mostly refactorings, but they can give small speedup
(up to 5% for upcoming jsonpath operators) due to elimination of unnecessary
palloc()s. The whole interface of findJsonbValueFromContainer() with JB_OBJECT
and JB_ARRAY flags always seemed a bit strange to me, so I think it is worth to
have separate functions for searching keys in objects and elements in arrays.
Performance tests:- Test data for {"x": {"y": {"z": i}}}: CREATE TABLE t AS SELECT jsonb_build_object('x', jsonb_build_object('y', jsonb_build_object('z', i))) js FROM generate_series(1, 3000000) i;
- Sample query: EXPLAIN (ANALYZE) SELECT js -> 'x' -> 'y' -> 'z' FROM t;
- Results: | execution time, ms query | master | optimized
------------------------------------------------------------------------------- {"x": {"y": {"z": i}}}js #> '{x,y,z}' | 1148.632 | 1005.578 -10%js #>> '{x,y,z}' | 1520.160 | 849.991 -40%(js #> '{x,y,z}')::numeric | 1310.881 | 1067.752 -20%(js #>> '{x,y,z}')::numeric | 1757.179 | 1109.495 -30%
js -> 'x' -> 'y' -> 'z' | 1030.211 | 977.267js -> 'x' -> 'y' ->> 'z' | 887.101 | 838.745(js -> 'x' -> 'y' -> 'z')::numeric | 1184.086 | 1050.462(js -> 'x' -> 'y' -> 'z')::int4 | 1279.315 | 1133.032(js -> 'x' -> 'y' ->> 'z')::numeric | 1134.003 | 1100.047(js -> 'x' -> 'y' ->> 'z')::int4 | 1077.216 | 991.995
js ? 'x' | 523.111 | 495.387js ?| '{x,y,z}' | 612.880 | 607.455js ?& '{x,y,z}' | 674.786 | 643.987js -> 'x' -> 'y' ? 'z' | 712.623 | 698.588js @> '{"x": {"y": {"z": 1}}}' | 1154.926 | 1149.069
jsonpath:js @@ '$.x.y.z == 123' | 973,444 | 912,08 -5%
{"x": i, "y": i, "z": i}jsonb_each(js) | 2281.577 | 2262.660jsonb_each_text(js) | 2603.539 | 2112.200 -20% [i, i, i]jsonb_array_elements(js) | 1255.210 | 1205.939jsonb_array_elements(js)::numeric | 1662.550 | 1576.227 -5%jsonb_array_elements_text(js) | 1555.021 | 1067.031 -30%
js @> '1' | 798.858 | 768.664 -4%js <@ '[1,2,3]' | 820.795 | 785.086 -5%js <@ '[0,1,2,3,4,5,6,7,8,9]' | 1214.170 | 1165.289 -5%
As it can be seen, #> operators are always slower than equivalent series of ->.
I think it is caused by array deconstruction in "jsonb #> text[]".
--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company