Subject: Re: Extract numeric filed in JSONB more effectively

From: jian he <[email-masked]>
Date: Wed, 2 Aug 2023 14:01:15 +0800

On Tue, Aug 1, 2023 at 12:39 PM Andy Fan <[email-masked]> wrote:
>
> Hi:
>
> Currently if we want to extract a numeric field in jsonb, we need to use
> the following expression:  cast (a->>'a' as numeric). It will turn a numeric
> to text first and then turn the text to numeric again. See
> jsonb_object_field_text and JsonbValueAsText.  However the binary format
> of numeric in JSONB is compatible with the numeric in SQL, so I think we
> can have an operator to extract the numeric directly. If the value of a given
> field is not a numeric data type, an error will be raised, this can be
> documented.
>
> In this patch, I added a new operator for this purpose, here is the
> performance gain because of this.
>
> create table tb (a jsonb);
> insert into tb select '{"a": 1}'::jsonb from generate_series(1, 100000)i;
>
> current method:
> select count(*) from tb where cast (a->>'a' as numeric) = 2;
> 167ms.
>
> new method:
> select count(*) from tb where a@->'a' = 2;
> 65ms.
>
> Is this the right way to go? Testcase, document and catalog version are
> updated.
>
>
> --
> Best Regards
> Andy Fan

return PointerGetDatum(v->val.numeric);
should be something like
PG_RETURN_NUMERIC(v->val.numeric);
?

---

From: Andy Fan <[email-masked]>
Date: Thu, 3 Aug 2023 08:50:47 +0800

> PG_RETURN_NUMERIC(v->val.numeric);

Thanks for this reminder, a new patch is attached.  and commitfest
entry is added as well[1]. For recording purposes,  I compared the
new operator with all the existing operators.

select 1 from tb where (a->'a')::numeric = 2;   30.56ms
select 1 from tb where (a->>'a')::numeric = 2; 29.43ms
select 1 from tb where (a@->'a') = 2;              14.80ms

[1] https://commitfest.postgresql.org/44/4476/

-- 
Best Regards
Andy Fan

---

From: Pavel Stehule <[email-masked]>
Date: Thu, 3 Aug 2023 06:59:19 +0200

Hi

> [1] https://commitfest.postgresql.org/44/4476/
I don't like this solution because it is bloating  operators and it is not
extra readable. For completeness you should implement cast for date, int,
boolean too. Next, the same problem is with XML or hstore type (probably
with any types that are containers).

It is strange so only casting is 2x slower. I don't like the idea so using
a special operator is 2x faster than common syntax for casting. It is a
signal, so there is a space for optimization. Black magic with special
operators is not user friendly for relatively common problems.

Maybe we can introduce some *internal operator* "extract to type", and in
rewrite stage we can the pattern (x->'field')::type transform to OP(x,
'field', typid)

Regards

Pavel

--

---

From: Andy Fan <[email-masked]>
Date: Thu, 3 Aug 2023 15:53:47 +0800

Hi Pavel:

Thanks for the feedback.

> extra readable.

If we support it with cast, could we say we are bloating CAST?  It is true
that it is not extra readable, if so how about  a->>'a'  return text?
Actually
I can't guess any meaning of the existing jsonb operations without
documentation.

For completeness you should implement cast for date, int, boolean too.
> Next, the same problem is with XML or hstore type (probably with any types
> that are containers).
>

I am not sure completeness is a gold rule we should obey anytime,
like we have some function like int24le to avoid the unnecessary
cast, but we just avoid casting for special types for performance
reason, but not for all. At the same time,  `int2/int4/int8` doesn't
have a binary compatibility type in jsonb. and the serialization
/deserialization for boolean is pretty cheap.

I didn't realize timetime types are binary compatible with SQL,
so maybe we can have some similar optimization as well.
(It is a pity that timestamp(tz) are not binary, or else we may
just need one operator).

>
> I don't like the idea so using a special operator is 2x faster than common
> syntax for casting. It is a signal, so there is a space for optimization.
> Black magic with special operators is not user friendly for relatively
> common problems.
>

I don't think "Black magic" is a proper word here, since it is not much
different from ->> return a text.  If you argue text can be cast to
most-of-types,  that would be a reason, but I doubt this difference
should generate a "black magic".

> rewrite stage we can the pattern (x->'field')::type transform to OP(x,

Not sure what the OP should be?  If it is a function, what is the
return value?  It looks to me like it is hard to do in c language?

After all,  if we really care about the number of operators, I'm OK
with just let users use the function directly, like

jsonb_field_as_numeric(jsonb, 'filedname')
jsonb_field_as_timestamp(jsonb, 'filedname');
jsonb_field_as_timestamptz(jsonb, 'filedname');
jsonb_field_as_date(jsonb, 'filedname');

it can save an operator and sloves the readable issue.

-- 
Best Regards
Andy Fan

---

From: Pavel Stehule <[email-masked]>
Date: Thu, 3 Aug 2023 11:47:33 +0200

Hi

> I can't guess any meaning of the existing jsonb operations without

yes, it can bloat CAST, but for usage we have already used syntax, and
these casts are cooked already:

(2023-08-03 11:04:51) postgres=# select castfunc::regprocedure from pg_cast
where castsource = 'jsonb'::regtype;
┌──────────────────┐
│     castfunc     │
╞══════════════════╡
│ -                │
│ bool(jsonb)      │
│ "numeric"(jsonb) │
│ int2(jsonb)      │
│ int4(jsonb)      │
│ int8(jsonb)      │
│ float4(jsonb)    │
│ float8(jsonb)    │
└──────────────────┘
(8 rows)

the operator ->> was a special case, the text type is special in postgres
as the most convertible type. And when you want to visualise a value or
display the value, you should convert value to text.

I can live with that because it is just one, but with your proposal opening
the doors for implementing tens of similar operators, I think it is bad.
Using ::target_type is common syntax and doesn't require reading
documentation.

More, I believe so lot of people uses more common syntax, and then this
syntax should to have good performance - for jsonb - (val->'op')::numeric
works, and then there should not be performance penalty, because this
syntax will be used in 99%.

Usage of cast is self documented.

>> I don't like the idea so using a special operator is 2x faster than
>> common syntax for casting. It is a signal, so there is a space for
>> optimization. Black magic with special operators is not user friendly for
>> relatively common problems.
>>
>

I used the term black magic, because nobody without reading documentation
can find this operator. It is used just for this special case, and the
functionality is the same as using cast (only with different performance).

The operator ->> is more widely used. But if we have some possibility to
work without it, then the usage for a lot of users will be more simple.
More if the target types can be based on context

Can be nice to use some like `EXTRACT(YEAR FROM val->'field')` instead
`EXTRACT(YEAR FROM (val->>'field')::date)`

> return value?  It looks to me like it is hard to do in c language?

It should be internal structure - it can be similar like COALESCE or IS
operator

> it can save an operator and sloves the readable issue.

I don't like it too much, but it is better than introduction new operator

We already have the jsonb_extract_path and jsonb_extract_path_text
function.

I can imagine to usage "anyelement" type too. some like
`jsonb_extract_path_type(jsonb, anyelement, variadic text[] )`

---

From: Chapman Flack <[email-masked]>
Date: Thu, 03 Aug 2023 08:34:44 -0400

> (It is a pity that timestamp(tz) are not binary, or else we may

Not to veer from the thread, but something about that paragraph
has been hard for me to parse/follow.

> return value?  It looks to me like it is hard to do in c language?

Now I am wondering about the 'planner support function' available
in CREATE FUNCTION since PG 12. I've never played with that yet.
Would that make it possible to have some, rather generic, extract
from JSON operator that can look at the surrounding expression
and replace itself sometimes with something more efficient?

Regards,
-Chap

---

From: Andy Fan <[email-masked]>
Date: Thu, 3 Aug 2023 21:22:48 +0800

> syntax will be used in 99%.

This looks like a valid opinion IMO,  but to rescue it, we have to do
something like "internal structure" and remove the existing cast.
But even we pay the effort, it still breaks some common knowledge,
since xx:numeric is not a cast.  It is an "internal structure"!

> can find this operator.

I think this is what document is used for..

> It is used just for this special case, and the functionality is the same
> as using cast (only with different performance).
>

This is not good, but I didn't see a better choice so far,  see my first
graph.

> More if the target types can be based on context

It would be cool but still I didn't see a way to do that without making
something else complex.

>>> Maybe we can introduce some *internal operator* "extract to type", and
>>> in rewrite stage we can the pattern (x->'field')::type transform to OP(x,
>>> 'field', typid)
>>>
>>

It may work, but see my answer in the first graph.

> I don't like it too much, but it is better than introduction new operator

Good to know it.  Naming operators is a complex task  if we add four.

> We already have the jsonb_extract_path and jsonb_extract_path_text

I can't follow this.  jsonb_extract_path returns a jsonb, which is  far
away from
our goal: return a numeric effectively?

> `jsonb_extract_path_type(jsonb, anyelement, variadic text[] )`

Can you elaborate this please?

-- 
Best Regards
Andy Fan

---

From: Andy Fan <[email-masked]>
Date: Thu, 3 Aug 2023 21:50:15 +0800

Hi:

> has been hard for me to parse/follow.

I don't think this is a key conflict so far. but I'd explain this in more
detail. If timestamp -> timestamptz or timestamptz -> timestamp is
binary compatible,  we can only have 1 operator to return a timestamp.
then when we cast it to timestamptz, it will be a no-op during runtime.
however cast between timestamp and timestamptz is not binary
compatible. whose castmethod is 'f';

> >> in
> and replace itself sometimes with something  efficient?
>

I didn't realize this before,  'planner support function' looks
amazing and SupportRequestSimplify looks promising, I will check it
more.

-- 
Best Regards
Andy Fan