Re: How to run in parallel in Postgres, EXECUTE_PARALLEL
Lars Aksel Opsahl <[email protected]> Sun, 8 Dec 2019 18:14:14 +0000
| Newsgroups | gmane.comp.db.postgresql.performance |
|---|---|
| Message-ID | <HE1P189MB0266588071668BF3D5CF85479D590@HE1P189MB0266.EURP189.PROD.OUTLOOK.COM> |
--_000_HE1P189MB0266588071668BF3D5CF85479D590HE1P189MB0266EURP_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
>You cannot run several queries in parallel in a PostgreSQL function.
>
>You may want to have a look at PL/Proxy which might be used for things lik=
e that.
>
>Yours,
>Laurenz Albe
>--
>Cybertec | https://www.cybertec-postgresql.com
Hi Laurenz
The code below takes 3, seconds
DO
$body$
DECLARE
BEGIN
EXECUTE 'SELECT pg_sleep(1); SELECT pg_sleep(1); SELECT pg_sleep(1);';
END
$body$;
Do you or anybody know if there are any plans for a function call that supp=
ort the calling structure below or something like it and that then could fi=
nish in 1 second ? (If you are calling a void function, the return value sh=
ould not be any problem.)
DO
$body$
DECLARE
command_string_list text[3];
BEGIN
command_string_list[0] =3D 'SELECT pg_sleep(1)';
command_string_list[1] =3D 'SELECT pg_sleep(1)';
command_string_list[2] =3D 'SELECT pg_sleep(1)';
EXECUTE_PARALLEL command_string_list;
END
$body$;
The only way to this today as I understand it, is to open 3 new connections=
back to the database which you can be done in different ways.
If we had a parallel functions like the one above it's easier to make paral=
lel sql without using complex scripts, java, python or other system.
Thanks.
Lars
--_000_HE1P189MB0266588071668BF3D5CF85479D590HE1P189MB0266EURP_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Diso-8859-=
1">
<style type=3D"text/css" style=3D"display:none;"> P {margin-top:0;margin-bo=
ttom:0;} </style>
</head>
<body dir=3D"ltr">
<div id=3D"appendonsend" style=3D"font-family: Calibri, Arial, Helvetica, s=
ans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
</div>
<div style=3D"font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12p=
t; color:rgb(0,0,0)">
<span style=3D"background: var(--white);">>You cannot run several querie=
s in parallel in a PostgreSQL function.</span><br>
</div>
<div class=3D"BodyFragment">
<div class=3D"PlainText" style=3D"font-size: 11pt;">><br>
>You may want to have a look at PL/Proxy which might be used for things =
like that.<br>
><br>
>Yours,<br>
>Laurenz Albe<br>
>-- <br>
>Cybertec | <a href=3D"https://www.cybertec-postgresql.com">https://www.=
cybertec-postgresql.com</a><br>
<br>
</div>
<div class=3D"PlainText" style=3D"font-size: 11pt;">Hi Laurenz</div>
<div class=3D"PlainText" style=3D"font-size: 11pt;"><br>
</div>
<div class=3D"PlainText" style=3D"font-size: 11pt;">The code below takes 3,=
seconds </div>
<div class=3D"PlainText" style=3D"font-size: 11pt;"><span style=3D"font-fam=
ily: Menlo; font-size: 12px; font-style: normal; font-variant-caps: normal;=
background: var(--white);">DO</span><br>
</div>
<div class=3D"PlainText" style=3D"font-size: 11pt;">
<p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">$body$</p>
<p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo; color: #7f=
0055"><b>DECLARE</b><span style=3D"color: #000000"><span> </span></spa=
n></p>
<p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo; color: #7f=
0055"><b style=3D"font-size: 12px;">BEGIN</b><br>
</p>
<p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo; color: #00=
00ff"><span style=3D"color: #000000"><span></span>EXECUTE
</span>'SELECT pg_sleep(1); SELECT pg_sleep(1); SELECT pg_sleep(1);'<span s=
tyle=3D"color: #000000">;</span></p>
<p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo; color: #7f=
0055"><b>END</b></p>
<p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">$body$;</p=
>
<br>
</div>
<div class=3D"PlainText" style=3D"font-size: 11pt;"><span style=3D"backgrou=
nd: var(--white);">Do you or anybody know if there are any plans for a func=
tion call that support the calling structure below or something like it and=
that then could finish in 1 second ?
(<span style=3D"caret-color: rgb(50, 49, 48); font-family: "Segoe UI&=
quot;, "Segoe UI Web (West European)", "Segoe UI", -app=
le-system, BlinkMacSystemFont, Roboto, "Helvetica Neue", sans-ser=
if; background-color: rgb(255, 255, 255); display: inline !important">If yo=
u
are calling a void function, the return value should not be any problem.)<=
/span></span><br>
</div>
<div class=3D"PlainText" style=3D"font-size: 11pt;"><span style=3D"backgrou=
nd: var(--white);"><span style=3D"caret-color: rgb(50, 49, 48); font-family=
: "Segoe UI", "Segoe UI Web (West European)", "Seg=
oe UI", -apple-system, BlinkMacSystemFont, Roboto, "Helvetica Neu=
e", sans-serif; background-color: rgb(255, 255, 255); display: inline =
!important"><br>
</span></span></div>
<div class=3D"PlainText" style=3D"font-size: 11pt;"><span style=3D"font-fam=
ily: Menlo; font-size: 12px; font-style: normal; font-variant-caps: normal;=
background: var(--white);">DO</span><br>
</div>
<div class=3D"PlainText" style=3D"font-size: 11pt;"><span style=3D"backgrou=
nd: var(--white);">
<p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">$body$</p>
<p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo; color: #7f=
0055"><b>DECLARE</b><span style=3D"color: #000000"><span> </span></spa=
n></p>
<p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">command_st=
ring_list text[3];</p>
<p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo; color: #7f=
0055"><b>BEGIN</b></p>
<p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span></sp=
an>command_string_list[0] =3D
<span style=3D"color: #0000ff">'SELECT pg_sleep(1)'</span>;</p>
<p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span></sp=
an>command_string_list[1] =3D
<span style=3D"color: #0000ff">'SELECT pg_sleep(1)'</span>;</p>
<p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span></sp=
an>command_string_list[2] =3D
<span style=3D"color: #0000ff">'SELECT pg_sleep(1)'</span>;</p>
<p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span></sp=
an>EXECUTE_PARALLEL command_string_list;</p>
<p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo; color: #7f=
0055"><b>END</b></p>
<p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo">$body$;</p=
>
</span></div>
<div class=3D"PlainText" style=3D"font-size: 11pt;"><span style=3D"backgrou=
nd: var(--white);"><span style=3D"caret-color: rgb(50, 49, 48); font-family=
: "Segoe UI", "Segoe UI Web (West European)", "Seg=
oe UI", -apple-system, BlinkMacSystemFont, Roboto, "Helvetica Neu=
e", sans-serif; background-color: rgb(255, 255, 255); display: inline =
!important"><br>
</span></span></div>
<div class=3D"PlainText" style=3D"font-size: 11pt;"><span style=3D"backgrou=
nd: var(--white);"><span style=3D"caret-color: rgb(50, 49, 48); font-family=
: "Segoe UI", "Segoe UI Web (West European)", "Seg=
oe UI", -apple-system, BlinkMacSystemFont, Roboto, "Helvetica Neu=
e", sans-serif; background-color: rgb(255, 255, 255); display: inline =
!important">The
only way to this today as I understand it, is to open 3 new connections ba=
ck to the database which you can be done in different ways. </span></s=
pan></div>
<div class=3D"PlainText" style=3D"font-size: 11pt;"><span style=3D"font-fam=
ily: "Segoe UI", "Segoe UI Web (West European)", "=
Segoe UI", -apple-system, BlinkMacSystemFont, Roboto, "Helvetica =
Neue", sans-serif;"> </span></div>
<div class=3D"PlainText"><span style=3D"font-size: 11pt;">If we had a =
</span><span style=3D"font-size: 14.666666984558105px;">parallel</span><spa=
n style=3D"font-size: 11pt;"> </span><span style=3D"font-size: 14.6666=
66984558105px;">functions like the one above it's easier
to make parallel sql without using complex scripts, java, python or o=
ther system.</span></div>
<div class=3D"PlainText"><span style=3D"font-size: 14.666666984558105px; ba=
ckground: var(--white);"><br>
</span></div>
<div class=3D"PlainText"><span style=3D"font-size: 14.666666984558105px; ba=
ckground: var(--white);">Thanks.</span><br>
</div>
<div class=3D"PlainText"><span style=3D"font-size: 14.666666984558105px;"><=
br>
</span></div>
<div class=3D"PlainText" style=3D"font-size: 11pt;">Lars</div>
<div class=3D"PlainText" style=3D"font-size: 11pt;"><span style=3D"backgrou=
nd: var(--white);"><br>
</span></div>
</div>
</body>
</html>
--_000_HE1P189MB0266588071668BF3D5CF85479D590HE1P189MB0266EURP_--