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

[Amit K. <ami...@en...>]

On 25 March 2013 17:11, Ashutosh Bapat <ash...@en...>wrote:

>
>
> 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.
>

Ok.


>
>
>>
>> ------------
>>
>> 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?
>

That's what I guessed too.


>
>
>> 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?
>

Nope, second glance not needed. go ahead and commit.


>  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
>