Re: Extract numeric filed in JSONB more effectively

Hi:

>
> 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(at)->'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;

--
Best Regards
Andy Fan