Re: [Postgres-xc-developers] FQS for queries with subquery

[Ashutosh B. <ash...@en...>]

On Mon, Mar 25, 2013 at 1:54 PM, Amit Khandekar <
ami...@en...> wrote:

> Hi Ashutosh,
>
> I only have following few points:
>
> -------------
>  trailing whitespace characters in the patch.
>
>
Will take care of that.


> --------------
>
> Re: "Subqueries and permission checks",  Just to make sure I understand
> the scope of the patch ...
> The statement "In very near future, we should be able to use the
> infrastructure for FQS for shipping sub-queries without planning at the
> coordinator, the whole query is not shippable" , can you please elaborate
> on this point ? Is it that individual subqueries are not fast-shipped in
> some cases if the whole query is not shippable ? For e.g. :
>
> create table t2 (c1 int, c2 int, c3 int) distribute by hash(c2);
> create table t1 (c1 int, c2 int, c3 int) distribute by replication;
>
> explain select * from (select avg(c1), c2 from t2 group by c2) a1 join
> (select avg(c1), c2 from t1 group by c2) b1 using
> (c2);                                 QUERY
> PLAN
>
> ----------------------------------------------------------------------------
>  Data Node Scan on "__REMOTE_FQS_QUERY__"  (cost=0.00..0.00 rows=0 width=0)
>    Node/s: datanode_1, datanode_2
>
> Now if I disable FQS, the join does not get reduced. That means, if the
> above query is used as a subquery in a FROM clause of an outer query, the
> subquery won't get pushed ? I guess this is expected, because as you said
> "Subqueries are not shipped if the whole query is not-shippable". But not
> sure.
>
>
I think I mis-constructed that sentence. The idea is this: while walking
the query before planning to check whether it can FQSed or not, we mark
each subquery in that query as un/shippable irrespective of the parent
query. If the whole query is not shippable, we will use this information to
ship the subquery without planning in subquery_planner(). In the example
that you have given, as of now, we plan both the sides of subquery in
subquery planner. But if we see that they are shippable in
subquery_planner() we can ship them as they are without planning, and then
reduce the join if it's reducible. This is not in the scope of current work.


>
> ------------
>
> There is a comment below in xc_FQS_join.sql . I guess we need to correct
> the comment now.
>
> -- JOIN between two distributed relations is not shippable as of now,
> because we
> -- don't store the distribution information completely while deducing
> nodes in
> -- the planner. If we come to that some time in the future, we would see
> change
> -- in the plan below
>
>
>
Ok


> ------------
>
> Can you please correct some typos in the comment :
>
>  * pgxc_replace_dist_vars_subquery
>  * The function looks up the *member so ExecNodes::en_dist*_var in the
>  * query->targetList. If found, they are re-stamped with the given varno
> and
>  * resno of the TargetEntry found and added to the new distribution var
> list
>  * being created. This function is useful to re-stamp the distribution
> columns
>  * of a subquery.
>
> ------------------
>
>
It should be "members of" right?


> Other than this, I have no more issues, and you can go ahead with the
> commit.
>
>
> Should I commit the patch after taking care of these comments or you want
to have a second glance?

> Thanks
> -Amit
>
>
>
> On 26 February 2013 18:07, Ashutosh Bapat <ash...@en...
> > wrote:
>
>> Hi All,
>> PFA the patch for using FQS for queries with subqueries. Subqueries
>> appear in the query as sublinks or as relations in From clause. The
>> sublinks are in-general shippable if all the relations involved are
>> replicated. I haven't got a rule to handle subqueries where distributed
>> tables are involved. For example, looking at the datanodes where individual
>> sublinks in query be below, should be shipped, I can not come up with a
>> rule to merge them so as to find a set of datanodes where the query can be
>> shipped.
>>
>> select (select tab1.val from tab1 where tab1.val = tab2.val2), tab2.val2
>> from tab2 where tab2.val = (select sum(val2) from tab1 group by val)
>>
>> In fact, you will notice that the above query is unshippable by FQS.
>>
>> This patch deals with the subqueries in the From clause of the query. The
>> idea is every query result has a set of datanodes where the result can be
>> computed and the distribution columns (sometime computed ones) on which the
>> distribution of the result depends. With this information the existing
>> rules for shippability of join are applicable. This patch includes fixes
>> discussed in threads with subjects "Subqueries and permission checks" and
>> "Annotating ExecNodes with distribution column information".
>>
>> Regression shows no new failures.
>> --
>> Best Wishes,
>> Ashutosh Bapat
>> EntepriseDB Corporation
>> The Enterprise Postgres Company
>>
>>
>> ------------------------------------------------------------------------------
>> Everyone hates slow websites. So do we.
>> Make your web apps faster with AppDynamics
>> Download AppDynamics Lite for free today:
>> http://p.sf.net/sfu/appdyn_d2d_feb
>>
>> _______________________________________________
>> Postgres-xc-developers mailing list
>> Pos...@li...
>> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>>
>>
>


-- 
Best Wishes,
Ashutosh Bapat
EntepriseDB Corporation
The Enterprise Postgres Company