From: Andy Fan <zhihuifan1213@163.com>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Peter Eisentraut <peter@eisentraut.org>,  Amit Langote
 <amitlangote09@gmail.com>,  Alvaro Herrera <alvherre@alvh.no-ip.org>,
  jian he <jian.universality@gmail.com>,  Chapman Flack
 <chap@anastigmatix.net>,  pgsql-hackers@lists.postgresql.org
Subject: Re: Extract numeric filed in JSONB more effectively
In-Reply-To: <CAApHDvqnUONCN54dAXdH678ELK7aH2gyVhh0GjTRtrexPd9YMw@mail.gmail.com>
	(David Rowley's message of "Thu, 12 Sep 2024 09:00:41 +1200")
References: <CAKU4AWoqAVya6PBhn+BCbFaBMt3z-2=i5fKO3bW=6HPhbid2Dw@mail.gmail.com>
	<CAKU4AWo4es1awVNbjLZkEpYCkgjxhkszbHiMF0UTsjx62K5RQg@mail.gmail.com>
	<169880504467.94392.3769687331705514588.pgcf@coridan.postgresql.org>
	<87a5rry0bz.fsf@163.com>
	<CACJufxHRMDDehg9Py8+3Yh9JqTjZ=MF6pbxqvX_KANLpC+X1cA@mail.gmail.com>
	<87h6jpob9x.fsf@163.com>
	<CACJufxH-6DkAGr0GRFSS7BAwKG7uVFceXtxHLM7Ub7oGagQGKg@mail.gmail.com>
	<87r0hmvuvr.fsf@163.com>
	<8102ff5b-b156-409e-a48f-e53e63a39b36@eisentraut.org>
	<8734t6c5rh.fsf@163.com> <87o7bn7z56.fsf@163.com>
	<875xx197bp.fsf@163.com> <87ttk0lgcx.fsf@163.com>
	<CAApHDvqnUONCN54dAXdH678ELK7aH2gyVhh0GjTRtrexPd9YMw@mail.gmail.com>
Date: Thu, 12 Sep 2024 03:03:18 +0000
Message-ID: <8734m5fua1.fsf@163.com>
MIME-Version: 1.0
Content-Type: text/plain
Archived-At: <https://www.postgresql.org/message-id/8734m5fua1.fsf%40163.com>
Precedence: bulk


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