Hi:
For all the people who are interested in this topic, I will post a
planner support function soon, you can check that then.
The updated patch doesn't need users to change their codes and can get
better performance. Thanks for all the feedback which makes things better.
To verify there is no unexpected stuff happening, here is the performance
comparison between master and patched.
create table tb(a jsonb);
insert into tb select '{"a": true, "b": 23.3333}' from generate_series(1,
100000)i;
Master:
select 1 from tb where (a->'b')::numeric = 1;
Time: 31.020 ms
select 1 from tb where not (a->'a')::boolean;
Time: 25.888 ms
select 1 from tb where (a->'b')::int2 = 1;
Time: 30.138 ms
select 1 from tb where (a->'b')::int4 = 1;
Time: 32.384 ms
select 1 from tb where (a->'b')::int8 = 1;\
Time: 29.922 ms
select 1 from tb where (a->'b')::float4 = 1;
Time: 54.139 ms
select 1 from tb where (a->'b')::float8 = 1;
Time: 66.933 ms
Patched:
select 1 from tb where (a->'b')::numeric = 1;
Time: 15.203 ms
select 1 from tb where not (a->'a')::boolean;
Time: 12.894 ms
select 1 from tb where (a->'b')::int2 = 1;
Time: 16.847 ms
select 1 from tb where (a->'b')::int4 = 1;
Time: 17.105 ms
select 1 from tb where (a->'b')::int8 = 1;
Time: 16.720 ms
select 1 from tb where (a->'b')::float4 = 1;
Time: 33.409 ms
select 1 from tb where (a->'b')::float8 = 1;
Time: 34.660 ms