Extract numeric filed in JSONB more effectively

Andy Fan <zhihui.fan1213@gmail.com>

From: Andy Fan <zhihui.fan1213@gmail.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2023-08-01T04:38:57Z
Lists: pgsql-hackers

Attachments

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

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →
  1. Add trailing commas to enum definitions