public inbox for [email protected]  

help / color / mirror / Atom feed

From: Peter Eisentraut <[email protected]>
To: Andy Fan <[email protected]>
To: jian he <[email protected]>
Cc: Chapman Flack <[email protected]>
Cc: [email protected]
Subject: Re: Extract numeric filed in JSONB more effectively
Date: Mon, 4 Mar 2024 13:33:12 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
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]>

On 09.02.24 10:05, Andy Fan wrote:
> 2. Where is the current feature blocked for the past few months?
> 
> It's error message compatible issue! Continue with above setup:
> 
> master:
> 
> select * from tb where (a->'b')::numeric > 3::numeric;
> ERROR:  cannot cast jsonb string to type numeric
> 
> select * from tb where (a->'b')::int4 > 3::numeric;
> ERROR:  cannot cast jsonb string to type integer
> 
> You can see the error message is different (numeric vs integer).
> 
> 
> Patched:
> 
> We still can get the same error message as master BUT the code
> looks odd.
> 
> select * from tb where (a->'b')::int4 > 3;
>                                                      QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------
>   Seq Scan on public.tb
>     Output: a
>     Filter: ((jsonb_finish_numeric(jsonb_object_field_start((tb.a)::internal, 'b'::text), '23'::oid))::integer > 3)
> (3 rows)
> 
> You can see "jsonb_finish_numeric(..,  '23::oid)" the '23::oid' is just
> for the *"integer"* output in error message:
> 
> "cannot cast jsonb string to type*integer*"
> 
> Now the sistuation is either we use the odd argument (23::oid) in
> jsonb_finish_numeric, or we use a incompatible error message with the
> previous version. I'm not sure which way is better, but this is the
> place the current feature is blocked.

I'm not bothered by that.  It also happens on occasion in the backend C 
code that we pass around extra information to be able to construct 
better error messages.  The functions here are not backend C code, but 
they are internal functions, so similar considerations can apply.


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;

So some questions to think about:

1. Compare performance of base case vs. this patch vs. json_value.

2. Can json_value be optimized further?

3. Is this patch still needed?

3a. If yes, should the internal rewriting make use of json_value or 
share code with it?


[0]: 
https://www.postgresql.org/message-id/flat/CA+HiwqE4XTdfb1nW=Ojoy_tQSRhYt-q_kb6i5d4xcKyrLC1Nbg@mail....
[1]: https://commitfest.postgresql.org/47/4377/

view thread (2+ messages)

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]
  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