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