Extract numeric filed in JSONB more effectively

From: Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Extract numeric filed in JSONB more effectively
Date: 2023-08-01 04:38:57
Message-ID: CAKU4AWoqAVya6PBhn+BCbFaBMt3z-2=i5fKO3bW=6HPhbid2Dw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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(at)->'a' = 2;
65ms.

Is this the right way to go? Testcase, document and catalog version are
updated.

--
Best Regards
Andy Fan

Attachment Content-Type Size
v1-0001-Add-jsonb-operator-to-return-a-numeric-directly.patch application/octet-stream 5.7 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Kyotaro Horiguchi 2023-08-01 04:51:13 Re: Incorrect handling of OOM in WAL replay leading to data loss
Previous Message Palak Chaturvedi 2023-08-01 04:38:52 Re: Extension Enhancement: Buffer Invalidation in pg_buffercache