public inbox for [email protected]
help / color / mirror / Atom feedFrom: Pavel Stehule <[email protected]>
To: Andy Fan <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: jian he <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: Extract numeric filed in JSONB more effectively
Date: Mon, 7 Aug 2023 08:19:48 +0200
Message-ID: <CAFj8pRAEYbHTPPGgaMMmQK05sumMqBuQ25q8Fx0CGppubD9ZCA@mail.gmail.com> (raw)
In-Reply-To: <CAKU4AWrMKu2EV0YUA-5zHo4oNYWs7wpJ2RhT9RcLUTdYxDu7VA@mail.gmail.com>
References: <CAKU4AWoqAVya6PBhn+BCbFaBMt3z-2=i5fKO3bW=6HPhbid2Dw@mail.gmail.com>
<CACJufxHASOEpngQ8V2tbXgs4VZC3ETrVbS=uk0KC_B_J1j7ejQ@mail.gmail.com>
<CAKU4AWrap1zpYqunJwWTN=CdP7E8e0U4mYmwn7hvTW3ERuENVg@mail.gmail.com>
<CAFj8pRD-R-GsGCjeYApbhZoiW8TV6zACaYStMBMM0=--+WgN_A@mail.gmail.com>
<CAKU4AWpDdFXAD+dMC1HeErXSKBUUBRGWkf=dAcX3wZgBNsWM=g@mail.gmail.com>
<CAFj8pRAO3oEiBaJJ9=HZp6CoP2ffbwSgrKkLKjPfYZwx9wOOuQ@mail.gmail.com>
<CAKU4AWoCHpKAVuQeOrk44cVPy_dVxn1aHrMUvHy5Ag-daFCSsQ@mail.gmail.com>
<CAFj8pRD4cdUmK0RG4oN5B2KRSeDhwfMYaL=XpfEu4iaLeZ_Kow@mail.gmail.com>
<CAKU4AWp8ab61e96v57OaB-Gm1bMfBNVLVy+s17U6_Ne3veB84g@mail.gmail.com>
<[email protected]>
<CAKU4AWoLfjLi+maMxHxjL0OwSPcs58yiSgz-OiRodVpB5CvD1w@mail.gmail.com>
<CAKU4AWrMKu2EV0YUA-5zHo4oNYWs7wpJ2RhT9RcLUTdYxDu7VA@mail.gmail.com>
Hi
po 7. 8. 2023 v 5:04 odesÃlatel Andy Fan <[email protected]> napsal:
> Hi:
>
>
>> For all the people who are interested in this topic, I will post a
>> planner support function soon, you can check that then.
>>
>>
> The updated patch doesn't need users to change their codes and can get
> better performance. Thanks for all the feedback which makes things better.
>
> To verify there is no unexpected stuff happening, here is the performance
> comparison between master and patched.
>
I am looking on your patch, and the message
+
+ default:
+ elog(ERROR, "cast jsonb field to %d is not supported.", targetOid);
is a little bit messy. This case should not be possible, because it is
filtered by jsonb_cast_is_optimized. So the message should be changed or it
needs a comment.
Regards
Pavel
>
> create table tb(a jsonb);
> insert into tb select '{"a": true, "b": 23.3333}' from generate_series(1,
> 100000)i;
>
> Master:
> select 1 from tb where (a->'b')::numeric = 1;
> Time: 31.020 ms
>
> select 1 from tb where not (a->'a')::boolean;
> Time: 25.888 ms
>
> select 1 from tb where (a->'b')::int2 = 1;
> Time: 30.138 ms
>
> select 1 from tb where (a->'b')::int4 = 1;
> Time: 32.384 ms
>
> select 1 from tb where (a->'b')::int8 = 1;\
> Time: 29.922 ms
>
> select 1 from tb where (a->'b')::float4 = 1;
> Time: 54.139 ms
>
> select 1 from tb where (a->'b')::float8 = 1;
> Time: 66.933 ms
>
> Patched:
>
> select 1 from tb where (a->'b')::numeric = 1;
> Time: 15.203 ms
>
> select 1 from tb where not (a->'a')::boolean;
> Time: 12.894 ms
>
> select 1 from tb where (a->'b')::int2 = 1;
> Time: 16.847 ms
>
> select 1 from tb where (a->'b')::int4 = 1;
> Time: 17.105 ms
>
> select 1 from tb where (a->'b')::int8 = 1;
> Time: 16.720 ms
>
> select 1 from tb where (a->'b')::float4 = 1;
> Time: 33.409 ms
>
> select 1 from tb where (a->'b')::float8 = 1;
> Time: 34.660 ms
>
> --
> Best Regards
> Andy Fan
>
view thread (9+ messages) latest in thread
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]
Subject: Re: Extract numeric filed in JSONB more effectively
In-Reply-To: <CAFj8pRAEYbHTPPGgaMMmQK05sumMqBuQ25q8Fx0CGppubD9ZCA@mail.gmail.com>
* 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