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 1qVolL-001IIc-SB for pgsql-hackers@arkaria.postgresql.org; Tue, 15 Aug 2023 07:46:48 +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 1qVolI-00C159-Rs for pgsql-hackers@arkaria.postgresql.org; Tue, 15 Aug 2023 07:46:45 +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 1qVolI-00C150-Dk for pgsql-hackers@lists.postgresql.org; Tue, 15 Aug 2023 07:46:44 +0000 Received: from mail-yw1-x1131.google.com ([2607:f8b0:4864:20::1131]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qVolB-000DxY-KB for pgsql-hackers@postgresql.org; Tue, 15 Aug 2023 07:46:43 +0000 Received: by mail-yw1-x1131.google.com with SMTP id 00721157ae682-583b3aa4f41so53703837b3.2 for ; Tue, 15 Aug 2023 00:46:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1692085597; x=1692690397; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=lxXsVQGco8ZQgiqS9eOCXQ8SncVHzUga8uMFozoEYm4=; b=pB+1nwxDvawOSF1m6jPy2PmBWLTS+gzV3//GCA8UAdaoPDid6lAxzfi/b+xMutysX9 ggZCpS1q8naw0KAJjPbYWuS3Airr4vSD1m2p0blznL7gJfxX3Rwn8G9h2T8KfKbXFRrU 9uR0sR4XAvvRgcL7iMd8ihDaGde0gwRuqZCtQC+M5BoczN9EFD+vjxSLqQfcX4EHv0h2 I3qe078vpVQi3vEqt7avh6ZIfy32wwpr0W5yXBYXtTMnapmLdcwfVCx+olPznMlKnXjT wkJpyULEuu6F55dgXKWacUqgb8OM7mVgqvQsW+NZIEYApPF29FWHRDzdNjPQ1Bmb/vLw DENA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1692085597; x=1692690397; 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=lxXsVQGco8ZQgiqS9eOCXQ8SncVHzUga8uMFozoEYm4=; b=Ykz5D8E7ygM1NCRbbrPjhO/aYRTfcAZppYBs/u04V9Egr5j3GFmfdg2Pqiicj1j9e8 HAM/bTwaB639CW5OFjOCKU3MVovHbmwWx5jA9i310R/DN/g9omGrW3cpD2Ah6627WLNe IgVt8yiadrGKx6lv8PNjePVFy9w4Hxremoh7f+NZ5B5B57omXUPB/xGrnVUSw3TwFOm1 B+UhUaJW+SuMivSha4tSMHUDL2W2F3siCSYELlrsoQQi6YFoG8cfbMbBrgXMg8CXnHJ6 B7f331STcgDF++qxxN3RKZG2WTjPVS/5kRB3HNJvZNVrIIWHzQsWVJCu94d7udogi3lS mryA== X-Gm-Message-State: AOJu0Yw2Ys3h6c2sRJEMOVVASWLfPL0OO8C4zTDPLRIgznGl/BecT5t8 j+pjd26Qo3tcnGaASsMlxeP6sRMIOdSs6tHS7+I= X-Google-Smtp-Source: AGHT+IF/DaCeVx6cm5CZzrUcXvJlj1wzBQr3yjMPnTgw051dlWfV3sXdFi8SOjEsrdOg9u6RvMtG9JV3SMQlRitpAEI= X-Received: by 2002:a81:8804:0:b0:586:a216:a348 with SMTP id y4-20020a818804000000b00586a216a348mr14139553ywf.18.1692085596819; Tue, 15 Aug 2023 00:46:36 -0700 (PDT) MIME-Version: 1.0 References: <3507485.1691090027@sss.pgh.pa.us> <900892914fdc3f477b101d699efb40e0@anastigmatix.net> <903341.1692022214@sss.pgh.pa.us> In-Reply-To: From: Pavel Stehule Date: Tue, 15 Aug 2023 09:45:59 +0200 Message-ID: Subject: Re: Extract numeric filed in JSONB more effectively To: Andy Fan Cc: Tom Lane , Chapman Flack , jian he , pgsql-hackers Content-Type: multipart/alternative; boundary="000000000000ed95b60602f16064" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ed95b60602f16064 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =C3=BAt 15. 8. 2023 v 9:05 odes=C3=ADlatel Andy Fan napsal: > > >> a) effectiveness. The ending performance should be similar like your >> current patch, but without necessity to use planner support API. >> > > So the cost is we need to create a new & different framework. > yes, it can be less work, code than for example introduction of "anycompatible". > >> > b) because you can write only var :=3D j->'f', and plpgsql forces cast >> function execution, but not via planner. >> > > var a :=3D 1 needs going with planner, IIUC, same with j->'f'. > i was wrong, the planner is full, but the executor is reduced. > > c) nothing else. It should not to require to modify cast function >> definitions >> > > If you look at how the planner support function works, that is > pretty simple, just modify the prosupport attribute. I'm not sure > this should be called an issue or avoiding it can be described > as a benefit. > > I don't think the current case is as bad as the other ones like > users needing to modify their queries or type-safety system > being broken. So personally I'm not willing to creating some > thing new & heavy. However I'm open to see what others say. > ok regards Pavel > > -- > Best Regards > Andy Fan > --000000000000ed95b60602f16064 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
=C3=BAt 15. 8. 2023 v=C2=A09:05 odes= =C3=ADlatel Andy Fan <zhihui= .fan1213@gmail.com> napsal:


a) effectiveness. T= he ending performance should be similar like your current patch, but withou= t necessity to use planner support API.
= =C2=A0
So the cost is we need to create a new & different fra= mework.=C2=A0=C2=A0

yes, = it can be less work, code than for example introduction of "anycompati= ble".=C2=A0
=C2=A0
=C2=A0
b) because you can write only var :=3D j->'f', and p= lpgsql forces cast function execution, but not via planner.
=C2=A0
var a :=3D 1 needs going with planner,= =C2=A0 IIUC,=C2=A0 same with j->'f'.=C2=A0=C2=A0

i was wrong, the planner is full, but th= e executor is reduced.

=C2=A0

c) nothing else. = It should not to require to modify cast function definitions
=C2=A0
If you look at how th= e planner support function works,=C2=A0 that is
pretty simple,=C2= =A0 just modify the prosupport attribute. I'm not sure
this s= hould be called an issue or avoiding it can be described
as a ben= efit.=C2=A0

I don't think the current case is = as bad as the other ones like
users needing to modify their queri= es or type-safety system
being broken. So personally I'm not = willing to creating some
thing new & heavy. However I'm o= pen to see what others say.=C2=A0=C2=A0
=
ok

regards

Pavel
=C2=A0
=C2=A0
= --
Best Regards
Andy Fan
=
--000000000000ed95b60602f16064--