Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qStbS-007hT1-Kk for pgsql-hackers@arkaria.postgresql.org; Mon, 07 Aug 2023 06:20:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qStbQ-007Fsm-Ni for pgsql-hackers@arkaria.postgresql.org; Mon, 07 Aug 2023 06:20:29 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qStbQ-007Fp2-1Y for pgsql-hackers@lists.postgresql.org; Mon, 07 Aug 2023 06:20:28 +0000 Received: from mail-yw1-x112e.google.com ([2607:f8b0:4864:20::112e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qStbN-000yIU-N5 for pgsql-hackers@postgresql.org; Mon, 07 Aug 2023 06:20:27 +0000 Received: by mail-yw1-x112e.google.com with SMTP id 00721157ae682-584388ec441so42331897b3.3 for ; Sun, 06 Aug 2023 23:20:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1691389225; x=1691994025; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=NTKCSXxIxTz4KRN8WURMbbCiym4rn/dSrKweRswszLI=; b=mcPRNCm3HWlFWOOT9nYUCZc1tNQzWtgrnxfv6U+Evq8rmRn8KUtOxjHlRh439hLvDo cJ3lq1Jwfhfl4y96854NnHFHzODprgNorrJCCZVBpw2krDY8TqZEQ95UIRyE0dLg3YKt Cr5c1xywEjU4q2r86TkoeppOVpjdccv0cLhv4jjRoSqSVtT6z2SWBwUP5XX/zl2ESCCz WPI7txP0M/auyqYDNnegBVdUO/tZN4fXOSBzyb/h++Jkl6IpS+41x53EHC0U2Y3mdVFq Nmht36h5afpphvkDhDvDktJzUaeUtfhYG7D8kb81y2poZBj07sdmv1yS9GF+VXDdoNbh eNpA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1691389225; x=1691994025; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=NTKCSXxIxTz4KRN8WURMbbCiym4rn/dSrKweRswszLI=; b=DNuwh+RiAOC4GBFtNCduF1qbCWqD89g3ODM5FcR0lSP7waVgTPGYnq6wBDylSx7nBa sV3w52Ego3DBD2g8DlAaTO8ZoznUHoaU42Q9OhMklmInTv/ZhwQAC+cZctYrjvemL3hX mfSj2K7Ry/mLePUTZKFhZj6cGCOUmkwPJDDhYnN11S2FjY4ucGjtWLkr6W+EsQ7t2WvR RVMaYi+5mLRh9Zb7DrIUkrQj45V6151sY7ABpzQQ8wLkPm1FD9dLyAwQOyYvsF/JDtkS 1SRmUKv6FjUvrXCjArlkZS2aM8KzMWNt5dIF6n5/1LI6RqTIyCvehWb1F+L5IuuX5+ku W0PA== X-Gm-Message-State: AOJu0YzaVFX2v7o/PU18JgyDd4mLzm4Hrjl0yc1RyRQubyM0xKfUkrZE ftslgnbTcoDyJvghWjpabE2kc1+XR9pxSyTDvZQ= X-Google-Smtp-Source: AGHT+IE3DAxaYH2SW5jqfrV1xOhRvQgqeedsv1s/Z/d0lzvOtiXfdnoSMGDlvuRQecUlTExdccdspV1my6thJ2s0bIw= X-Received: by 2002:a81:8501:0:b0:57a:8de9:29e7 with SMTP id v1-20020a818501000000b0057a8de929e7mr7852371ywf.28.1691389224950; Sun, 06 Aug 2023 23:20:24 -0700 (PDT) MIME-Version: 1.0 References: <3507485.1691090027@sss.pgh.pa.us> In-Reply-To: From: Pavel Stehule Date: Mon, 7 Aug 2023 08:19:48 +0200 Message-ID: Subject: Re: Extract numeric filed in JSONB more effectively To: Andy Fan Cc: Tom Lane , jian he , pgsql-hackers Content-Type: multipart/alternative; boundary="000000000000ee214e06024f3d9f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ee214e06024f3d9f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi po 7. 8. 2023 v 5:04 odes=C3=ADlatel Andy Fan na= psal: > Hi: > > >> For all the people who are interested in this topic, I will post a >> planner support function soon, you can check that then. >> >> > The updated patch doesn't need users to change their codes and can get > 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. > I am looking on your patch, and the message + + default: + elog(ERROR, "cast jsonb field to %d is not supported.", targetOid); is a little bit messy. This case should not be possible, because it is filtered by jsonb_cast_is_optimized. So the message should be changed or it needs a comment. Regards Pavel > > 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 =3D 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 =3D 1; > Time: 30.138 ms > > select 1 from tb where (a->'b')::int4 =3D 1; > Time: 32.384 ms > > select 1 from tb where (a->'b')::int8 =3D 1;\ > Time: 29.922 ms > > select 1 from tb where (a->'b')::float4 =3D 1; > Time: 54.139 ms > > select 1 from tb where (a->'b')::float8 =3D 1; > Time: 66.933 ms > > Patched: > > select 1 from tb where (a->'b')::numeric =3D 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 =3D 1; > Time: 16.847 ms > > select 1 from tb where (a->'b')::int4 =3D 1; > Time: 17.105 ms > > select 1 from tb where (a->'b')::int8 =3D 1; > Time: 16.720 ms > > select 1 from tb where (a->'b')::float4 =3D 1; > Time: 33.409 ms > > select 1 from tb where (a->'b')::float8 =3D 1; > Time: 34.660 ms > > -- > Best Regards > Andy Fan > --000000000000ee214e06024f3d9f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

po 7. 8. 2023 v=C2=A05:04 odes=C3=ADlatel And= y Fan <zhihui.fan1213@gmail.= com> napsal:
Hi:
=C2=A0
For all the people who are intereste= d in this topic, I will post a=C2=A0
planner support function soo= n,=C2=A0 you can check that then.


The updated patch doesn't need users to change their= codes and can get
better performance. Thanks for all the feedback which= makes things better.

To verify there is no unexpected stuff happeni= ng, here is the performance
comparison between master and patched.

I am looking on your patch,= and the message

+
+ default:
+ elog(ERRO= R, "cast jsonb field to %d is not supported.", targetOid);
<= div>
is a little bit messy. This case should not be possible,= because it is filtered by=20 jsonb_cast_is_optimized. So the message should be changed or it needs a=20 comment.

Regards

Pavel
=C2=A0

create table tb(a json= b);
insert into tb select '{"a": true, "b": 23.3= 333}' from generate_series(1,
100000)i;

Master:
select 1 f= rom tb where =C2=A0(a->'b')::numeric =3D 1;
Time: 31.020 ms
select 1 from tb where not (a->'a')::boolean;
Time: 25.= 888 ms

select 1 from tb where =C2=A0(a->'b')::int2 =3D 1;=
Time: 30.138 ms

select 1 from tb where =C2=A0(a->'b')= ::int4 =3D 1;
Time: 32.384 ms

select 1 from tb where =C2=A0(a->= ;'b')::int8 =3D 1;\
Time: 29.922 ms

select 1 from tb wher= e =C2=A0(a->'b')::float4 =3D 1;
Time: 54.139 ms

select= 1 from tb where =C2=A0(a->'b')::float8 =3D 1;
Time: 66.933 m= s

Patched:

select 1 from tb where =C2=A0(a->'b')::= numeric =3D 1;
Time: 15.203 ms

select 1 from tb where not (a->= 'a')::boolean;
Time: 12.894 ms

select 1 from tb where =C2= =A0(a->'b')::int2 =3D 1;
Time: 16.847 ms

select 1 from= tb where =C2=A0(a->'b')::int4 =3D 1;
Time: 17.105 ms

= select 1 from tb where =C2=A0(a->'b')::int8 =3D 1;
Time: 16.7= 20 ms

select 1 from tb where =C2=A0(a->'b')::float4 =3D 1= ;
Time: 33.409 ms

select 1 from tb where =C2=A0(a->'b'= )::float8 =3D 1;
Time: 34.660 ms

--
Best Regards
Andy Fan
--000000000000ee214e06024f3d9f--