Re: Extract numeric filed in JSONB more effectively
От | Andy Fan |
---|---|
Тема | Re: Extract numeric filed in JSONB more effectively |
Дата | |
Msg-id | CAKU4AWrMKu2EV0YUA-5zHo4oNYWs7wpJ2RhT9RcLUTdYxDu7VA@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Extract numeric filed in JSONB more effectively (Andy Fan <zhihui.fan1213@gmail.com>) |
Ответы |
Re: Extract numeric filed in JSONB more effectively
|
Список | pgsql-hackers |
Hi:
For all the people who are interested in this topic, I will post aplanner support function soon, you can check that then.
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.
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
Вложения
В списке pgsql-hackers по дате отправления: