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.
I am looking on your patch, and the message
+ + default: + elog(ERROR, "cast jsonb field to %d is not supported.", targetOid);
is a little bit messy. This case should not be possible, because it is filtered by jsonb_cast_is_optimized. So the message should be changed or it needs a comment.
Regards
Pavel
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
