Re: Choosing parallel_degree - Mailing list pgsql-hackers

<div class="moz-cite-prefix">On 04/08/2016 08:53 PM, Robert Haas wrote:<br /></div><blockquote
cite="mid:CA+TgmoaVcydkmML4nKk0wJ0roZf-WKLH8VdvWjAnQ=PjoEeqxQ@mail.gmail.com"type="cite"><pre wrap="">On Fri, Apr 8,
2016at 1:22 AM, Amit Kapila <a class="moz-txt-link-rfc2396E"
href="mailto:amit.kapila16@gmail.com"><amit.kapila16@gmail.com></a>wrote:
 
</pre><blockquote type="cite"><pre wrap="">Other than that, patch looks good and I have marked it as Ready For
Committer.  Hope, we get this for 9.6.
</pre></blockquote><pre wrap="">
Committed.  I think this is likely to make parallel query
significantly more usable in 9.6.

</pre></blockquote> While testing ,I observed couple of things - <br /><br /> Case 1 =Not accepting parallel seq scan
whenparallel_degree is set to 0<br /><br /> postgres=# create table fok2(n int) with (parallel_degree=0);<br /> CREATE
TABLE<br/> postgres=# insert into fok2 values (generate_series(1,1000000)); analyze fok2; vacuum fok2;<br /> INSERT 0
1000000<br/> ANALYZE<br /> VACUUM<br /> postgres=# set max_parallel_degree =5;<br /> SET<br /> postgres=# explain
analyzeverbose   select * from fok2  where n<=10;<br />                                                   QUERY
PLAN                                                 <br />
--------------------------------------------------------------------------------------------------------------<br/>
 SeqScan on public.fok2  (cost=0.00..16925.00 rows=100 width=4) (actual time=0.027..217.882 rows=10 loops=1)<br />   
Output:n<br />    Filter: (fok2.n <= 10)<br />    Rows Removed by Filter: 999990<br />  Planning time: 0.084 ms<br
/> Execution time: 217.935 ms<br /> (6 rows)<br /><br /> I am assuming parallel_degree=0 is as same as not using it  ,
i.e<br /> create table fok2(n int) with (parallel_degree=0);  = create table fok2(n int);<br /><br /> so in this case
itshould have accepted the parallel seq .scan.<br /><br /> Case 2=Total no# of workers are NOT matching with the
workersinformation -<br /><br /> postgres=# alter table fok set (parallel_degree=10);<br /> ALTER TABLE<br />
postgres=#set max_parallel_degree =9;<br /> SET<br /> postgres=# explain analyze verbose   select * from fok  where
n<=1;<br/>                                                        QUERY
PLAN                                                       <br />
-------------------------------------------------------------------------------------------------------------------------<br
/> Gather  (cost=1000.00..6823.89 rows=100 width=4) (actual time=0.621..107.755 rows=1 loops=1)<br />    Output: n<br
/><b>  Number of Workers: 9</b><br />    ->  Parallel Seq Scan on public.fok  (cost=0.00..5814.00 rows=11 width=4)
(actualtime=83.382..95.157 rows=0 loops=9)<br />          Output: n<br />          Filter: (fok.n <= 1)<br />
        Rows Removed by Filter: 111111<br />          Worker 0: actual time=82.181..82.181 rows=0 loops=1<br />
        Worker 1: actual time=97.236..97.236 rows=0 loops=1<br />          Worker 2: actual time=93.586..93.586 rows=0
loops=1<br/>          Worker 3: actual time=94.159..94.159 rows=0 loops=1<br />          Worker 4: actual
time=88.459..88.459rows=0 loops=1<br />          Worker 5: actual time=90.245..90.245 rows=0 loops=1<br />         
Worker6: actual time=101.577..101.577 rows=0 loops=1<br />          Worker 7: actual time=102.955..102.955 rows=0
loops=1<br/>  Planning time: 0.119 ms<br />  Execution time: 108.585 ms<br /> (17 rows)<br /><br /> Expected =
Expectingworker8 information , also loops=10 (including the Master)<br /><br /> Case 3=Getting error if we set the max
valuein max_parallel_degree  as well in parallel_degree  .<br /><br /> postgres=# create table abd(n int) with
(parallel_degree=262144);<br/> ERROR:  value 262144 out of bounds for option "parallel_degree"<br /> DETAIL:  Valid
valuesare between "0" and "262143".<br /><br /> postgres=# create table abd(n int) with (parallel_degree=262143);<br />
CREATETABLE<br /> postgres=# insert into abd values (generate_series(1,1000000)); analyze abd; vacuum abd;<br /> INSERT
01000000<br /> ANALYZE<br /><br /> postgres=# set max_parallel_degree =2624444;<br /> ERROR:  2624444 is outside the
validrange for parameter "max_parallel_degree" (0 .. 262143)<br /><br /> postgres=# set max_parallel_degree =262143;<br
/>SET<br /> postgres=# <br /><br /> postgres=# explain analyze verbose select * from abd  where n<=1;<br /> ERROR: 
requestedshared memory size overflows size_t<br /><br /> if we remove the analyze keyword then query running
successfully.<br/><br /> Expected = Is it not better to throw the error at the time of setting max_parallel_degree, if
notsupported ?<br /><pre class="moz-signature" cols="72">-- 
 
regards,tushar
</pre>