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>
User-agent: mu4e 1.10.7; emacs 29.1
From: Andy Fan <zhihuifan1213@163.com>
To: Andy Fan <zhihuifan1213@163.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
Date: Sun, 10 Mar 2024 07:16:40 +0800
In-reply-to: <8734t6c5rh.fsf@163.com>
Message-ID: <87o7bn7z56.fsf@163.com>
MIME-Version: 1.0
Content-Type: text/plain
Archived-At: <https://www.postgresql.org/message-id/87o7bn7z56.fsf%40163.com>
Precedence: bulk


>> But I have a different question about this patch set.  This has some
>> overlap with the JSON_VALUE function that is being discussed at
>> [0][1]. For example, if I apply the patch
>> v39-0001-Add-SQL-JSON-query-functions.patch from that thread, I can run
>>
>> select count(*) from tb where json_value(a, '$.a' returning numeric) = 2;
>>
>> and I get a noticeable performance boost over
>>
>> select count(*) from tb where cast (a->'a' as numeric) = 2;
>
> Here is my test and profile about the above 2 queries.
>
..
> As we can see the patch here has the best performance (this result looks
> be different from yours?).
>
> After I check the code, I am sure both patches *don't* have the problem
> in master where it get a jsonbvalue first and convert it to jsonb and
> then cast to numeric.
>
> Then I perf the result, and find the below stuff:
>
..

> JSONB_VALUE has a much longer way to get getKeyJsonValueFromContainer,
> then I think JSON_VALUE probably is designed for some more complex path 
> which need to pay extra effort which bring the above performance
> difference. 


Hello Peter,

Thanks for highlight the JSON_VALUE patch! Here is the sistuation in my
mind now. 

My patch is desigined to *not* introducing any new user-faced functions, 
but let some existing functions run faster. JSON_VALUE patch is designed
to following more on SQL standard so introuduced one new function which
has more flexibility on ERROR handling [1].  

Both patches are helpful on the subject here, but my patch here has a
better performance per my testing, I don't think I did anything better
here, just because JSON_VALUE function is designed for some more generic
purpose which has to pay some extra effort, and even if we have some
chance to improve JSON_VALUE, I don't think it shoud not block the patch
here (I'd like to learn more about this, it may takes some time!)

So I think the my patch here can be go ahead again, what do you think? 

[1]
https://www.postgresql.org/message-id/CACJufxGtetrn34Hwnb9D2if5D_HOPAh235MtEZ1meVYx-BiNtg%40mail.gmail.com 

-- 
Best Regards
Andy Fan