Re: How to run in parallel in Postgres
Lars Aksel Opsahl <[email protected]> Sat, 7 Dec 2019 11:17:25 +0000
| Newsgroups | gmane.comp.db.postgresql.performance |
|---|---|
| Message-ID | <HE1P189MB0266377CA007E5848FECDE529D5E0@HE1P189MB0266.EURP189.PROD.OUTLOOK.COM> |
--_000_HE1P189MB0266377CA007E5848FECDE529D5E0HE1P189MB0266EURP_ Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable >From: Ondrej Ivani=E8 <[email protected]> >Sent: Saturday, December 7, 2019 2:23 AM >Cc: [email protected] <[email protected]= resql.org> >Subject: Re: How to run in parallel in Postgres > >Hi Lars, > >I have two suggestions: > >- `xargs` almost always present and it can run in parallel (-P) but scrip= t needs to be changed: >for((i=3D1;i<=3D28;i++)); do echo "SELECT find_overlap_gap_single_cell('t= est_data.overlap_gap_input_t1','geom',4258,'test_data.overlap_gap_input_t1_= res',${I},28);"; done | xargs -n1 -P 10 psql ... > >- `UNION ALL` might trigger parallel execution (you need to mess with the= cost of the function and perhaps other settings): >SELECT > find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geom',425= 8,'test_data.overlap_gap_input_t1_res',1,28) >UNION ALL >SELECT > find_overlap_gap_single_cell('test_data.overlap_gap_input_t1','geom',425= 8,'test_data.overlap_gap_input_t1_res',2,28) >... > > >Cheers, > Hi Ondrej * Yes using xargs seems be an alternative to GNU parallel and I will ha= ve that in mind. * I did a test using UNION ALL in the branch https://github.com/larsop/= find-overlap-and-gap/tree/union_all_parallel but I was not able to trigger = Postgres parallel . That may be related to what Justin say about create ta= bles. Thanks. Lars --_000_HE1P189MB0266377CA007E5848FECDE529D5E0HE1P189MB0266EURP_ Content-Type: text/html; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable <html> <head> <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Diso-8859-= 2"> <style type=3D"text/css" style=3D"display:none;"> P {margin-top:0;margin-bo= ttom:0;} </style> </head> <body dir=3D"ltr"> <div style=3D"font-family: Calibri, Arial, Helvetica, sans-serif; font-size= : 12pt; color: rgb(0, 0, 0);"> </div> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span>&nbs= p;</span>>From: <span style=3D"text-decoration: underline">Ondrej</span> <span style=3D"tex= t-decoration: underline"> Ivani=E8</span> <ondrej.ivanic<span style=3D"text-decoration: underline"= >@gmail.com</span>></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span>&nbs= p;</span>>Sent: Saturday, December 7, 2019 2:23 AM</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span>&nbs= p;</span>><span style=3D"text-decoration: underline">Cc</span>: <span style=3D"text-decoration: underline">pgsql</span>-performance<span st= yle=3D"text-decoration: underline">@lists.postgresql.org</span> <pgsql-p= erformance<span style=3D"text-decoration: underline">@lists.postgresql.org<= /span>></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span>&nbs= p;</span>>Subject: Re: How to run in parallel in <span style=3D"text-decoration: underline">Postgres</span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span>&nbs= p;</span>><span> </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span>&nbs= p;</span>><span style=3D"text-decoration: underline">Hi</span> <span style=3D"text-decoration: underline">Lars</span>,</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span>&nbs= p;</span>></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span>&nbs= p;</span>>I have two suggestions:</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span>&nbs= p;</span>></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span>&nbs= p;</span>>- `<span style=3D"text-decoration: underline">xargs</span>` al= most always present and it can run in parallel (-P) but script needs to be = changed:</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span>&nbs= p;</span>>for((i=3D1;i<=3D28;i++)); do echo "SELECT find= _overlap_gap_single_cell('test_data.overlap_gap_input_t1','<span style=3D"t= ext-decoration: underline">geom</span>',4258,'test_data.overlap_gap_input_t= 1_res',${I},28);"; done | <span style=3D"text-decoration: underline">xargs</span> -n1 -P 10 <= span style=3D"text-decoration: underline"> psql</span> ...<span> </span></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span>&nbs= p;</span>></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span>&nbs= p;</span>>- `UNION ALL` might trigger parallel execution (you need to me= ss with the cost of the function and perhaps other settings):</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span>&nbs= p;</span>>SELECT</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span>&nbs= p;</span>> find_overlap_gap_single_cell('test_data.overlap_gap_input_t1'= ,'<span style=3D"text-decoration: underline">geom</span>',4258,'test_data.o= verlap_gap_input_t1_res',1,28)</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span>&nbs= p;</span>>UNION ALL</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span>&nbs= p;</span>>SELECT</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span>&nbs= p;</span>> find_overlap_gap_single_cell('test_data.overlap_gap_input_t1'= ,'<span style=3D"text-decoration: underline">geom</span>',4258,'test_data.o= verlap_gap_input_t1_res',2,28)</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span>&nbs= p;</span>>...</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span>&nbs= p;</span>></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span>&nbs= p;</span>></p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span>&nbs= p;</span>>Cheers,</p> <p style=3D"margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Menlo"><span>&nbs= p;</span>></p> <br> <div> <div> <div class=3D"x_gmail_quote"> <div dir=3D"ltr" class=3D"x_gmail_attr"><span style=3D"color: rgb(0, 0, 0);= font-family: Calibri, Arial, Helvetica, sans-serif; font-size: 12pt; backg= round: var(--white);">Hi Ondrej</span><br> </div> <div dir=3D"ltr" class=3D"x_gmail_attr"> <div style=3D"margin: 0px; font-family: Calibri, Arial, Helvetica, sans-ser= if; font-size: 12pt; color: rgb(0, 0, 0)"> <br> </div> <div style=3D"margin: 0px"> <ul> <li><font color=3D"#000000" face=3D"Calibri, Arial, Helvetica, sans-serif">= <span style=3D"margin: 0px; caret-color: rgb(0, 0, 0)">Yes using xargs= seems be an alternative to GNU </span><span style=3D"caret-color: rgb= (0, 0, 0);">parallel and I will have that in mind.</span></font></li><li><f= ont color=3D"#000000" face=3D"Calibri, Arial, Helvetica, sans-serif">I did = a test using UNION ALL in the branch <a href=3D"https://github.com/larsop/find-overlap-and-gap/tree/union_all_pa= rallel" id=3D"LPNoLP584677" style=3D"caret-color: rgb(0, 0, 0);"> https://github.com/larsop/find-overlap-and-gap/tree/union_all_parallel</a>&= nbsp;but I was not able to trigger Postgres parallel . That may be re= lated to what Justin say about create tables.</font></li><br> </ul> </div> <div style=3D"margin: 0px; font-family: Calibri, Arial, Helvetica, sans-ser= if; font-size: 12pt; color: rgb(0, 0, 0)"> <span style=3D"color: rgb(0, 0, 0); font-family: Calibri, Arial, Helvetica,= sans-serif; font-size: 12pt; background: var(--white);">Thanks.</span><br> </div> <div style=3D"margin: 0px; font-family: Calibri, Arial, Helvetica, sans-ser= if; font-size: 12pt; color: rgb(0, 0, 0)"> <br> </div> <div style=3D"margin: 0px; font-family: Calibri, Arial, Helvetica, sans-ser= if; font-size: 12pt; color: rgb(0, 0, 0)"> Lars</div> <div style=3D"margin: 0px"> <div style=3D"margin: 0px; font-family: "Segoe UI", "Segoe U= I Web (West European)", "Segoe UI", -apple-system, BlinkMacS= ystemFont, Roboto, "Helvetica Neue", sans-serif; caret-color: rgb= (50, 49, 48)"> </div> </div> <br> <br> </div> </div> </div> </div> </body> </html> --_000_HE1P189MB0266377CA007E5848FECDE529D5E0HE1P189MB0266EURP_--