public inbox for [email protected]
help / color / mirror / Atom feedFrom: Pavel Stehule <[email protected]>
To: Andy Fan <[email protected]>
Cc: jian he <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: Extract numeric filed in JSONB more effectively
Date: Thu, 3 Aug 2023 06:59:19 +0200
Message-ID: <CAFj8pRD-R-GsGCjeYApbhZoiW8TV6zACaYStMBMM0=--+WgN_A@mail.gmail.com> (raw)
In-Reply-To: <CAKU4AWrap1zpYqunJwWTN=CdP7E8e0U4mYmwn7hvTW3ERuENVg@mail.gmail.com>
References: <CAKU4AWoqAVya6PBhn+BCbFaBMt3z-2=i5fKO3bW=6HPhbid2Dw@mail.gmail.com>
<CACJufxHASOEpngQ8V2tbXgs4VZC3ETrVbS=uk0KC_B_J1j7ejQ@mail.gmail.com>
<CAKU4AWrap1zpYqunJwWTN=CdP7E8e0U4mYmwn7hvTW3ERuENVg@mail.gmail.com>
Hi
čt 3. 8. 2023 v 2:51 odesílatel Andy Fan <[email protected]> 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
>
> [1] https://commitfest.postgresql.org/44/4476/
>
>
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
>
view thread (8+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Extract numeric filed in JSONB more effectively
In-Reply-To: <CAFj8pRD-R-GsGCjeYApbhZoiW8TV6zACaYStMBMM0=--+WgN_A@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox