Re: Extract numeric filed in JSONB more effectively

Поиск
Список
Период
Сортировка
От Pavel Stehule
Тема Re: Extract numeric filed in JSONB more effectively
Дата
Msg-id CAFj8pRD-R-GsGCjeYApbhZoiW8TV6zACaYStMBMM0=--+WgN_A@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

čt 3. 8. 2023 v 2:51 odesílatel Andy Fan <zhihui.fan1213@gmail.com> napsal:
Hi Jian:
 
return PointerGetDatum(v->val.numeric);
should be something like
PG_RETURN_NUMERIC(v->val.numeric);
?

Thanks for this reminder, a new patch is attached.  and commitfest
entry is added as well[1]. For recording purposes,  I compared the
new operator with all the existing operators.

select 1 from tb where (a->'a')::numeric = 2;   30.56ms
select 1 from tb where (a->>'a')::numeric = 2; 29.43ms
select 1 from tb where (a@->'a') = 2;              14.80ms 



I don't like this solution because it is bloating  operators and it is not extra readable. For completeness you should implement cast for date, int, boolean too. Next, the same problem is with XML or hstore type (probably with any types that are containers).

It is strange so only casting is 2x slower. I don't like the idea so using a special operator is 2x faster than common syntax for casting. It is a signal, so there is a space for optimization. Black magic with special operators is not user friendly for relatively common problems.

Maybe we can introduce some *internal operator* "extract to type", and in rewrite stage we can the pattern (x->'field')::type transform to OP(x, 'field', typid)

Regards

Pavel


--
Best Regards
Andy Fan

В списке pgsql-hackers по дате отправления: