On Thu, Jan 19, 2017 at 3:05 PM, Dilip Kumar <dilipbalaut@gmail.com> wrote:
> @@ -1213,6 +1216,7 @@ SS_process_ctes(PlannerInfo *root)
> &splan->firstColCollation);
> splan->useHashTable = false;
> splan->unknownEqFalse = false;
> + splan->parallel_safe = best_path->parallel_safe;
>
> I noticed that if path for CTE is parallel safe then we are marking
> CTE subplan as parallel safe, In particular, I don't have any problem
> with that, but can you add some test case which can cover this path, I
> mean to say where CTE subplan are pushed.
>
> ------------
> I have tried to test the subplan with CTE below is my test.
> create table t1(a int , b varchar);
> create table t (n int, b varchar);
>
> Query:
> explain verbose select * from t where t.n not in (WITH RECURSIVE t(n) AS (
> VALUES (1)
> UNION ALL
> SELECT a+1 FROM t1 WHERE a < 100
> )
> SELECT sum(n) FROM t);
>
> During debugging I found that subplan created for below part of the
> query is parallel_unsafe, Is it a problem or there is some explanation
> of why it's not parallel_safe,
>
> (WITH RECURSIVE t(n) AS (
> VALUES (1)
> UNION ALL
> SELECT a+1 FROM t1 WHERE a < 100
> )
> SELECT sum(n) FROM t);
> ----------
The corresponding plan for the query you have specified is:
QUERY PLAN
----------------------------------------------------------------------------------Seq Scan on public.t
(cost=40.73..20894.74rows=500480 width=35) Output: t.n, t.b Filter: (NOT (hashed SubPlan 2)) SubPlan 2 ->
Aggregate (cost=40.72..40.73 rows=1 width=8) Output: sum(t_1.n) CTE t -> Append
(cost=0.00..31.18rows=424 width=4) -> Result (cost=0.00..0.01 rows=1 width=4)
Output:1 -> Seq Scan on public.t1 (cost=0.00..26.93
rows=423 width=4) Output: (t1.a + 1) Filter: (t1.a < 100) ->
CTEScan on t t_1 (cost=0.00..8.48 rows=424 width=4) Output: t_1.n
(15 rows)
Now, the plan for CTE is parallel_safe. But, a CTE plan is converted
to an InitPlan and returns a Param which is used in the CTE Scan.
Since Param is not parallel_safe till now, the SubPlan is also not
parallel_safe. This is why CTE subplans will not be pushed under
Gather.
--
Thanks & Regards,
Kuntal Ghosh
EnterpriseDB: http://www.enterprisedb.com