Re: Choosing parallel_degree

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.

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)

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 ?

--
regards,tushar