Re: Choosing parallel_degree

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(dot)kapila16(at)gmail(dot)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