Re: Choosing parallel_degree - Mailing list pgsql-hackers

On 11/04/2016 15:56, tushar wrote:
> On 04/08/2016 08:53 PM, Robert Haas wrote:
>> On Fri, Apr 8, 2016 at 1:22 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:
>>> Other than that, patch looks good and I have marked it as Ready For
>>> Committer.  Hope, we get this for 9.6.
>> Committed.  I think this is likely to make parallel query
>> significantly more usable in 9.6.
>>
> While testing ,I observed couple of things -
> 
> Case 1 =Not accepting parallel seq scan when parallel_degree is set to 0
> 
> postgres=# create table fok2(n int) with (parallel_degree=0);
> CREATE TABLE
> postgres=# insert into fok2 values (generate_series(1,1000000)); analyze
> fok2; vacuum fok2;
> INSERT 0 1000000
> ANALYZE
> VACUUM
> postgres=# set max_parallel_degree =5;
> SET
> postgres=# explain analyze verbose   select * from fok2  where n<=10;
>                                                   QUERY
> PLAN                                                 
> --------------------------------------------------------------------------------------------------------------
>  Seq Scan on public.fok2  (cost=0.00..16925.00 rows=100 width=4) (actual
> time=0.027..217.882 rows=10 loops=1)
>    Output: n
>    Filter: (fok2.n <= 10)
>    Rows Removed by Filter: 999990
>  Planning time: 0.084 ms
>  Execution time: 217.935 ms
> (6 rows)
> 
> I am assuming parallel_degree=0 is as same as not using it  , i.e
> create table fok2(n int) with (parallel_degree=0);  = create table
> fok2(n int);
> 
> so in this case it should have accepted the parallel seq .scan.
> 

No, setting it to 0 means to force not using parallel workers (but
considering the parallel path IIRC).

> Case 2=Total no# of workers are NOT matching with the workers information -
> 
> postgres=# alter table fok set (parallel_degree=10);
> ALTER TABLE
> postgres=# set max_parallel_degree =9;
> SET
> postgres=# explain analyze verbose   select * from fok  where n<=1;
>                                                        QUERY
> PLAN                                                       
>
-------------------------------------------------------------------------------------------------------------------------
>  Gather  (cost=1000.00..6823.89 rows=100 width=4) (actual
> time=0.621..107.755 rows=1 loops=1)
>    Output: n
> *   Number of Workers: 9*
>    ->  Parallel Seq Scan on public.fok  (cost=0.00..5814.00 rows=11
> width=4) (actual time=83.382..95.157 rows=0 loops=9)
>          Output: n
>          Filter: (fok.n <= 1)
>          Rows Removed by Filter: 111111
>          Worker 0: actual time=82.181..82.181 rows=0 loops=1
>          Worker 1: actual time=97.236..97.236 rows=0 loops=1
>          Worker 2: actual time=93.586..93.586 rows=0 loops=1
>          Worker 3: actual time=94.159..94.159 rows=0 loops=1
>          Worker 4: actual time=88.459..88.459 rows=0 loops=1
>          Worker 5: actual time=90.245..90.245 rows=0 loops=1
>          Worker 6: actual time=101.577..101.577 rows=0 loops=1
>          Worker 7: actual time=102.955..102.955 rows=0 loops=1
>  Planning time: 0.119 ms
>  Execution time: 108.585 ms
> (17 rows)
> 
> Expected = Expecting worker8 information , also loops=10 (including the
> Master)
> 

Even if you set a per-table parallel_degree higher than
max_parallel_degree, it'll be maxed at max_parallel_degree.

Then, the explain shows that the planner assumed it'll launch 9 workers,
but only 8 were available (or needed perhaps) at runtime.

> Case 3=Getting error if we set the max value in max_parallel_degree  as
> well in parallel_degree  .
> 
> postgres=# create table abd(n int) with (parallel_degree=262144);
> ERROR:  value 262144 out of bounds for option "parallel_degree"
> DETAIL:  Valid values are between "0" and "262143".
> 
> postgres=# create table abd(n int) with (parallel_degree=262143);
> CREATE TABLE
> postgres=# insert into abd values (generate_series(1,1000000)); analyze
> abd; vacuum abd;
> INSERT 0 1000000
> ANALYZE
> 
> postgres=# set max_parallel_degree =2624444;
> ERROR:  2624444 is outside the valid range for parameter
> "max_parallel_degree" (0 .. 262143)
> 
> postgres=# set max_parallel_degree =262143;
> SET
> postgres=#
> 
> postgres=# explain analyze verbose select * from abd  where n<=1;
> ERROR:  requested shared memory size overflows size_t
> 
> if we remove the analyze keyword then query running successfully.
> 
> Expected = Is it not better to throw the error at the time of setting
> max_parallel_degree, if not supported ?

+1


-- 
Julien Rouhaud
http://dalibo.com - http://dalibo.org