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 1tQXxC-003Pvo-Ey for pgsql-hackers@arkaria.postgresql.org; Wed, 25 Dec 2024 20:26:02 +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 1tQXxA-00FlEh-FG for pgsql-hackers@arkaria.postgresql.org; Wed, 25 Dec 2024 20:26:00 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tQXxA-00FlEZ-0u for pgsql-hackers@lists.postgresql.org; Wed, 25 Dec 2024 20:25:59 +0000 Received: from mail-lf1-x135.google.com ([2a00:1450:4864:20::135]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1tQXx7-001bDF-0E for pgsql-hackers@lists.postgresql.org; Wed, 25 Dec 2024 20:25:59 +0000 Received: by mail-lf1-x135.google.com with SMTP id 2adb3069b0e04-53f22fd6832so6740594e87.1 for ; Wed, 25 Dec 2024 12:25:57 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1735158355; x=1735763155; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=TDazAFf76suajaeDDZqsxlISSS1ccH+uYzq8C5o0w9k=; b=g0WUcvrXSk0tNvxXqSHiISbI/sp4APSwRgMeHcbxZr1NLbzr/kG90ZmBeZ1duAhDIc jPGrQEPIJPTFw48N8rPcSqNC5GHtjSsiUwAUsQGtBKKxdsnBdjXyG0GdfNgq1VF6Oq0r Ex1BtwwJAIPBiR8OExvj1pfUN8lHUx6FH9UTPS3l7cYlgZPXjMQi0Ac+ecITYL5UJnoC m0rhvafbMWslEPu5VuJSj331Upx1pGWJqNNh3LFZvTrvs1fS9Cw5G5Fgr0P3rwTXCkSE F9Ok/Rdi2a4wdIAVHwJDjP9c22/0heBwT758XQT6OCWvUKQ2duDbAshTviJMJ1TDxlVs WWTQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1735158355; x=1735763155; 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=TDazAFf76suajaeDDZqsxlISSS1ccH+uYzq8C5o0w9k=; b=hk9ngYpPQ/ZKzt6OI83NkqNvG94hamf7TRRNHckvOGJ2TCXltrsKv5ucEjb8ukBl0P enPwpLn/ijnky1fZqYej0yu56cdxKVQtbPdxbtQSZ308RE6nymODVKN0evhV47cnsyvu OP6zcSX8UC0YxrwKallyk0MzmqogfURdbxDtfDf4V7Uaam0vFeCIxNmwjs6TwdPjxgpz o1DMHgXiS1/bGmcvRsr1A7rSCDBLc3B9/B/n4XAQFk4bxefqNJ7D4gS+tIZ16bnMV+pw Lvsi+j7sjzbxww4ddVXN8Cynhf02slJ88hzqtUiISgazJuPmeDKMgizQ6KechEKrvNFA AAKw== X-Forwarded-Encrypted: i=1; AJvYcCVYqxiul5SlbOgsaIcHsOiMUiBXmXR2/iOtl6sZYdwYB8LWUvCjvxfM+uCQEIUcvY4jfZ2TLfjhxgjMIOUY@lists.postgresql.org X-Gm-Message-State: AOJu0YzlAbQjiVj7g2hkEsG6H0b0GRhElLFq3jzLFZLs9JpUli5v0HoE sH78+arvxcPwSiQKsLNJOhyM9mhjxCNQvsx0UZN8MM5edvkYlLqpXSwVmJmxdavEoZoPq9159Zg qA7ATIptA20ul3d0+yfkzHc/4GjE= X-Gm-Gg: ASbGncurlyf29zsxW8mNLCstw93IOLZTu2ypnb/WLxpbgwZ3FKaQuK7tMdLz5aXb1Le dH3S5F9lPRypsPvgZuIbKFPbUousD6BROzX8HyA== X-Google-Smtp-Source: AGHT+IGiXj2XVpYo26emZB1f5w9SWq6w2/RF2YNoV6X8d5+kiY1/ZLpwJRyRyyROMhWkByVBuzZrAuMU4d81kX/L1iM= X-Received: by 2002:a05:6512:104c:b0:541:1c48:8bf6 with SMTP id 2adb3069b0e04-5422956c531mr6239541e87.53.1735158355081; Wed, 25 Dec 2024 12:25:55 -0800 (PST) MIME-Version: 1.0 References: <1342498.1729444411@sss.pgh.pa.us> <1445998.1729482404@sss.pgh.pa.us> <2062830.1729625620@sss.pgh.pa.us> <2265411.1729699470@sss.pgh.pa.us> <2354718.1729737539@sss.pgh.pa.us> <2581216.1729794746@sss.pgh.pa.us> <1948345.1730500073@sss.pgh.pa.us> <3797606.1732045516@sss.pgh.pa.us> <2811228.1734553329@sss.pgh.pa.us> <1355690.1734971201@sss.pgh.pa.us> In-Reply-To: <1355690.1734971201@sss.pgh.pa.us> From: Michel Pelletier Date: Wed, 25 Dec 2024 12:25:18 -0800 Message-ID: Subject: Re: Using Expanded Objects other than Arrays from plpgsql To: Tom Lane Cc: Pavel Stehule , pgsql-hackers@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000623faf062a1e09f9" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000623faf062a1e09f9 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Dec 23, 2024 at 8:26=E2=80=AFAM Tom Lane wrote: > Michel Pelletier writes: > > ... > > I agree it makes sense to have more use cases before making deeper > > changes. I only work with expanded forms, but need to call wait() to > > pre-expand the object to avoid multiple expansions in functions that ca= n > > take the same object in multiple parameters. > > Hmm. I agree that the wait() call is a bit ugly, but there are at > least two things that seem worth looking into before we go so far > as inventing type-support infrastructure: > 2. If the problem is primarily with passing the same object to > multiple parameters of a function, couldn't you detect and optimize > that within the function? It would be messier than just blindly > applying DatumGetWhatever() to each parameter position; but with a > bit of thought I bet you could create some support logic that would > hide most of the mess. > Ah that's a great idea, and it works beautifully! Now I can do an efficient triangle count without even needing a function, see below expand_matrix is only called once: postgres=3D# select reduce_scalar(mxm(graph, graph, mask=3D>graph, c=3D>gra= ph)) / 6 as tcount from vlivejournals ; DEBUG: matrix_mxm DEBUG: DatumGetMatrix DEBUG: expand_matrix -- only called once! DEBUG: new_matrix DEBUG: DatumGetMatrixMaybeA DEBUG: DatumGetMatrixMaybeAB DEBUG: DatumGetMatrixMaybeABC DEBUG: matrix_reduce_scalar DEBUG: DatumGetMatrix DEBUG: new_scalar DEBUG: scalar_div_int32 DEBUG: new_scalar DEBUG: scalar_out =E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=90 =E2=94=82 tcount =E2=94=82 =E2=94=9C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=A4 =E2=94=82 int32:177820130 =E2=94=82 =E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=98 What a wonderful Christmas present to me, thank you Tom! That pretty much resolves my main issues. I'm still in an exploratory phase but I think this gets me pretty far. Is this something that has to wait for 18 to be released? Also do you need any further testing or code reviewing from me? -Michel --000000000000623faf062a1e09f9 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Mon, Dec 23, 2024 at 8:26=E2=80=AFAM T= om Lane <tgl@sss.pgh.pa.us> = wrote:
Michel Pelletier <pelletier.michel@gmail.com> writes:
> ...
> I agree it makes sense to have more use cases before making deeper
> changes.=C2=A0 I only work with expanded forms,=C2=A0 but need to call= wait() to
> pre-expand the object to avoid multiple expansions in functions that c= an
> take the same object in multiple parameters.

Hmm.=C2=A0 I agree that the wait() call is a bit ugly, but there are at
least two things that seem worth looking into before we go so far
as inventing type-support infrastructure:

2. If the problem is primarily with passing the same object to
multiple parameters of a function, couldn't you detect and optimize
that within the function?=C2=A0 It would be messier than just blindly
applying DatumGetWhatever() to each parameter position; but with a
bit of thought I bet you could create some support logic that would
hide most of the mess.

Ah that's a = great idea, and it works beautifully!=C2=A0 Now I can do an efficient trian= gle count without even needing a function, see below expand_matrix is only = called once:

postgres=3D#= select reduce_scalar(mxm(graph, graph, mask=3D>graph, c=3D>graph)) /= 6 as tcount from vlivejournals ;
DEBUG: =C2=A0matrix_mxm
DEBUG: =C2= =A0DatumGetMatrix
DEBUG: =C2=A0expand_matrix=C2=A0 =C2=A0-- only called = once!
DEBUG: =C2=A0new_matrix
DEBUG: =C2=A0DatumGetMatrixMaybeA
DE= BUG: =C2=A0DatumGetMatrixMaybeAB
DEBUG: =C2=A0DatumGetMatrixMaybeABC
= DEBUG: =C2=A0matrix_reduce_scalar
DEBUG: =C2=A0DatumGetMatrix
DEBUG: = =C2=A0new_scalar
DEBUG: =C2=A0scalar_div_int32
DEBUG: =C2=A0new_scala= r
DEBUG: =C2=A0scalar_out
=E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=90
=E2=94=82=C2=A0 = =C2=A0 =C2=A0tcount=C2=A0 =C2=A0 =C2=A0 =E2=94=82
=E2=94=9C=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=A4=
=E2=94=82 int32:177820130 =E2=94=82
=E2=94=94=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=98

What a wonderful Christmas present to me, thank= you Tom!

That pretty much resolves my main issues= .=C2=A0 I'm still in an exploratory phase but I think this gets me pret= ty far.=C2=A0 Is this something that has to wait for 18 to be released?=C2= =A0 Also do you need any further testing or code reviewing from me?=C2=A0

-Michel
--000000000000623faf062a1e09f9--