From: | Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> |
---|---|
To: | Chapman Flack <chap(at)anastigmatix(dot)net> |
Cc: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, 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-03 13:50:15 |
Message-ID: | CAKU4AWrMR8AS2XoedYB=X0dwmrHv23z2STtByazDVVJLkDt9UA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi:
On Thu, Aug 3, 2023 at 8:34 PM Chapman Flack <chap(at)anastigmatix(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2023-08-03 13:52:40 | Re: Extract numeric filed in JSONB more effectively |
Previous Message | Nazir Bilal Yavuz | 2023-08-03 13:38:41 | Re: Show WAL write and fsync stats in pg_stat_io |