Re: Extract numeric filed in JSONB more effectively
| От | Andy Fan |
|---|---|
| Тема | Re: Extract numeric filed in JSONB more effectively |
| Дата | |
| Msg-id | CAKU4AWrMR8AS2XoedYB=X0dwmrHv23z2STtByazDVVJLkDt9UA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Extract numeric filed in JSONB more effectively (Chapman Flack <chap@anastigmatix.net>) |
| Список | pgsql-hackers |
Hi:
On Thu, Aug 3, 2023 at 8:34 PM Chapman Flack <chap@anastigmatix.net> wrote:
On 2023-08-03 03:53, Andy Fan wrote:
> I didn't realize timetime types are binary compatible with SQL,
> so maybe we can have some similar optimization as well.
> (It is a pity that timestamp(tz) are not binary, or else we may
> just need one operator).
Not to veer from the thread, but something about that paragraph
has been hard for me to parse/follow.
I don't think this is a key conflict so far. but I'd explain this in more
detail. If timestamp -> timestamptz or timestamptz -> timestamp is
binary compatible, we can only have 1 operator to return a timestamp.
then when we cast it to timestamptz, it will be a no-op during runtime.
however cast between timestamp and timestamptz is not binary
compatible. whose castmethod is 'f';
>> 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)
>
> Not sure what the OP should be? If it is a function, what is the
> return value? It looks to me like it is hard to do in c language?
Now I am wondering about the 'planner support function' available
in CREATE FUNCTION since PG 12. I've never played with that yet.
Would that make it possible to have some, rather generic, extract
from JSON operator that can look at the surrounding expression
and replace itself sometimes with something efficient?
I didn't realize this before, 'planner support function' looks
amazing and SupportRequestSimplify looks promising, I will check it
more.
Best Regards
Andy Fan
В списке pgsql-hackers по дате отправления: