Enabling parallelism for queries coming from SQL or other PL functions

Started by Rafia Sabihover 9 years ago36 messageshackers
Jump to latest
#1Rafia Sabih
rafia.pghackers@gmail.com

Hello everybody,

In the current version, queries in SQL or PL functions can not
leverage parallelism. To relax this restriction I prepared a patch,
the approach used in the patch is explained next,

Approach:

1. Allow parallelism for queries in PL functions by passing
CURSOR_OPT_PARALLEL_OK instead of 0 to exec_prepare_plan called from
exec_stmt_execsql or exec_stmt_dynexecute. Similarly, pass
CURSOR_OPT_PARALLEL_OK instead of 0 to SPI_execute and exec_run_select
called from exec_stmt_dynexecute. CURSOR_OPT_PARALLEL_OK is passed to
these functions after checking if the statement is not trigger, since
in that case using parallelism may not be efficient.

2. In ExecutePlan there is an additional check to see if the query is
coming from SQL or PL functions and is having a parallel plan. In that
case we ignore the check of numberTuples since it is always one for
these functions and existing checks restrict parallelism for these
cases. Though, I understand this may not be the most elegant way to do
this, and would be pleased to know some better alternative.

I have attached a sql file containing cases for some pgpsql, perl,
python functions and an .out file which contains the parallel plans
for the queries in these functions after this patch. This might be
helpful in understanding the level of parallelism this patch is
relaxing for PL functions.

Thanks to my colleagues Amit Kapila and Dilip Kumar for discussions in
this regard.

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

Attachments:

pl_parallel_v1.patchapplication/octet-stream; name=pl_parallel_v1.patchDownload+58-8
pl_parallel.outapplication/octet-stream; name=pl_parallel.outDownload
pl_parallel_test.sqlapplication/octet-stream; name=pl_parallel_test.sqlDownload
#2Dilip Kumar
dilipbalaut@gmail.com
In reply to: Rafia Sabih (#1)
Re: Enabling parallelism for queries coming from SQL or other PL functions

On Wed, Feb 22, 2017 at 10:25 AM, Rafia Sabih
<rafia.sabih@enterprisedb.com> wrote:

Hello everybody,

In the current version, queries in SQL or PL functions can not
leverage parallelism. To relax this restriction I prepared a patch,
the approach used in the patch is explained next,

Some initial comments.

----------
if (numberTuples || dest->mydest == DestIntoRel)
use_parallel_mode = false;

if (use_parallel_mode)
EnterParallelMode();
+ else if (estate->es_plannedstmt->parallelModeNeeded &&
+ (dest->mydest == DestSPI || dest->mydest == DestSQLFunction))
+ {
+ use_parallel_mode = true;
+ EnterParallelMode();
+ }

I think we can simplify this, can we replace above code with something
like this?

if (dest->mydest == DestIntoRel ||
numberTuples && (dest->mydest != DestSPI || dest->mydest !=
DestSQLFunction))
use_parallel_mode = false;

-------------

+ {
+ /* Allow parallelism if the function is not trigger type. */
+ if (estate->func->fn_is_trigger == PLPGSQL_NOT_TRIGGER)
+ exec_res = SPI_execute(querystr, estate->readonly_func,
CURSOR_OPT_PARALLEL_OK);
+ else
+ exec_res = SPI_execute(querystr, estate->readonly_func, 0);
+ }

The last parameter of SPI_execute is tuple count, not cursorOption,
you need to fix this. Also, this is crossing the 80 line boundary.

-----------
Any specific reason for not changing SPI_execute_with_args, EXECUTE
with USING will take this path.

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#3Rafia Sabih
rafia.pghackers@gmail.com
In reply to: Dilip Kumar (#2)
Re: Enabling parallelism for queries coming from SQL or other PL functions

On Wed, Feb 22, 2017 at 10:22 PM, Dilip Kumar <dilipbalaut@gmail.com> wrote:

Some initial comments.

----------
if (numberTuples || dest->mydest == DestIntoRel)
use_parallel_mode = false;

if (use_parallel_mode)
EnterParallelMode();
+ else if (estate->es_plannedstmt->parallelModeNeeded &&
+ (dest->mydest == DestSPI || dest->mydest == DestSQLFunction))
+ {
+ use_parallel_mode = true;
+ EnterParallelMode();
+ }

I think we can simplify this, can we replace above code with something
like this?

if (dest->mydest == DestIntoRel ||
numberTuples && (dest->mydest != DestSPI || dest->mydest !=
DestSQLFunction))
use_parallel_mode = false;

Yes, it can be simplified to
if (dest->mydest == DestIntoRel || (numberTuples && (dest->mydest !=
DestSPI && dest->mydest ! DestSQLFunction)))
Thanks.

-------------

+ {
+ /* Allow parallelism if the function is not trigger type. */
+ if (estate->func->fn_is_trigger == PLPGSQL_NOT_TRIGGER)
+ exec_res = SPI_execute(querystr, estate->readonly_func,
CURSOR_OPT_PARALLEL_OK);
+ else
+ exec_res = SPI_execute(querystr, estate->readonly_func, 0);
+ }

The last parameter of SPI_execute is tuple count, not cursorOption,
you need to fix this. Also, this is crossing the 80 line boundary.

Oops, corrected.

-----------
Any specific reason for not changing SPI_execute_with_args, EXECUTE
with USING will take this path.

Fixed.

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

Attachments:

pl_parallel_v2.patchapplication/octet-stream; name=pl_parallel_v2.patchDownload+48-8
#4Dilip Kumar
dilipbalaut@gmail.com
In reply to: Rafia Sabih (#3)
Re: Enabling parallelism for queries coming from SQL or other PL functions

On Thu, Feb 23, 2017 at 12:11 PM, Rafia Sabih
<rafia.sabih@enterprisedb.com> wrote:

Yes, it can be simplified to
if (dest->mydest == DestIntoRel || (numberTuples && (dest->mydest !=
DestSPI && dest->mydest ! DestSQLFunction)))
Thanks.

Okay, this looks cleaner.

Few more comments.
1.I don't see any check in the code which will prevent the parallel
execution of the query inside a function if its called from a DML
statement.
e.g. If we use a function in the update statement's which has the
select statement.

2. How are you protecting, if the outer select is running in parallel,
then the function called from there should not run anything in
parallel? This may allow worker launching another set of workers. Am
I missing something?

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#5Dilip Kumar
dilipbalaut@gmail.com
In reply to: Dilip Kumar (#4)
Re: Enabling parallelism for queries coming from SQL or other PL functions

On Thu, Feb 23, 2017 at 8:58 PM, Dilip Kumar <dilipbalaut@gmail.com> wrote:

Few more comments.
1.I don't see any check in the code which will prevent the parallel
execution of the query inside a function if its called from a DML
statement.
e.g. If we use a function in the update statement's which has the
select statement.

Having said that, I am thinking do we really need to block such cases?
It just looks fine to me that an update statement calls a function (in
targetlist or condition), which launches a bunch of workers for the
internal query inside PL; finishes the work and shutdown them, only
after this, the update will change any record. So basically I want to
make a point that between the worker launch and shutdown there is no
change in the database state.

Any other opinion on this?

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#6Amit Kapila
amit.kapila16@gmail.com
In reply to: Dilip Kumar (#4)
Re: Enabling parallelism for queries coming from SQL or other PL functions

On Thu, Feb 23, 2017 at 8:58 PM, Dilip Kumar <dilipbalaut@gmail.com> wrote:

On Thu, Feb 23, 2017 at 12:11 PM, Rafia Sabih
<rafia.sabih@enterprisedb.com> wrote:

2. How are you protecting, if the outer select is running in parallel,
then the function called from there should not run anything in
parallel? This may allow worker launching another set of workers. Am
I missing something?

We have a below check in standard_planner() (!IsParallelWorker())
which should prohibit generating parallel plan inside worker, if that
is what you are seeing, then we might need a similar check at other
places.

if ((cursorOptions & CURSOR_OPT_PARALLEL_OK) != 0 &&
IsUnderPostmaster &&
dynamic_shared_memory_type != DSM_IMPL_NONE &&
parse->commandType == CMD_SELECT &&
!parse->hasModifyingCTE &&
max_parallel_workers_per_gather > 0 &&
!IsParallelWorker() &&
!IsolationIsSerializable())
{
/* all the cheap tests pass, so scan the query tree */
glob->maxParallelHazard = max_parallel_hazard(parse);
glob->parallelModeOK = (glob->maxParallelHazard != PROPARALLEL_UNSAFE);
}

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#7Amit Kapila
amit.kapila16@gmail.com
In reply to: Dilip Kumar (#5)
Re: Enabling parallelism for queries coming from SQL or other PL functions

On Thu, Feb 23, 2017 at 9:20 PM, Dilip Kumar <dilipbalaut@gmail.com> wrote:

On Thu, Feb 23, 2017 at 8:58 PM, Dilip Kumar <dilipbalaut@gmail.com> wrote:

Few more comments.
1.I don't see any check in the code which will prevent the parallel
execution of the query inside a function if its called from a DML
statement.
e.g. If we use a function in the update statement's which has the
select statement.

Having said that, I am thinking do we really need to block such cases?
It just looks fine to me that an update statement calls a function (in
targetlist or condition), which launches a bunch of workers for the
internal query inside PL; finishes the work and shutdown them, only
after this, the update will change any record. So basically I want to
make a point that between the worker launch and shutdown there is no
change in the database state.

+1. I also think you are right that there should not be a problem in
such a case.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#8Dilip Kumar
dilipbalaut@gmail.com
In reply to: Amit Kapila (#6)
Re: Enabling parallelism for queries coming from SQL or other PL functions

On Fri, Feb 24, 2017 at 10:06 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:

We have a below check in standard_planner() (!IsParallelWorker())
which should prohibit generating parallel plan inside worker, if that
is what you are seeing, then we might need a similar check at other
places.

if ((cursorOptions & CURSOR_OPT_PARALLEL_OK) != 0 &&
IsUnderPostmaster &&
dynamic_shared_memory_type != DSM_IMPL_NONE &&
parse->commandType == CMD_SELECT &&
!parse->hasModifyingCTE &&
max_parallel_workers_per_gather > 0 &&
!IsParallelWorker() &&
!IsolationIsSerializable())
{
/* all the cheap tests pass, so scan the query tree */
glob->maxParallelHazard = max_parallel_hazard(parse);
glob->parallelModeOK = (glob->maxParallelHazard != PROPARALLEL_UNSAFE);
}

Ok, I see. But, the problem with PL functions is that plan might have
already generated in previous execution of the function and during
that time outer query might not be running in parallel. So I think we
may need some check during execution time as well?

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#9Amit Kapila
amit.kapila16@gmail.com
In reply to: Dilip Kumar (#8)
Re: Enabling parallelism for queries coming from SQL or other PL functions

On Fri, Feb 24, 2017 at 11:30 AM, Dilip Kumar <dilipbalaut@gmail.com> wrote:

On Fri, Feb 24, 2017 at 10:06 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:

We have a below check in standard_planner() (!IsParallelWorker())
which should prohibit generating parallel plan inside worker, if that
is what you are seeing, then we might need a similar check at other
places.

if ((cursorOptions & CURSOR_OPT_PARALLEL_OK) != 0 &&
IsUnderPostmaster &&
dynamic_shared_memory_type != DSM_IMPL_NONE &&
parse->commandType == CMD_SELECT &&
!parse->hasModifyingCTE &&
max_parallel_workers_per_gather > 0 &&
!IsParallelWorker() &&
!IsolationIsSerializable())
{
/* all the cheap tests pass, so scan the query tree */
glob->maxParallelHazard = max_parallel_hazard(parse);
glob->parallelModeOK = (glob->maxParallelHazard != PROPARALLEL_UNSAFE);
}

Ok, I see. But, the problem with PL functions is that plan might have
already generated in previous execution of the function and during
that time outer query might not be running in parallel.

Sure, but that should only happen if the function is *not* declared as
parallel safe (aka in parallel safe functions, we should not generate
parallel plans).

So I think we
may need some check during execution time as well?

Right, I also think we need some mechanism where if the user has not
marked the parallel safe functions appropriately, then such executions
should result in error. For example, if parallel-safe function calls
a parallel-unsafe function which contains either write statement or
statement that could generate a parallel plan, then we should not
allow execution of such queries. We already have safeguard checks at
most places like write statements (see heap_update), however, I think
we need a similar check in ExecGather.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#10Dilip Kumar
dilipbalaut@gmail.com
In reply to: Amit Kapila (#9)
Re: Enabling parallelism for queries coming from SQL or other PL functions

On Sat, Feb 25, 2017 at 5:12 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:

Sure, but that should only happen if the function is *not* declared as
parallel safe (aka in parallel safe functions, we should not generate
parallel plans).

So basically we want to put a restriction that parallel-safe function
can not use the parallel query? This will work but it seems too
restrictive to me. Because by marking function parallel safe we enable
it to be used with the outer parallel query that is fine. But, that
should not restrict the function from using the parallel query if it's
used with the other outer query which is not having the parallel
plan(or function is executed directly).

So I think we
may need some check during execution time as well?

Right, I also think we need some mechanism where if the user has not
marked the parallel safe functions appropriately, then such executions
should result in error. For example, if parallel-safe function calls
a parallel-unsafe function which contains either write statement or
statement that could generate a parallel plan, then we should not
allow execution of such queries. We already have safeguard checks at
most places like write statements (see heap_update), however, I think
we need a similar check in ExecGather.

How about we allow parallel-safe functions to create a parallel plan
but whenever it's used from an unsafe place i.e. already in the
parallel mode we don't allow to launch worker?

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#11Amit Kapila
amit.kapila16@gmail.com
In reply to: Dilip Kumar (#10)
Re: Enabling parallelism for queries coming from SQL or other PL functions

On Sat, Feb 25, 2017 at 9:47 PM, Dilip Kumar <dilipbalaut@gmail.com> wrote:

On Sat, Feb 25, 2017 at 5:12 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:

Sure, but that should only happen if the function is *not* declared as
parallel safe (aka in parallel safe functions, we should not generate
parallel plans).

So basically we want to put a restriction that parallel-safe function
can not use the parallel query? This will work but it seems too
restrictive to me. Because by marking function parallel safe we enable
it to be used with the outer parallel query that is fine. But, that
should not restrict the function from using the parallel query if it's
used with the other outer query which is not having the parallel
plan(or function is executed directly).

I think if the user is explicitly marking a function as parallel-safe,
then it doesn't make much sense to allow parallel query in such
functions as it won't be feasible for the planner (or at least it will
be quite expensive) to detect the same. By the way, if the user has
any such expectation from a function, then he can mark the function as
parallel-restricted or parallel-unsafe.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#12Robert Haas
robertmhaas@gmail.com
In reply to: Amit Kapila (#11)
Re: Enabling parallelism for queries coming from SQL or other PL functions

On Sun, Feb 26, 2017 at 6:34 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:

On Sat, Feb 25, 2017 at 9:47 PM, Dilip Kumar <dilipbalaut@gmail.com> wrote:

On Sat, Feb 25, 2017 at 5:12 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:

Sure, but that should only happen if the function is *not* declared as
parallel safe (aka in parallel safe functions, we should not generate
parallel plans).

So basically we want to put a restriction that parallel-safe function
can not use the parallel query? This will work but it seems too
restrictive to me. Because by marking function parallel safe we enable
it to be used with the outer parallel query that is fine. But, that
should not restrict the function from using the parallel query if it's
used with the other outer query which is not having the parallel
plan(or function is executed directly).

I think if the user is explicitly marking a function as parallel-safe,
then it doesn't make much sense to allow parallel query in such
functions as it won't be feasible for the planner (or at least it will
be quite expensive) to detect the same. By the way, if the user has
any such expectation from a function, then he can mark the function as
parallel-restricted or parallel-unsafe.

However, if a query is parallel-safe, it might not end up getting run
in parallel. In that case, it could still benefit from parallelism
internally. I think we want to allow that. For example, suppose you
run a query like:

SELECT x, sum(somewhat_expensive_function(y)) FROM sometab GROUP BY 1;

If sometab isn't very big, it's probably better to use a non-parallel
plan for this query, because then somewhat_expensive_function() can
still use parallelism internally, which might be better. However, if
sometab is large enough, then it might be better to parallelize the
whole query using a Partial/FinalizeAggregate and force each call to
somewhat_expensive_function() to run serially. So I don't think a
hard-and-fast rule that parallel-safe functions can't use parallelism
internally is a good idea.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#13Robert Haas
robertmhaas@gmail.com
In reply to: Rafia Sabih (#1)
Re: Enabling parallelism for queries coming from SQL or other PL functions

On Wed, Feb 22, 2017 at 10:25 AM, Rafia Sabih
<rafia.sabih@enterprisedb.com> wrote:

1. Allow parallelism for queries in PL functions by passing
CURSOR_OPT_PARALLEL_OK instead of 0 to exec_prepare_plan called from
exec_stmt_execsql or exec_stmt_dynexecute. Similarly, pass
CURSOR_OPT_PARALLEL_OK instead of 0 to SPI_execute and exec_run_select
called from exec_stmt_dynexecute. CURSOR_OPT_PARALLEL_OK is passed to
these functions after checking if the statement is not trigger, since
in that case using parallelism may not be efficient.

2. In ExecutePlan there is an additional check to see if the query is
coming from SQL or PL functions and is having a parallel plan. In that
case we ignore the check of numberTuples since it is always one for
these functions and existing checks restrict parallelism for these
cases. Though, I understand this may not be the most elegant way to do
this, and would be pleased to know some better alternative.

I think I see the problem that you're trying to solve, but I agree
that this doesn't seem all that elegant. The reason why we have that
numberTuples check is because we're afraid that we might be in a
context like the extended-query protocol, where the caller can ask for
1 tuple, and then later ask for another tuple. That won't work,
because once we shut down the workers we can't reliably generate the
rest of the query results. However, I think it would probably work
fine to let somebody ask for less than the full number of tuples if
it's certain that they won't later ask for any more.

So maybe what we ought to do is allow CURSOR_OPT_PARALLEL_OK to be set
any time we know that ExecutorRun() will be called for the QueryDesc
at most once rather than (as at present) only where we know it will be
executed only once with a tuple-count of zero. Then we could change
things in ExecutePlan so that it doesn't disable parallel query when
the tuple-count is non-zero, but does take an extra argument "bool
execute_only_once", and it disables parallel execution if that is not
true. Also, if ExecutorRun() is called a second time for the same
QueryDesc when execute_only_once is specified as true, it should
elog(ERROR, ...). Then exec_execute_message(), for example, can pass
that argument as false when the tuple-count is non-zero, but other
places that are going to fetch a limited number of rows could pass it
as true even though they also pass a row-count.

I'm not sure if that's exactly right, but something along those lines
seems like it should work.

I think that a final patch for this functionality should involve
adding CURSOR_OPT_PARALLEL_OK to appropriate places in each PL, plus
maybe some infrastructure changes like the ones mentioned above.
Maybe it can be divided into two patches, one to make the
infrastructure changes and a second to add CURSOR_OPT_PARALLEL_OK to
more places.

+    /* Allow parallelism if the query is read-only */
+    if(read_only)
+        plan.cursor_options = CURSOR_OPT_PARALLEL_OK;
+    else
+        plan.cursor_options = 0;

I don't think this can ever be right. If the only thing we are
worried about is the query being read-only, then we could just pass
CURSOR_OPT_PARALLEL_OK everywhere and planner.c would figure it out
without any help from us. But that's not the problem. The problem is
that the PL may be using a function like SPI_prepare_cursor(), where
it's going to later use SPI_cursor_fetch() or similar. Parallel query
can't handle cursor operations. Whatever changes we make to spi.c
should involve passing CURSOR_OPT_PARALLEL_OK everywhere that we can
be sure there will be no cursor operations and not anywhere that we
might have cursor operations. Cursor operations - or more
specifically anything that might try to suspend execution of the query
and resume later - are the problem. Things that will already cause
the tests in standard_planner() to disable parallelism don't need to
be rechecked elsewhere:

if ((cursorOptions & CURSOR_OPT_PARALLEL_OK) != 0 &&
IsUnderPostmaster &&
dynamic_shared_memory_type != DSM_IMPL_NONE &&
parse->commandType == CMD_SELECT &&
!parse->hasModifyingCTE &&
max_parallel_workers_per_gather > 0 &&
!IsParallelWorker() &&
!IsolationIsSerializable())
{
/* all the cheap tests pass, so scan the query tree */
glob->maxParallelHazard = max_parallel_hazard(parse);
glob->parallelModeOK = (glob->maxParallelHazard != PROPARALLEL_UNSAFE);
}
else
{
/* skip the query tree scan, just assume it's unsafe */
glob->maxParallelHazard = PROPARALLEL_UNSAFE;
glob->parallelModeOK = false;
}

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#14Amit Kapila
amit.kapila16@gmail.com
In reply to: Robert Haas (#12)
Re: Enabling parallelism for queries coming from SQL or other PL functions

On Sun, Feb 26, 2017 at 4:14 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Sun, Feb 26, 2017 at 6:34 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:

On Sat, Feb 25, 2017 at 9:47 PM, Dilip Kumar <dilipbalaut@gmail.com> wrote:

On Sat, Feb 25, 2017 at 5:12 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:

Sure, but that should only happen if the function is *not* declared as
parallel safe (aka in parallel safe functions, we should not generate
parallel plans).

So basically we want to put a restriction that parallel-safe function
can not use the parallel query? This will work but it seems too
restrictive to me. Because by marking function parallel safe we enable
it to be used with the outer parallel query that is fine. But, that
should not restrict the function from using the parallel query if it's
used with the other outer query which is not having the parallel
plan(or function is executed directly).

I think if the user is explicitly marking a function as parallel-safe,
then it doesn't make much sense to allow parallel query in such
functions as it won't be feasible for the planner (or at least it will
be quite expensive) to detect the same. By the way, if the user has
any such expectation from a function, then he can mark the function as
parallel-restricted or parallel-unsafe.

However, if a query is parallel-safe, it might not end up getting run
in parallel. In that case, it could still benefit from parallelism
internally. I think we want to allow that. For example, suppose you
run a query like:

SELECT x, sum(somewhat_expensive_function(y)) FROM sometab GROUP BY 1;

If sometab isn't very big, it's probably better to use a non-parallel
plan for this query, because then somewhat_expensive_function() can
still use parallelism internally, which might be better. However, if
sometab is large enough, then it might be better to parallelize the
whole query using a Partial/FinalizeAggregate and force each call to
somewhat_expensive_function() to run serially.

Is there any easy way to find out which way is less expensive? Even
if we find some way or just make a rule that when an outer query uses
parallelism, then force function call to run serially, how do we
achieve that? I mean in each worker we can ensure that each
individual statements from a function can run serially (by having a
check of isparallelworker() in gather node), but having a similar
check in the master backend is tricky or maybe we don't want to care
for the same in master backend. Do you have any suggestions on how to
make it work?

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#15Robert Haas
robertmhaas@gmail.com
In reply to: Amit Kapila (#14)
Re: Enabling parallelism for queries coming from SQL or other PL functions

On Mon, Feb 27, 2017 at 8:33 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:

Is there any easy way to find out which way is less expensive?

No. But that's a separate problem. I'm just saying we shouldn't
arbitrarily prohibit parallelism for parallel-safe functions.

Even
if we find some way or just make a rule that when an outer query uses
parallelism, then force function call to run serially, how do we
achieve that I mean in each worker we can ensure that each
individual statements from a function can run serially (by having a
check of isparallelworker() in gather node), but having a similar
check in the master backend is tricky or maybe we don't want to care
for the same in master backend. Do you have any suggestions on how to
make it work?

I don't understand what's wrong with the existing logic in standard_planner().

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#16Amit Kapila
amit.kapila16@gmail.com
In reply to: Robert Haas (#15)
Re: Enabling parallelism for queries coming from SQL or other PL functions

On Mon, Feb 27, 2017 at 12:21 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Mon, Feb 27, 2017 at 8:33 AM, Amit Kapila <amit.kapila16@gmail.com> wrote:

Is there any easy way to find out which way is less expensive?

No. But that's a separate problem. I'm just saying we shouldn't
arbitrarily prohibit parallelism for parallel-safe functions.

Even
if we find some way or just make a rule that when an outer query uses
parallelism, then force function call to run serially, how do we
achieve that I mean in each worker we can ensure that each
individual statements from a function can run serially (by having a
check of isparallelworker() in gather node), but having a similar
check in the master backend is tricky or maybe we don't want to care
for the same in master backend. Do you have any suggestions on how to
make it work?

I don't understand what's wrong with the existing logic in standard_planner().

When such a function (that contains statements which have parallel
plans) is being executed as part of another parallel plan, it can
allow spawning workers unboundedly. Assume a query like select *
from t1 where c1 < func1(), this can use parallel scan for t1 and
then in master backend, during partial scan of t1, it can again spawn
new set of workers for queries inside func1(), this can happen
multiple times if parallel query inside func1() again calls some other
function func2() which has parallel query. Now, this might be okay,
but today such a situation doesn't exist that Gather execution can
invoke another Gather node, so it is worth to consider if we want to
allow it.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#17Robert Haas
robertmhaas@gmail.com
In reply to: Amit Kapila (#16)
Re: Enabling parallelism for queries coming from SQL or other PL functions

On Tue, Feb 28, 2017 at 5:25 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:

When such a function (that contains statements which have parallel
plans) is being executed as part of another parallel plan, it can
allow spawning workers unboundedly. Assume a query like select *
from t1 where c1 < func1(), this can use parallel scan for t1 and
then in master backend, during partial scan of t1, it can again spawn
new set of workers for queries inside func1(), this can happen
multiple times if parallel query inside func1() again calls some other
function func2() which has parallel query. Now, this might be okay,
but today such a situation doesn't exist that Gather execution can
invoke another Gather node, so it is worth to consider if we want to
allow it.

If we want to prohibit that, the check in standard_planner can be
changed from !IsParallelWorker() to !IsInParallelMode(), but I'm not
100% sure whether that's an improvement or not. I would be inclined
to leave it alone unless we get several votes to change it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#18Amit Kapila
amit.kapila16@gmail.com
In reply to: Robert Haas (#17)
Re: Enabling parallelism for queries coming from SQL or other PL functions

On Thu, Mar 2, 2017 at 3:50 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Tue, Feb 28, 2017 at 5:25 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:

When such a function (that contains statements which have parallel
plans) is being executed as part of another parallel plan, it can
allow spawning workers unboundedly. Assume a query like select *
from t1 where c1 < func1(), this can use parallel scan for t1 and
then in master backend, during partial scan of t1, it can again spawn
new set of workers for queries inside func1(), this can happen
multiple times if parallel query inside func1() again calls some other
function func2() which has parallel query. Now, this might be okay,
but today such a situation doesn't exist that Gather execution can
invoke another Gather node, so it is worth to consider if we want to
allow it.

If we want to prohibit that, the check in standard_planner can be
changed from !IsParallelWorker() to !IsInParallelMode(), but I'm not
100% sure whether that's an improvement or not.

I am not sure how you can achieve that by just changing
standard_planner() code, because the plans of statements inside pl can
be cached in which case it will not try to regenerate the plan.

I would be inclined
to leave it alone unless we get several votes to change it.

Okay, not a problem.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#19Robert Haas
robertmhaas@gmail.com
In reply to: Amit Kapila (#18)
Re: Enabling parallelism for queries coming from SQL or other PL functions

On Thu, Mar 2, 2017 at 3:54 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:

On Thu, Mar 2, 2017 at 3:50 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Tue, Feb 28, 2017 at 5:25 PM, Amit Kapila <amit.kapila16@gmail.com> wrote:

When such a function (that contains statements which have parallel
plans) is being executed as part of another parallel plan, it can
allow spawning workers unboundedly. Assume a query like select *
from t1 where c1 < func1(), this can use parallel scan for t1 and
then in master backend, during partial scan of t1, it can again spawn
new set of workers for queries inside func1(), this can happen
multiple times if parallel query inside func1() again calls some other
function func2() which has parallel query. Now, this might be okay,
but today such a situation doesn't exist that Gather execution can
invoke another Gather node, so it is worth to consider if we want to
allow it.

If we want to prohibit that, the check in standard_planner can be
changed from !IsParallelWorker() to !IsInParallelMode(), but I'm not
100% sure whether that's an improvement or not.

I am not sure how you can achieve that by just changing
standard_planner() code, because the plans of statements inside pl can
be cached in which case it will not try to regenerate the plan.

Oh, good point.

I would be inclined
to leave it alone unless we get several votes to change it.

Okay, not a problem.

Cool.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

#20Rafia Sabih
rafia.pghackers@gmail.com
In reply to: Robert Haas (#13)
Re: Enabling parallelism for queries coming from SQL or other PL functions

On Sun, Feb 26, 2017 at 7:09 PM, Robert Haas <robertmhaas@gmail.com> wrote:

I think I see the problem that you're trying to solve, but I agree
that this doesn't seem all that elegant. The reason why we have that
numberTuples check is because we're afraid that we might be in a
context like the extended-query protocol, where the caller can ask for
1 tuple, and then later ask for another tuple. That won't work,
because once we shut down the workers we can't reliably generate the
rest of the query results. However, I think it would probably work
fine to let somebody ask for less than the full number of tuples if
it's certain that they won't later ask for any more.

So maybe what we ought to do is allow CURSOR_OPT_PARALLEL_OK to be set
any time we know that ExecutorRun() will be called for the QueryDesc
at most once rather than (as at present) only where we know it will be
executed only once with a tuple-count of zero. Then we could change
things in ExecutePlan so that it doesn't disable parallel query when
the tuple-count is non-zero, but does take an extra argument "bool
execute_only_once", and it disables parallel execution if that is not
true. Also, if ExecutorRun() is called a second time for the same
QueryDesc when execute_only_once is specified as true, it should
elog(ERROR, ...). Then exec_execute_message(), for example, can pass
that argument as false when the tuple-count is non-zero, but other
places that are going to fetch a limited number of rows could pass it
as true even though they also pass a row-count.

I'm not sure if that's exactly right, but something along those lines
seems like it should work.

IIUC, this needs an additional bool execute_once in the queryDesc which is
set to true in standard_ExecutorRun when the query is detected to be coming
from PL function or provided count is zero i.e. execute till the end, in
case execute_once is already true then report the error.

I think that a final patch for this functionality should involve
adding CURSOR_OPT_PARALLEL_OK to appropriate places in each PL, plus
maybe some infrastructure changes like the ones mentioned above.
Maybe it can be divided into two patches, one to make the
infrastructure changes and a second to add CURSOR_OPT_PARALLEL_OK to
more places.

I have split the patch into two, one is to allow optimiser to select a
parallel plan for queries in PL functions
(pl_parallel_opt_support_v1.patch), wherein CURSOR_OPT_PARALLEL_OK is
passed at required places.

Next, the patch for allowing execution of such queries in parallel mode,
that involves infrastructural changes along the lines mentioned upthread
(pl_parallel_exec_support_v1.patch).

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

Attachments:

pl_parallel_exec_support_v1.patchapplication/octet-stream; name=pl_parallel_exec_support_v1.patchDownload+27-6
pl_parallel_opt_support_v1.patchapplication/octet-stream; name=pl_parallel_opt_support_v1.patchDownload+16-6
#21Robert Haas
robertmhaas@gmail.com
In reply to: Rafia Sabih (#20)
#22Rafia Sabih
rafia.pghackers@gmail.com
In reply to: Robert Haas (#21)
#23Rafia Sabih
rafia.pghackers@gmail.com
In reply to: Robert Haas (#21)
#24Dilip Kumar
dilipbalaut@gmail.com
In reply to: Rafia Sabih (#23)
#25Rafia Sabih
rafia.pghackers@gmail.com
In reply to: Dilip Kumar (#24)
#26Robert Haas
robertmhaas@gmail.com
In reply to: Rafia Sabih (#22)
#27Rafia Sabih
rafia.pghackers@gmail.com
In reply to: Robert Haas (#26)
#28Dilip Kumar
dilipbalaut@gmail.com
In reply to: Rafia Sabih (#27)
#29Robert Haas
robertmhaas@gmail.com
In reply to: Dilip Kumar (#28)
#30Rafia Sabih
rafia.pghackers@gmail.com
In reply to: Robert Haas (#29)
#31Robert Haas
robertmhaas@gmail.com
In reply to: Rafia Sabih (#30)
#32Dilip Kumar
dilipbalaut@gmail.com
In reply to: Robert Haas (#31)
#33Rafia Sabih
rafia.pghackers@gmail.com
In reply to: Dilip Kumar (#32)
#34Robert Haas
robertmhaas@gmail.com
In reply to: Rafia Sabih (#33)
#35Rafia Sabih
rafia.pghackers@gmail.com
In reply to: Robert Haas (#34)
#36Robert Haas
robertmhaas@gmail.com
In reply to: Rafia Sabih (#35)