Re: Enabling parallelism for queries coming from SQL or other PL functions

On Wed, Mar 22, 2017 at 12:55 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Tue, Mar 21, 2017 at 6:36 AM, Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>> How about taking the decision of execute_once based on
>> fcache->returnsSet instead of based on lazyEval?
>>
>> change
>> + ExecutorRun(es->qd, ForwardScanDirection, count, !es->lazyEval);
>> to
>> + ExecutorRun(es->qd, ForwardScanDirection, count, !fcache->returnsSet);
>>
>> IMHO, Robert have the same thing in mind?
>
> Yeah, something like that.
>
Done in execute-once-v2.patch

Apart from this, I also observed that in case of SQL functions,
cursorOptions are not set properly, note in init_execution_state,

stmt = pg_plan_query(queryTree,
fcache->readonly_func ? CURSOR_OPT_PARALLEL_OK : 0,
NULL)

Now, this fcache->readonly_func is set in init_sql_fcache,

/* Remember if function is STABLE/IMMUTABLE */
fcache->readonly_func =
(procedureStruct->provolatile != PROVOLATILE_VOLATILE);

so, if parallel safe stable is missing in function definition then it
is not as readonly as per this code. Also, we can see that this is set
as per function rather than per query as in case of other PL
functions. So, I did

stmt = pg_plan_query(queryTree,
- fcache->readonly_func ? CURSOR_OPT_PARALLEL_OK : 0,
+ CURSOR_OPT_PARALLEL_OK,
NULL);

Now, if the query is an update/insert/delete statement then it is
anyways taken care by planner and is not parallelised. This also
enables parallelism for the case when one query is select and another
is update in an SQL function which couldn't be done before.

This is done in pl_parallel_opt_v3.patch.

>>>SELECT * FROM blah() LIMIT 3
>>>
>>>...will trigger three separate calls to ExecutorRun(), which is a
>>>problem if the plan is a parallel plan.
>>
>> And you also need to test this case what Robert have mentioned up thread.
>
> +1
Checked, nope ExecutorRun is called only once in this case and
execute_once is true here.

--
Regards,
Rafia Sabih
EnterpriseDB: http://www.enterprisedb.com/