Yes, it's not great, but that's just how this works. We can't pre-specialize all possible operations that one might want to do in PostgreSQL - that'd be absurdly expensive for binary and initial database sizes.
Are any people saying we would pre-specialize all possible operators?
I would say anything if adding operators will be expensive for binary and
initial database sizes. If so, how many per operator and how many
operators would be in your expectation?
> Secondly, because of the same reason above, we use PG_GETARG_JSONB_P(0), > which may detoast a value so we need to free it with PG_FREE_IF_COPY. > then this looks like another potential wastage.
Is it? Detoasting only happens if the argument was toasted, and I have serious doubts that the result of (a->'a') will be toasted in our current system. Sure, we do need to allocate an intermediate result, but that's in a temporary memory context that should be trivially cheap to free.
If you take care about my context, I put this as a second factor for the
current strategy. and it is the side effects of factor 1. FWIW, that cost
is paid for every jsonb object, not something during the initial database.
> As a comparison, cast to other data types like > int2/int4 may be not needed since they are not binary compatible.
Yet there are casts from jsonb to and back from int2, int4 and int8. I don't see a very good reason to add this, for the same reasons mentioned by Pavel.
Who is insisting on adding such an operator in your opinion?
*If* we were to add this operator, I would want this patch to also include a #-variant for text[]-based deep access (c.q. #> / #>>), and equivalent operators for the json type to keep the current access operator parity.
> Here is the performance comparison (with -O3, my previous post is -O0). > > select 1 from tb where (a->'a')::numeric = 2; 31ms. > select 1 from tb where (a@->'a') = 2; 15ms
What's tb here?
This is my first post. Copy it here again.
create table tb (a jsonb); insert into tb select '{"a": 1}'::jsonb from generate_series(1, 100000)i;