MIME-Version: 1.0
References: 
 <CA+i5JwYMZ_eUtbdGBX9vg2GQzpTLo9m+bMoW1wniEqVv5sxV-w@mail.gmail.com>
In-Reply-To: 
 <CA+i5JwYMZ_eUtbdGBX9vg2GQzpTLo9m+bMoW1wniEqVv5sxV-w@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Sun, 10 Apr 2022 09:13:14 -0700
Message-ID: 
 <CAKFQuwYsSUU1USBTzBjyNFUTzgGbLqoxZyT7o2hhuYFcyfR2CA@mail.gmail.com>
Subject: Re: How best to do parallel query given tens of thousands of
 iteration of a loop of recursive queries?
To: Shaozhong SHI <shishaozhong@gmail.com>
Cc: pgsql-sql <pgsql-sql@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000c6e31205dc4f1b8c"
Archived-At: 
 <https://www.postgresql.org/message-id/CAKFQuwYsSUU1USBTzBjyNFUTzgGbLqoxZyT7o2hhuYFcyfR2CA%40mail.gmail.com>
Precedence: bulk

--000000000000c6e31205dc4f1b8c
Content-Type: text/plain; charset="UTF-8"

On Sun, Apr 10, 2022 at 6:50 AM Shaozhong SHI <shishaozhong@gmail.com>
wrote:

> There is a plpgsql script that have a loop to carry out the same recursive
> queries.
>
> The estimation of iteration is in the order of tens of thousands.
>
> What is the best way of making using parallel query strategy.
>

An example would be helpful.

However, as a general guideline, since parallelism is done at the per-row
scope, removing looping logic from the script and turning the main script
logic into one or more functions that operate on a single row, while
obeying the rules such functions need to abide by in order to be marked
parallel safe, will open up the possibility for the server to process
different rows using different workers and then appending their results
together for the next node to consume.

David J.

--000000000000c6e31205dc4f1b8c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_default" style=3D"fon=
t-family:arial,helvetica,sans-serif"><span style=3D"font-family:Arial,Helve=
tica,sans-serif">On Sun, Apr 10, 2022 at 6:50 AM Shaozhong SHI &lt;<a href=
=3D"mailto:shishaozhong@gmail.com">shishaozhong@gmail.com</a>&gt; wrote:</s=
pan><br></div></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_q=
uote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,2=
04);padding-left:1ex"><div dir=3D"ltr">There is a plpgsql script that have =
a loop to carry out the same recursive queries.<div><br></div><div>The esti=
mation of iteration is in the order of tens of thousands.</div><div><br></d=
iv><div>What is the best way of making using parallel query strategy.</div>=
</div></blockquote><div><br></div><div class=3D"gmail_default" style=3D"fon=
t-family:arial,helvetica,sans-serif">An example would be helpful.</div><div=
 class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif"><=
br></div><div class=3D"gmail_default" style=3D"font-family:arial,helvetica,=
sans-serif">However, as a general guideline, since parallelism is done at t=
he per-row scope, removing looping logic from the script and turning the ma=
in script logic into one or more functions that operate on a single row, wh=
ile obeying the rules such functions need to abide by in order to be marked=
 parallel safe, will open up the possibility for the server to process diff=
erent rows using different workers and then appending their results togethe=
r for the next node to consume.</div><div class=3D"gmail_default" style=3D"=
font-family:arial,helvetica,sans-serif"><br></div><div class=3D"gmail_defau=
lt" style=3D"font-family:arial,helvetica,sans-serif">David J.</div><div cla=
ss=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif"></div=
></div></div>

--000000000000c6e31205dc4f1b8c--