Re: parallelize queries containing subplans

On Wed, Dec 28, 2016 at 11:47 AM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> Now, we can further extend this to parallelize queries containing
> correlated subplans like below:
>
> explain select * from t1 where t1.i in (select t2.i from t2 where t2.i=t1.i);
> QUERY PLAN
> -------------------------------------------------------------
> Seq Scan on t1 (cost=0.00..831049.09 rows=8395 width=12)
> Filter: (SubPlan 1)
> SubPlan 1
> -> Seq Scan on t2 (cost=0.00..97.73 rows=493 width=4)
> Filter: (i = t1.i)
> (5 rows)
>
> In the above query, Filter on t2 (i = t1.i) generates Param node which
> is a parallel-restricted node, so such queries won't be able to use
> parallelism even with the patch. I think we can mark Params which
> refer to same level as parallel-safe and I think we have this
> information (node-> varlevelsup/ phlevelsup/ agglevelsup) available
> when we replace correlation vars (SS_replace_correlation_vars).
>

I have implemented the above idea which will allow same or immediate
outer level PARAMS as parallel_safe. The results of above query after
patch:

postgres=# explain select * from t1 where t1.i in (select t2.i from t2
where t2.i=t1.i);
QUERY PLAN
--------------------------------------------------------------------------
Gather (cost=0.00..488889.88 rows=8395 width=12)
Workers Planned: 1
-> Parallel Seq Scan on t1 (cost=0.00..488889.88 rows=4938 width=12)
Filter: (SubPlan 1)
SubPlan 1
-> Seq Scan on t2 (cost=0.00..97.73 rows=493 width=4)
Filter: (i = t1.i)
(7 rows)

Note - This patch can be applied on top of
pq_pushdown_subplan_v1.patch posted upthread [1]

[1] - https://www.postgresql.org/message-id/CAA4eK1%2Be8Z45D2n%2BrnDMDYsVEb5iW7jqaCH_tvPMYau%3D1Rru9w%40mail.gmail.com

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