Efficiency test of several function usages of pgsql database json

Efficiency test of several function usages of pgsql database json

Comparison of efficiency tests on several operators of Pgsql
1. Json::->> and->>

Test method: A single run 100 times, run 10 single time to take average time.
Test result:->> efficiency is about 5% high

Functional differences:
Json::->> need to convert objects to jsonb before they are used, and then perform->> operations, which is more time-consuming than->>.
So if we need to do a JSONB operation on the returned object, when we use the jsonb_* correlation function,
It is recommended to use jsonb_* instead of Jsonb_*_text, which converts the Jsonb object of the result to text, which is more than two times jsonb <--> text conversion operation.

2. Any and in
SELECT * FROM table where column in (' 1 ', ' 3 ', ' 5 ', ' 7 ');
SELECT * FROM table where column any (' {1,3,5,7} ':: text[]);

Test method: A single run 100 times, run 10 single time to take average time.
Test result: In high efficiency about 5%

Functional differences:
If the parameter is passed in by itself, then it is recommended to use in () if the argument is a Jsonb object conversion.

3. When the query condition determines whether there is a value, use exists and the result is null
SELECT * FROM table1 a where exists (select 1 from jsonb_array_elements (a.value) as B where b->> ' goods_id ' = '? ');
SELECT * FROM table1 a where (select 1 from jsonb_array_elements (a.value) as B where b->> ' goods_id ' = '? ' Limit 1) I s not null;

Test method: A single run 100 times, run 10 single time to take average time.
Test Result: Exists efficiency is high 8-10% about.
Functional difference: Exists only compares not manipulating data, is not NULL has action to manipulate data.

4. False and 1 = 2
SELECT * from table where false;
It is recommended to use false.

Efficiency test of several function usages of pgsql database json