From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, jian he <jian(dot)universality(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Extract numeric filed in JSONB more effectively |
Date: | 2023-08-07 06:19:48 |
Message-ID: | CAFj8pRAEYbHTPPGgaMMmQK05sumMqBuQ25q8Fx0CGppubD9ZCA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi
po 7. 8. 2023 v 5:04 odesílatel Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Smith | 2023-08-07 06:25:28 | Re: [PATCH] Reuse Workers and Replication Slots during Logical Replication |
Previous Message | David Rowley | 2023-08-07 06:18:12 | Re: Fix a comment in paraminfo_get_equal_hashops |