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 1qVntZ-001GG2-6M for pgsql-hackers@arkaria.postgresql.org; Tue, 15 Aug 2023 06:51:13 +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 1qVntX-00BVib-3f for pgsql-hackers@arkaria.postgresql.org; Tue, 15 Aug 2023 06:51:11 +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 1qVntW-00BViQ-LZ for pgsql-hackers@lists.postgresql.org; Tue, 15 Aug 2023 06:51:10 +0000 Received: from mail-yw1-x1129.google.com ([2607:f8b0:4864:20::1129]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qVntP-000DVL-MC for pgsql-hackers@postgresql.org; Tue, 15 Aug 2023 06:51:09 +0000 Received: by mail-yw1-x1129.google.com with SMTP id 00721157ae682-5899ed05210so53288587b3.3 for ; Mon, 14 Aug 2023 23:51:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1692082263; x=1692687063; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=b4i1HOoFTs7thJzTbN7WGo3vyjvP3jCrbgxXCzVpUPA=; b=Qu7K+GxMfK8ZyZMi9UXlfKx5MMsSrGLYCHz1tYAQm1ATKjc0cYTG0GoE5KmoinsnfR 6cWDb6HeKlJjBCyCbh9uXkz8TE6SKLp/jhiKftCagE3ycOeMsokPYrmw4q4I8fJQJLXU UD8Pzfkxa5qjdszFQATL9bDYmhaQjEEzSC/mbXSMIb/ybnlk4V3MvdbDqqnlCqCW91Gc yFdqFwLr4Y2sBPnYGOgP4t5cYMVdmqKpyEo8SrVw1IJS/9yjWqZUr2OPsjDDzba3xTlR GnZGUe6eui2lXX5r0XodTGbF0m2zSclEc2ZgapAy5CK5LPTY2cxqR5eKt8H8rJjGMQCi iXjw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1692082263; x=1692687063; 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=b4i1HOoFTs7thJzTbN7WGo3vyjvP3jCrbgxXCzVpUPA=; b=ZDDwhWU/i7EHiUEbfgWsd6FlbJnb3zG/s0dAjwzllJfin/WpMJMQFWAehbqETJqJCb G/HdS9zwGTrWDO0hDcrNNT6qkSnllKtOa8tqI0UW4YiXYYmNq5mnOdOUB7J9z5lDmpou As6w67BmhNE7Aj+ZA63gbppemXWzr5WPpHF+cIq5VUCdjMdzxvSSNsThphqkQ20EDOAK 0VNsl10n/xgDOqhQyFIvL2VSx5FJ4/Prp8Ro6ZJ+GCoWrR+R/WDRSEi5iMbwFjxziWE0 KgOAsvHY2yp3FMCVJuEaFlPj+2fC/KWkyt/zY9VsESfG07oGvAhUaeOvRXhpq8ocFjv1 Xo9A== X-Gm-Message-State: AOJu0Yx77UtUwxWLq8HaZucpFpdqGsTEwWyeaqU93LIHUBXp0eOHf7Fc JpdogwigOskh9WvW/m0GKP1LMOl2DD4ziV1UrcQ= X-Google-Smtp-Source: AGHT+IHj+HP6Qe7p1Ruo7sy9Z4v4+N/cAXnHno+4WclipYyqVUN9hHwJt1cZyYwVZcGP4eDw8SfGzsKtb7FpQObPGw0= X-Received: by 2002:a81:a093:0:b0:577:1560:9e17 with SMTP id x141-20020a81a093000000b0057715609e17mr12048050ywg.35.1692082262871; Mon, 14 Aug 2023 23:51:02 -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 08:50:25 +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="0000000000003595220602f09ae7" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000003595220602f09ae7 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =C3=BAt 15. 8. 2023 v 8:04 odes=C3=ADlatel Andy Fan napsal: > >> My idea of an ideal solution is the introduction of the possibility to >> use "any" pseudotype as return type with possibility to set default retu= rn >> type. Now, "any" is allowed only for arguments. The planner can set the >> expected type when it knows it, or can use the default type. >> >> so for extraction of jsonb field we can use FUNCTION >> jsonb_extract_field(jsonb, text) RETURNS "any" DEFAULT jsonb >> > > Is this an existing framework or do you want to create something new? > This should be created > >> if we call SELECT jsonb_extract_field(..., 'x') -> then it returns jsonb= , >> if we use SELECT jsonb_extract_field('...', 'x')::date, then it returns = date >> > > If so, what is the difference from the current jsonb->'f' and > (jsonb->'f' )::date? > a) effectiveness. The ending performance should be similar like your current patch, but without necessity to use planner support API. b) more generic usage. For example, the expressions in plpgsql are executed a little bit differently than SQL queries. So there the optimization from your patch probably should not work, because you can write only var :=3D j->'f', and plpgsql forces cast function execution, but not via planner. c) nothing else. It should not to require to modify cast function definitions >> With this possibility we don't need to touch to cast functions, and we >> can simply implement similar functions for other non atomic types. >> > > What do you mean by "atomic type" here? If you want to introduce some > new framework, I think we need a very clear benefit. > Atomic types (skalar types like int, varchar, date), nonatomic types - array, composite, xml, jsonb, hstore or arrays of composite types. > > -- > Best Regards > Andy Fan > --0000000000003595220602f09ae7 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


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

My idea of an ideal solution is the introduction= of the possibility to use "any" pseudotype as return type with p= ossibility to set default return type. Now, "any" is allowed only= for arguments. The planner can set the expected type when it knows it, or = can use the default type.

so for extraction of jso= nb field we can use FUNCTION jsonb_extract_field(jsonb, text) RETURNS "= ;any" DEFAULT jsonb
=C2=A0
Is this an existin= g framework or do you want to create something new?=C2=A0
=

This should be created
=C2=A0
<= div class=3D"gmail_quote">

if we call= SELECT jsonb_extract_field(..., 'x') -> then it returns jsonb, = if we use SELECT jsonb_extract_field('...', 'x')::date, the= n it returns date

If = so, what is the difference from the current=C2=A0 jsonb->'f'=C2= =A0 =C2=A0and (jsonb->'f' )::date?=C2=A0=C2=A0
=

a) effectiveness. The ending performance s= hould be similar like your current patch, but without necessity to use plan= ner support API.

b) more generic usage. For exampl= e, the expressions in plpgsql are executed a little bit differently than SQ= L queries. So there the optimization from your patch probably should not wo= rk, because you can write only var :=3D j->'f', and plpgsql forc= es cast function execution, but not via planner.

<= div>c) nothing else. It should not to require to modify cast function defin= itions
=C2=A0


With this possibility we do= n't need to touch to cast functions, and we can simply implement simila= r functions for other non atomic types.
=C2=A0
What do you mean by "atomic type"= here?=C2=A0 =C2=A0If you want to introduce some new framework,=C2=A0 I thi= nk we need a very clear benefit.=C2=A0=C2=A0
=

Atomic types (skalar types like int, varchar, date), no= natomic types - array, composite, xml, jsonb, hstore or arrays of composite= types.

=C2=A0

--
Best Regards
Andy Fan
--0000000000003595220602f09ae7--