As discussed in a nearby thread [1] (parallelize queries containing
initplans), it appears that there are cases where queries referring
PARAM_EXTERN params are treated as parallel-restricted even though
they should be parallel-safe. I have done some further investigation
and found that actually for most of the cases they are treated as
parallel-restricted (due to tests in max_parallel_hazard_walker). The
cases where such queries are treated as parallel-safe are when
eval_const_expressions_mutator converts the param to const. So
whenever we select the generic plan, it won't work. One simple
example is:
create table t1(c1 int, c2 char(500));
insert into t1 values(generate_series(1,10000),'aaa');
analyze t1;
set force_parallel_mode = on;
regression=# prepare t1_select(int) as Select c1 from t1 where c1 < $1;
PREPARE
regression=# explain (costs off, analyze) execute t1_select(10000);
QUERY PLAN
-------------------------------------------------------------------
Gather (actual time=35.252..44.727 rows=9999 loops=1)
Workers Planned: 1
Workers Launched: 1
Single Copy: true
-> Seq Scan on t1 (actual time=0.364..5.638 rows=9999 loops=1)
Filter: (c1 < 10000)
Rows Removed by Filter: 1
Planning time: 2135.514 ms
Execution time: 52.886 ms
(9 rows)
The next four executions will give the same plan, however, the sixth
execution will give below plan:
regression=# explain (costs off, analyze) execute t1_select(10005);
QUERY PLAN
--------------------------------------------------------------
Seq Scan on t1 (actual time=0.049..6.188 rows=10000 loops=1)
Filter: (c1 < $1)
Planning time: 22577.404 ms
Execution time: 7.612 ms
(4 rows)
Attached patch fix_parallel_safety_for_extern_params_v1.patch fixes
this problem. Note, for now, I have added a very simple test in
regression tests to cover prepared statement case, but we might want
to add something related to generic plan selection as I have shown
above. I am just not sure if it is a good idea to have multiple
executions just to get the generic plan.
After fixing this problem, when I ran the regression tests with
force_parallel_mode = regress, I saw multiple other failures. All the
failures boil down to two kinds of cases:
1. There was an assumption while extracting simple expressions that
the target list of gather node can contain constants or Var's. Now,
once the above patch allows extern params as parallel-safe, that
assumption no longer holds true. We need to handle params as well.
Attached patch fix_simple_expr_interaction_gather_v1.patch handles
that case.
2. We don't allow execution to use parallelism if the plan can be
executed multiple times. This has been enforced in ExecutePlan, but
it seems like that we miss to handle the case where we are already in
parallel mode by the time we enforce that condition. So, what
happens, as a result, is that it will allow to use parallelism when it
shouldn't (when the same plan is executed multiple times) and lead to
a crash. One way to fix is that we temporarily exit the parallel mode
in such cases and reenter in the same state once the current execution
is over. Attached patch fix_parallel_mode_nested_execution_v1.patch
fixes this problem.
Thanks to Kuntal who has helped in investigating the regression
failures which happened as a result of making param extern params as
parallel-safe.
[1] - https://www.postgresql.org/message-id/CA%2BTgmobSN66o2_c76rY12JvS_sZa17zpKvpuyG-QzRvVLYE8Vg%40mail.gmail.com
--
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