public inbox for [email protected]
help / color / mirror / Atom feedFrom: Andy Fan <[email protected]>
To: David Rowley <[email protected]>
Cc: Peter Eisentraut <[email protected]>
Cc: Amit Langote <[email protected]>
Cc: Alvaro Herrera <[email protected]>
Cc: jian he <[email protected]>
Cc: Chapman Flack <[email protected]>
Cc: [email protected]
Subject: Re: Extract numeric filed in JSONB more effectively
Date: Thu, 12 Sep 2024 03:03:18 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAApHDvqnUONCN54dAXdH678ELK7aH2gyVhh0GjTRtrexPd9YMw@mail.gmail.com>
References: <CAKU4AWoqAVya6PBhn+BCbFaBMt3z-2=i5fKO3bW=6HPhbid2Dw@mail.gmail.com>
<CAKU4AWo4es1awVNbjLZkEpYCkgjxhkszbHiMF0UTsjx62K5RQg@mail.gmail.com>
<169880504467.94392.3769687331705514588.pgcf@coridan.postgresql.org>
<[email protected]>
<CACJufxHRMDDehg9Py8+3Yh9JqTjZ=MF6pbxqvX_KANLpC+X1cA@mail.gmail.com>
<[email protected]>
<CACJufxH-6DkAGr0GRFSS7BAwKG7uVFceXtxHLM7Ub7oGagQGKg@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
<CAApHDvqnUONCN54dAXdH678ELK7aH2gyVhh0GjTRtrexPd9YMw@mail.gmail.com>
Hello David,
Thanks for checking this!
> There's a lot of complexity in the v18 patch that I don't understand
> the need for.
>
> I imagined you'd the patch should create a SupportRequestSimplify
> support function for jsonb_numeric() that checks if the input
> expression is an OpExpr with funcid of jsonb_object_field(). All you
> do then is ditch the cast and change the OpExpr to call a new function
> named jsonb_object_field_numeric() which returns the val.numeric
> directly. Likely the same support function could handle jsonb casts
> to other types too, in which case you'd just call some other function,
> e.g jsonb_object_field_timestamp() or jsonb_object_field_boolean().
Basically yes. The reason complexity comes when we many operators we
want to optimize AND my patch I want to reduce the number of function
created.
The optimized functions and operators includes:
1. jsonb_object_field / ->
2. jsonb_array_element / ->
3. jsonb_extract_path / #>
4. jsonb_path_query
5. jsonb_path_query_first
> ..., in which case you'd just call some other function,
> e.g jsonb_object_field_timestamp() or jsonb_object_field_boolean().
This works, but We need to create 2 functions for each operator. In the
patched case, we have 5 operators, so we need to create 10 functions.
op[1,2,3,4,5]_bool
op[1,2,3,4,5]_numeric.
Within the start / finish function, we need to create *7* functions.
op[1,2,3,4,5]_start : return the "JsonbVaue".
jsonb_finish_numeric: convert jsonbvalue to numeric
jsonb_finish_bool : convert jsonbvalue to bool.
I think the above is the major factor for the additional complexity.
Some other factors contribute to complexity a bit.
1. we also have jsonb_int{2,4,8}/float{4,8} in pg_proc for '->'
operator, not only numeric.
2. user may use OpExpr, like (jb->'x')::numeric, user may also use
FuncExpr, like (jsonb_object_field(a, 'x'))::numeric.
--
Best Regards
Andy Fan
view thread (28+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Extract numeric filed in JSONB more effectively
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox