postgres-xc-developers

[Postgres-xc-developers] shipping outer joins

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

Hi All,
Currently, we do not ship OUTER join to the datanodes when both the sides
of the join are not replicated. But there are cases, where shipping OUTER
join between distributed/replicated tables should be possible. Those cases
are as stated below. For every case, find a justification or proof of
correctness for the said shippability.


1. An equi-outer-join between the distribution columns of two distributed
tables such that the tables are distributed on same set of nodes and
distribution strategy is same and datatype of distribution column is same,
is shippable to the datanodes where the distributed tables are distributed.
justification for the claim
---------------------------------
Any outer join A OJ B between relations A and B is defined as A IJ B (term
I) + rows from A which are not part of I (term II), with NULL values for
columns of B + rows of B which are not part of I (term III), with NULL
values for columns of A. A IJ B is inner join between A and B. I will
prefix n to OJ, IJ, or terms I, II, III to mean the operations executed on
nth node.

In such case, rows with same value for distribution column reside on the
same datanode for both the tables. Hence, a given row on a given node from
either table can not join with a row of other table from any node other
than where it resides (referred as (a)). Hence if we collect A IJn B across
all the nodes, it produces A IJ B. Because of (a), a row which is part of
IIn and IIIn, will also be part of II and III resp. Since a row resides
only on a single node, a row r1 which is part of IIn can not be part of IIm
(n != m). Similarly for any row in IIIn. Thus if we collect IIn and IIIn
across all nodes, it produces II and III respectively. Now In + IIn + IIIn
is nothing but A OJn B. Hence if we collect A OJn B from all the nodes, it
produces A OJ B. Hence the above result.

2. An equi-outer-join between a distributed and a replicated relation is
shippable to the datanodes where distributed relation is distributed if the
replicated relation is replicated on those nodes and the outer side of join
is distributed relation.

justification
---------------
Any left outer join A LOJ B between relations A and B is defined as A IJ B
(term I) + rows from A which are not part of I (term II). A IJ B is inner
join between A and B. I will prefix n (or m) to LOJ, IJ, or terms I, II to
mean the operations executed on nth (or mth) node. In this case, A is
distributed and B is replicated.

Since all the rows of B are available on every node where A is distributed,
join of a given row of A on a given node with rows of B can be evaluated on
that node (a). Since a given row of A exists only on a single node (b), A
IJn B intersection A IJm B (n != m) should be NULL. Thus if we collect A
IJn B from all nodes, it will produce A IJ B. Because of (a) and (b), a row
of A on a given node n, which is not part of A IJn B, will be part of IIn
as well as II and can not be part of IIm (m != n). Thus if we collect all
IIn, it would produce II. Since A IJn B + IIn is A LOJn B, we can collect A
LOJn B from all the nodes to produce A LOJ B. Hence the claim above.

Does anybody see holes in those arguments?
-- 
Best Wishes,
Ashutosh Bapat
EntepriseDB Corporation
The Enterprise Postgres Company

Re: [Postgres-xc-developers] shipping outer joins

[Koichi S. <koi...@gm...>]

Yes, it's collect.  In fact, in the case 2, we can ship outer join
even though outer side of join is replicated but we need new
infrastructure to calculate fragment of replicated table using the
join key and other table's distribution method.    I think it's a
reasonable approach to implement case 1 and case 2 first.

Regards;
----------
Koichi Suzuki


2013/1/29 Ashutosh Bapat <ash...@en...>:
> Hi All,
> Currently, we do not ship OUTER join to the datanodes when both the sides of
> the join are not replicated. But there are cases, where shipping OUTER join
> between distributed/replicated tables should be possible. Those cases are as
> stated below. For every case, find a justification or proof of correctness
> for the said shippability.
>
>
> 1. An equi-outer-join between the distribution columns of two distributed
> tables such that the tables are distributed on same set of nodes and
> distribution strategy is same and datatype of distribution column is same,
> is shippable to the datanodes where the distributed tables are distributed.
> justification for the claim
> ---------------------------------
> Any outer join A OJ B between relations A and B is defined as A IJ B (term
> I) + rows from A which are not part of I (term II), with NULL values for
> columns of B + rows of B which are not part of I (term III), with NULL
> values for columns of A. A IJ B is inner join between A and B. I will prefix
> n to OJ, IJ, or terms I, II, III to mean the operations executed on nth
> node.
>
> In such case, rows with same value for distribution column reside on the
> same datanode for both the tables. Hence, a given row on a given node from
> either table can not join with a row of other table from any node other than
> where it resides (referred as (a)). Hence if we collect A IJn B across all
> the nodes, it produces A IJ B. Because of (a), a row which is part of IIn
> and IIIn, will also be part of II and III resp. Since a row resides only on
> a single node, a row r1 which is part of IIn can not be part of IIm (n !=
> m). Similarly for any row in IIIn. Thus if we collect IIn and IIIn across
> all nodes, it produces II and III respectively. Now In + IIn + IIIn is
> nothing but A OJn B. Hence if we collect A OJn B from all the nodes, it
> produces A OJ B. Hence the above result.
>
> 2. An equi-outer-join between a distributed and a replicated relation is
> shippable to the datanodes where distributed relation is distributed if the
> replicated relation is replicated on those nodes and the outer side of join
> is distributed relation.
>
> justification
> ---------------
> Any left outer join A LOJ B between relations A and B is defined as A IJ B
> (term I) + rows from A which are not part of I (term II). A IJ B is inner
> join between A and B. I will prefix n (or m) to LOJ, IJ, or terms I, II to
> mean the operations executed on nth (or mth) node. In this case, A is
> distributed and B is replicated.
>
> Since all the rows of B are available on every node where A is distributed,
> join of a given row of A on a given node with rows of B can be evaluated on
> that node (a). Since a given row of A exists only on a single node (b), A
> IJn B intersection A IJm B (n != m) should be NULL. Thus if we collect A IJn
> B from all nodes, it will produce A IJ B. Because of (a) and (b), a row of A
> on a given node n, which is not part of A IJn B, will be part of IIn as well
> as II and can not be part of IIm (m != n). Thus if we collect all IIn, it
> would produce II. Since A IJn B + IIn is A LOJn B, we can collect A LOJn B
> from all the nodes to produce A LOJ B. Hence the claim above.
>
> Does anybody see holes in those arguments?
> --
> Best Wishes,
> Ashutosh Bapat
> EntepriseDB Corporation
> The Enterprise Postgres Company
>
> ------------------------------------------------------------------------------
> Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS,
> MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
> with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
> MVPs and experts. ON SALE this month only -- learn more at:
> http://p.sf.net/sfu/learnnow-d2d
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>

Re: [Postgres-xc-developers] shipping outer joins

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

Hi All,
There is a small improvement possible in case 2. We don't need to
necessarily restrict the case only to equi-joins, justification for the
same remains un-altered. The reason we need equi-join in case 1, is because
other join conditions can not be evaluated locally when both the relations
are distributed.

Again anybody sees any loop-hole in the argument?

On Tue, Jan 29, 2013 at 4:04 PM, Ashutosh Bapat <
ash...@en...> wrote:

> Hi All,
> Currently, we do not ship OUTER join to the datanodes when both the sides
> of the join are not replicated. But there are cases, where shipping OUTER
> join between distributed/replicated tables should be possible. Those cases
> are as stated below. For every case, find a justification or proof of
> correctness for the said shippability.
>
>
> 1. An equi-outer-join between the distribution columns of two distributed
> tables such that the tables are distributed on same set of nodes and
> distribution strategy is same and datatype of distribution column is same,
> is shippable to the datanodes where the distributed tables are distributed.
> justification for the claim
> ---------------------------------
> Any outer join A OJ B between relations A and B is defined as A IJ B (term
> I) + rows from A which are not part of I (term II), with NULL values for
> columns of B + rows of B which are not part of I (term III), with NULL
> values for columns of A. A IJ B is inner join between A and B. I will
> prefix n to OJ, IJ, or terms I, II, III to mean the operations executed on
> nth node.
>
> In such case, rows with same value for distribution column reside on the
> same datanode for both the tables. Hence, a given row on a given node from
> either table can not join with a row of other table from any node other
> than where it resides (referred as (a)). Hence if we collect A IJn B across
> all the nodes, it produces A IJ B. Because of (a), a row which is part of
> IIn and IIIn, will also be part of II and III resp. Since a row resides
> only on a single node, a row r1 which is part of IIn can not be part of IIm
> (n != m). Similarly for any row in IIIn. Thus if we collect IIn and IIIn
> across all nodes, it produces II and III respectively. Now In + IIn + IIIn
> is nothing but A OJn B. Hence if we collect A OJn B from all the nodes, it
> produces A OJ B. Hence the above result.
>
> 2. An equi-outer-join between a distributed and a replicated relation is
> shippable to the datanodes where distributed relation is distributed if the
> replicated relation is replicated on those nodes and the outer side of join
> is distributed relation.
>
> justification
> ---------------
> Any left outer join A LOJ B between relations A and B is defined as A IJ B
> (term I) + rows from A which are not part of I (term II). A IJ B is inner
> join between A and B. I will prefix n (or m) to LOJ, IJ, or terms I, II to
> mean the operations executed on nth (or mth) node. In this case, A is
> distributed and B is replicated.
>
> Since all the rows of B are available on every node where A is
> distributed, join of a given row of A on a given node with rows of B can be
> evaluated on that node (a). Since a given row of A exists only on a single
> node (b), A IJn B intersection A IJm B (n != m) should be NULL. Thus if we
> collect A IJn B from all nodes, it will produce A IJ B. Because of (a) and
> (b), a row of A on a given node n, which is not part of A IJn B, will be
> part of IIn as well as II and can not be part of IIm (m != n). Thus if we
> collect all IIn, it would produce II. Since A IJn B + IIn is A LOJn B, we
> can collect A LOJn B from all the nodes to produce A LOJ B. Hence the claim
> above.
>
> Does anybody see holes in those arguments?
> --
> Best Wishes,
> Ashutosh Bapat
> EntepriseDB Corporation
> The Enterprise Postgres Company
>



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

Re: [Postgres-xc-developers] shipping outer joins

[Koichi S. <koi...@gm...>]

Yes, I understand the case and if corresponding inner theta join is
shippable, then outer theta join is shippable too.

I think this applies generally.

Any other thoughts?
----------
Koichi Suzuki


2013/2/5 Ashutosh Bapat <ash...@en...>:
> Hi All,
> There is a small improvement possible in case 2. We don't need to
> necessarily restrict the case only to equi-joins, justification for the same
> remains un-altered. The reason we need equi-join in case 1, is because other
> join conditions can not be evaluated locally when both the relations are
> distributed.
>
> Again anybody sees any loop-hole in the argument?
>
>
> On Tue, Jan 29, 2013 at 4:04 PM, Ashutosh Bapat
> <ash...@en...> wrote:
>>
>> Hi All,
>> Currently, we do not ship OUTER join to the datanodes when both the sides
>> of the join are not replicated. But there are cases, where shipping OUTER
>> join between distributed/replicated tables should be possible. Those cases
>> are as stated below. For every case, find a justification or proof of
>> correctness for the said shippability.
>>
>>
>> 1. An equi-outer-join between the distribution columns of two distributed
>> tables such that the tables are distributed on same set of nodes and
>> distribution strategy is same and datatype of distribution column is same,
>> is shippable to the datanodes where the distributed tables are distributed.
>> justification for the claim
>> ---------------------------------
>> Any outer join A OJ B between relations A and B is defined as A IJ B (term
>> I) + rows from A which are not part of I (term II), with NULL values for
>> columns of B + rows of B which are not part of I (term III), with NULL
>> values for columns of A. A IJ B is inner join between A and B. I will prefix
>> n to OJ, IJ, or terms I, II, III to mean the operations executed on nth
>> node.
>>
>> In such case, rows with same value for distribution column reside on the
>> same datanode for both the tables. Hence, a given row on a given node from
>> either table can not join with a row of other table from any node other than
>> where it resides (referred as (a)). Hence if we collect A IJn B across all
>> the nodes, it produces A IJ B. Because of (a), a row which is part of IIn
>> and IIIn, will also be part of II and III resp. Since a row resides only on
>> a single node, a row r1 which is part of IIn can not be part of IIm (n !=
>> m). Similarly for any row in IIIn. Thus if we collect IIn and IIIn across
>> all nodes, it produces II and III respectively. Now In + IIn + IIIn is
>> nothing but A OJn B. Hence if we collect A OJn B from all the nodes, it
>> produces A OJ B. Hence the above result.
>>
>> 2. An equi-outer-join between a distributed and a replicated relation is
>> shippable to the datanodes where distributed relation is distributed if the
>> replicated relation is replicated on those nodes and the outer side of join
>> is distributed relation.
>>
>> justification
>> ---------------
>> Any left outer join A LOJ B between relations A and B is defined as A IJ B
>> (term I) + rows from A which are not part of I (term II). A IJ B is inner
>> join between A and B. I will prefix n (or m) to LOJ, IJ, or terms I, II to
>> mean the operations executed on nth (or mth) node. In this case, A is
>> distributed and B is replicated.
>>
>> Since all the rows of B are available on every node where A is
>> distributed, join of a given row of A on a given node with rows of B can be
>> evaluated on that node (a). Since a given row of A exists only on a single
>> node (b), A IJn B intersection A IJm B (n != m) should be NULL. Thus if we
>> collect A IJn B from all nodes, it will produce A IJ B. Because of (a) and
>> (b), a row of A on a given node n, which is not part of A IJn B, will be
>> part of IIn as well as II and can not be part of IIm (m != n). Thus if we
>> collect all IIn, it would produce II. Since A IJn B + IIn is A LOJn B, we
>> can collect A LOJn B from all the nodes to produce A LOJ B. Hence the claim
>> above.
>>
>> Does anybody see holes in those arguments?
>> --
>> Best Wishes,
>> Ashutosh Bapat
>> EntepriseDB Corporation
>> The Enterprise Postgres Company
>
>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EntepriseDB Corporation
> The Enterprise Postgres Company
>
> ------------------------------------------------------------------------------
> Free Next-Gen Firewall Hardware Offer
> Buy your Sophos next-gen firewall before the end March 2013
> and get the hardware for free! Learn more.
> http://p.sf.net/sfu/sophos-d2d-feb
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>

Re: [Postgres-xc-developers] shipping outer joins

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

On 12 February 2013 14:40, Ashutosh Bapat
<ash...@en...> wrote:
> Hi All,
> PFA patch based on the ideas in this mail thread.
>
> There are two regression failures join and functional_deps. These are old
> bugs 3604199, 3604090 unrelated to the code in this patch.
>
>
> On Tue, Feb 5, 2013 at 1:48 PM, Ashutosh Bapat
> <ash...@en...> wrote:
>>
>> Hi All,
>> There is a small improvement possible in case 2. We don't need to
>> necessarily restrict the case only to equi-joins, justification for the same
>> remains un-altered. The reason we need equi-join in case 1, is because other
>> join conditions can not be evaluated locally when both the relations are
>> distributed.
>>
>> Again anybody sees any loop-hole in the argument?
>>
>>
>> On Tue, Jan 29, 2013 at 4:04 PM, Ashutosh Bapat
>> <ash...@en...> wrote:
>>>
>>> Hi All,
>>> Currently, we do not ship OUTER join to the datanodes when both the sides
>>> of the join are not replicated. But there are cases, where shipping OUTER
>>> join between distributed/replicated tables should be possible. Those cases
>>> are as stated below. For every case, find a justification or proof of
>>> correctness for the said shippability.
>>>
>>>
>>> 1. An equi-outer-join between the distribution columns of two distributed
>>> tables such that the tables are distributed on same set of nodes and
>>> distribution strategy is same and datatype of distribution column is same,
>>> is shippable to the datanodes where the distributed tables are distributed.
>>> justification for the claim
>>> ---------------------------------
>>> Any outer join A OJ B between relations A and B is defined as A IJ B
>>> (term I) + rows from A which are not part of I (term II), with NULL values
>>> for columns of B + rows of B which are not part of I (term III), with NULL
>>> values for columns of A. A IJ B is inner join between A and B. I will prefix
>>> n to OJ, IJ, or terms I, II, III to mean the operations executed on nth
>>> node.
>>>
>>> In such case, rows with same value for distribution column reside on the
>>> same datanode for both the tables. Hence, a given row on a given node from
>>> either table can not join with a row of other table from any node other than
>>> where it resides (referred as (a)). Hence if we collect A IJn B across all
>>> the nodes, it produces A IJ B. Because of (a), a row which is part of IIn
>>> and IIIn, will also be part of II and III resp. Since a row resides only on
>>> a single node, a row r1 which is part of IIn can not be part of IIm (n !=
>>> m). Similarly for any row in IIIn. Thus if we collect IIn and IIIn across
>>> all nodes, it produces II and III respectively. Now In + IIn + IIIn is
>>> nothing but A OJn B. Hence if we collect A OJn B from all the nodes, it
>>> produces A OJ B. Hence the above result.
>>>
>>> 2. An equi-outer-join between a distributed and a replicated relation is
>>> shippable to the datanodes where distributed relation is distributed if the
>>> replicated relation is replicated on those nodes and the outer side of join
>>> is distributed relation.
>>>
>>> justification
>>> ---------------
>>> Any left outer join A LOJ B between relations A and B is defined as A IJ
>>> B (term I) + rows from A which are not part of I (term II). A IJ B is inner
>>> join between A and B. I will prefix n (or m) to LOJ, IJ, or terms I, II to
>>> mean the operations executed on nth (or mth) node. In this case, A is
>>> distributed and B is replicated.
>>>
>>> Since all the rows of B are available on every node where A is
>>> distributed, join of a given row of A on a given node with rows of B can be
>>> evaluated on that node (a). Since a given row of A exists only on a single
>>> node (b), A IJn B intersection A IJm B (n != m) should be NULL. Thus if we
>>> collect A IJn B from all nodes, it will produce A IJ B. Because of (a) and
>>> (b), a row of A on a given node n, which is not part of A IJn B, will be
>>> part of IIn as well as II and can not be part of IIm (m != n). Thus if we
>>> collect all IIn, it would produce II. Since A IJn B + IIn is A LOJn B, we
>>> can collect A LOJn B from all the nodes to produce A LOJ B. Hence the claim
>>> above.
>>>
>>> Does anybody see holes in those arguments?

Hi Ashutosh,

I think the logic of shippability of outer joins is flawless. Didn't
find any holes. Patch comments below :

-------

In case of distributed equi-join case, why is
IsExecNodesColumnDistributed() used instead of
IsExecNodesDistributedByValue() ? We want to always rule out the round
robin case, no ? I can see that pgxc_find_dist_equijoin_qual() will
always fail for round robin tables because they won't have any distrib
columns, but still , just curious ...

-------

		 * PGXC_TODO: What do we do when baselocatortype is
		 * LOCATOR_TYPE_DISTRIBUTED? It could be anything HASH distributed or
		 * MODULO distributed. In that case, having equi-join doesn't work
		 * really, because same value from different relation will go to
		 * different node.

The above comment says that it does not work if one of the tables is
distributed by hash and other table is distributed by modulo. But the
code is actually checking the baselocatortype also, so I guess it
works correctly after all ? I did not get what is the TODO here. Or
does it mean this ? :
For (t1_hash join t2_hash on ...) tj1 join (t1_mod join t2_mod on ...)
tj2 on tj1.col1 = tj2.col4
the merged nodes for tj1 will have  LOCATOR_TYPE_DISTRIBUTED, and the
merged nodes for tj2 will also be LOCATOR_TYPE_DISTRIBUTED, and so tj1
join tj2 would be wrongly marked shippable even though they should not
be shippable because of the mix of hash and modulo ?

-------

Is pgxc_is_expr_shippable(equi_join_expr) necessary ? Won't this qual
be examined in is_query_shippable() walker ?

--------

If both tables reside on a single datanode, every join case should be
shippable, which doesn't seem to be happening :
postgres=# create table tab2 (id2 int, v varchar) distribute by
replication to node (datanode_1);
postgres=# create table tab1 (id1 int, v varchar) to node (datanode_1);
postgres=# explain select * from (tab1 full outer join tab2 on id1 = id2 ) ;
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Hash Full Join  (cost=0.12..0.26 rows=10 width=72)
   Hash Cond: (tab1.id1 = tab2.id2)
   ->  Data Node Scan on tab1 "_REMOTE_TABLE_QUERY_"  (cost=0.00..0.00
rows=1000 width=36)
         Node/s: datanode_1
   ->  Hash  (cost=0.00..0.00 rows=1000 width=36)
         ->  Data Node Scan on tab2 "_REMOTE_TABLE_QUERY_"
(cost=0.00..0.00 rows=1000 width=36)
               Node/s: datanode_1

Probably you need to take out the following statement out of the
distributed case and apply it as a general rule:
		/* If there is only single node, try merging the nodes */
		if (list_length(inner_en->nodeList) == 1 &&
			list_length(outer_en->nodeList) == 1)
			merge_nodes = true;


>>> --
>>> Best Wishes,
>>> Ashutosh Bapat
>>> EntepriseDB Corporation
>>> The Enterprise Postgres Company
>>
>>
>>
>>
>> --
>> Best Wishes,
>> Ashutosh Bapat
>> EntepriseDB Corporation
>> The Enterprise Postgres Company
>
>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EntepriseDB Corporation
> The Enterprise Postgres Company
>
> ------------------------------------------------------------------------------
> Free Next-Gen Firewall Hardware Offer
> Buy your Sophos next-gen firewall before the end March 2013
> and get the hardware for free! Learn more.
> http://p.sf.net/sfu/sophos-d2d-feb
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>

Re: [Postgres-xc-developers] shipping outer joins

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

Hi Amit,
Please find my replies inlined,



> I think the logic of shippability of outer joins is flawless. Didn't
> find any holes. Patch comments below :
>
> -------
>
> In case of distributed equi-join case, why is
> IsExecNodesColumnDistributed() used instead of
> IsExecNodesDistributedByValue() ? We want to always rule out the round
> robin case, no ? I can see that pgxc_find_dist_equijoin_qual() will
> always fail for round robin tables because they won't have any distrib
> columns, but still , just curious ...
>
>
It keeps open the possibility that we will be able to ship equi-join if we
can somehow infer that the rows from both the sides of join, participating
in the result of join are collocated.


> -------
>
>                  * PGXC_TODO: What do we do when baselocatortype is
>                  * LOCATOR_TYPE_DISTRIBUTED? It could be anything HASH
> distributed or
>                  * MODULO distributed. In that case, having equi-join
> doesn't work
>                  * really, because same value from different relation will
> go to
>                  * different node.
>
> The above comment says that it does not work if one of the tables is
> distributed by hash and other table is distributed by modulo. But the
> code is actually checking the baselocatortype also, so I guess it
> works correctly after all ? I did not get what is the TODO here. Or
> does it mean this ? :
> For (t1_hash join t2_hash on ...) tj1 join (t1_mod join t2_mod on ...)
> tj2 on tj1.col1 = tj2.col4
> the merged nodes for tj1 will have  LOCATOR_TYPE_DISTRIBUTED, and the
> merged nodes for tj2 will also be LOCATOR_TYPE_DISTRIBUTED, and so tj1
> join tj2 would be wrongly marked shippable even though they should not
> be shippable because of the mix of hash and modulo ?
>
>
That's correct. This should be taken care by my second patch up for review.
I think with that patch, we won't need LOCATOR_TYPE_DISTRIBUTED. While
reviewing that patch, can you please also review if this is true.


> -------
>
> Is pgxc_is_expr_shippable(equi_join_expr) necessary ? Won't this qual
> be examined in is_query_shippable() walker ?
>

This code will get executed in standard_planner() as well, so it's possible
that some of the join quals will be shippable and some are not. While this
is fine for an inner join, we want to make sure the a qual which implies
collocation of rows is shippable. This check is more from future extension
perspective than anything else.


>
> --------
>
> If both tables reside on a single datanode, every join case should be
> shippable, which doesn't seem to be happening :
> postgres=# create table tab2 (id2 int, v varchar) distribute by
> replication to node (datanode_1);
> postgres=# create table tab1 (id1 int, v varchar) to node (datanode_1);
> postgres=# explain select * from (tab1 full outer join tab2 on id1 = id2 )
> ;
>                                            QUERY PLAN
>
> -------------------------------------------------------------------------------------------------
>  Hash Full Join  (cost=0.12..0.26 rows=10 width=72)
>    Hash Cond: (tab1.id1 = tab2.id2)
>    ->  Data Node Scan on tab1 "_REMOTE_TABLE_QUERY_"  (cost=0.00..0.00
> rows=1000 width=36)
>          Node/s: datanode_1
>    ->  Hash  (cost=0.00..0.00 rows=1000 width=36)
>          ->  Data Node Scan on tab2 "_REMOTE_TABLE_QUERY_"
> (cost=0.00..0.00 rows=1000 width=36)
>                Node/s: datanode_1
>
> Probably you need to take out the following statement out of the
> distributed case and apply it as a general rule:
>                 /* If there is only single node, try merging the nodes */
>                 if (list_length(inner_en->nodeList) == 1 &&
>                         list_length(outer_en->nodeList) == 1)
>                         merge_nodes = true;
>
>
I am thinking about this and actually thought that we should mark a single
node ExecNodes as REPLICATED, so that it doesn't need any special handling.
What do you think?


>
> >>> --
> >>> Best Wishes,
> >>> Ashutosh Bapat
> >>> EntepriseDB Corporation
> >>> The Enterprise Postgres Company
> >>
> >>
> >>
> >>
> >> --
> >> Best Wishes,
> >> Ashutosh Bapat
> >> EntepriseDB Corporation
> >> The Enterprise Postgres Company
> >
> >
> >
> >
> > --
> > Best Wishes,
> > Ashutosh Bapat
> > EntepriseDB Corporation
> > The Enterprise Postgres Company
> >
> >
> ------------------------------------------------------------------------------
> > Free Next-Gen Firewall Hardware Offer
> > Buy your Sophos next-gen firewall before the end March 2013
> > and get the hardware for free! Learn more.
> > http://p.sf.net/sfu/sophos-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

Re: [Postgres-xc-developers] shipping outer joins

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

On 8 March 2013 14:00, Ashutosh Bapat <ash...@en...>wrote:

> Hi Amit,
> Please find my replies inlined,
>
>
>
>> I think the logic of shippability of outer joins is flawless. Didn't
>> find any holes. Patch comments below :
>>
>> -------
>>
>> In case of distributed equi-join case, why is
>> IsExecNodesColumnDistributed() used instead of
>> IsExecNodesDistributedByValue() ? We want to always rule out the round
>> robin case, no ? I can see that pgxc_find_dist_equijoin_qual() will
>> always fail for round robin tables because they won't have any distrib
>> columns, but still , just curious ...
>>
>>
> It keeps open the possibility that we will be able to ship equi-join if we
> can somehow infer that the rows from both the sides of join, participating
> in the result of join are collocated.
>
>
>> -------
>>
>>                  * PGXC_TODO: What do we do when baselocatortype is
>>                  * LOCATOR_TYPE_DISTRIBUTED? It could be anything HASH
>> distributed or
>>                  * MODULO distributed. In that case, having equi-join
>> doesn't work
>>                  * really, because same value from different relation
>> will go to
>>                  * different node.
>>
>> The above comment says that it does not work if one of the tables is
>> distributed by hash and other table is distributed by modulo. But the
>> code is actually checking the baselocatortype also, so I guess it
>> works correctly after all ? I did not get what is the TODO here. Or
>> does it mean this ? :
>> For (t1_hash join t2_hash on ...) tj1 join (t1_mod join t2_mod on ...)
>> tj2 on tj1.col1 = tj2.col4
>> the merged nodes for tj1 will have  LOCATOR_TYPE_DISTRIBUTED, and the
>> merged nodes for tj2 will also be LOCATOR_TYPE_DISTRIBUTED, and so tj1
>> join tj2 would be wrongly marked shippable even though they should not
>> be shippable because of the mix of hash and modulo ?
>>
>>
> That's correct. This should be taken care by my second patch up for
> review. I think with that patch, we won't need LOCATOR_TYPE_DISTRIBUTED.
> While reviewing that patch, can you please also review if this is true.
>
>
>> -------
>>
>> Is pgxc_is_expr_shippable(equi_join_expr) necessary ? Won't this qual
>> be examined in is_query_shippable() walker ?
>>
>
> This code will get executed in standard_planner() as well, so it's
> possible that some of the join quals will be shippable and some are not.
> While this is fine for an inner join, we want to make sure the a qual which
> implies collocation of rows is shippable. This check is more from future
> extension perspective than anything else.
>
>

Ok. Understood all the comments above.


>
>> --------
>>
>> If both tables reside on a single datanode, every join case should be
>> shippable, which doesn't seem to be happening :
>> postgres=# create table tab2 (id2 int, v varchar) distribute by
>> replication to node (datanode_1);
>> postgres=# create table tab1 (id1 int, v varchar) to node (datanode_1);
>> postgres=# explain select * from (tab1 full outer join tab2 on id1 = id2
>> ) ;
>>                                            QUERY PLAN
>>
>> -------------------------------------------------------------------------------------------------
>>  Hash Full Join  (cost=0.12..0.26 rows=10 width=72)
>>    Hash Cond: (tab1.id1 = tab2.id2)
>>    ->  Data Node Scan on tab1 "_REMOTE_TABLE_QUERY_"  (cost=0.00..0.00
>> rows=1000 width=36)
>>          Node/s: datanode_1
>>    ->  Hash  (cost=0.00..0.00 rows=1000 width=36)
>>          ->  Data Node Scan on tab2 "_REMOTE_TABLE_QUERY_"
>> (cost=0.00..0.00 rows=1000 width=36)
>>                Node/s: datanode_1
>>
>> Probably you need to take out the following statement out of the
>> distributed case and apply it as a general rule:
>>                 /* If there is only single node, try merging the nodes */
>>                 if (list_length(inner_en->nodeList) == 1 &&
>>                         list_length(outer_en->nodeList) == 1)
>>                         merge_nodes = true;
>>
>>
> I am thinking about this and actually thought that we should mark a single
> node ExecNodes as REPLICATED, so that it doesn't need any special handling.
> What do you think?
>

I am concerned about loss of information that the underlying table is
actually distributed. Also, there is a function
IsReturningDMLOnReplicatedTable() which is using this information, although
not sure how much it's making use of that information. I leave that to you
for deciding which option to choose. I personally feel it's always good to
be explicit while checking for this condition.


>
>
>>
>> >>> --
>> >>> Best Wishes,
>> >>> Ashutosh Bapat
>> >>> EntepriseDB Corporation
>> >>> The Enterprise Postgres Company
>> >>
>> >>
>> >>
>> >>
>> >> --
>> >> Best Wishes,
>> >> Ashutosh Bapat
>> >> EntepriseDB Corporation
>> >> The Enterprise Postgres Company
>> >
>> >
>> >
>> >
>> > --
>> > Best Wishes,
>> > Ashutosh Bapat
>> > EntepriseDB Corporation
>> > The Enterprise Postgres Company
>> >
>> >
>> ------------------------------------------------------------------------------
>> > Free Next-Gen Firewall Hardware Offer
>> > Buy your Sophos next-gen firewall before the end March 2013
>> > and get the hardware for free! Learn more.
>> > http://p.sf.net/sfu/sophos-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
>

Re: [Postgres-xc-developers] shipping outer joins

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

Ok, I think it's better to leave distributed as distributed and handle each
separately.

On Mon, Mar 18, 2013 at 2:02 PM, Amit Khandekar <
ami...@en...> wrote:

>
>
> On 8 March 2013 14:00, Ashutosh Bapat <ash...@en...>wrote:
>
>> Hi Amit,
>> Please find my replies inlined,
>>
>>
>>
>>> I think the logic of shippability of outer joins is flawless. Didn't
>>> find any holes. Patch comments below :
>>>
>>> -------
>>>
>>> In case of distributed equi-join case, why is
>>> IsExecNodesColumnDistributed() used instead of
>>> IsExecNodesDistributedByValue() ? We want to always rule out the round
>>> robin case, no ? I can see that pgxc_find_dist_equijoin_qual() will
>>> always fail for round robin tables because they won't have any distrib
>>> columns, but still , just curious ...
>>>
>>>
>> It keeps open the possibility that we will be able to ship equi-join if
>> we can somehow infer that the rows from both the sides of join,
>> participating in the result of join are collocated.
>>
>>
>>> -------
>>>
>>>                  * PGXC_TODO: What do we do when baselocatortype is
>>>                  * LOCATOR_TYPE_DISTRIBUTED? It could be anything HASH
>>> distributed or
>>>                  * MODULO distributed. In that case, having equi-join
>>> doesn't work
>>>                  * really, because same value from different relation
>>> will go to
>>>                  * different node.
>>>
>>> The above comment says that it does not work if one of the tables is
>>> distributed by hash and other table is distributed by modulo. But the
>>> code is actually checking the baselocatortype also, so I guess it
>>> works correctly after all ? I did not get what is the TODO here. Or
>>> does it mean this ? :
>>> For (t1_hash join t2_hash on ...) tj1 join (t1_mod join t2_mod on ...)
>>> tj2 on tj1.col1 = tj2.col4
>>> the merged nodes for tj1 will have  LOCATOR_TYPE_DISTRIBUTED, and the
>>> merged nodes for tj2 will also be LOCATOR_TYPE_DISTRIBUTED, and so tj1
>>> join tj2 would be wrongly marked shippable even though they should not
>>> be shippable because of the mix of hash and modulo ?
>>>
>>>
>> That's correct. This should be taken care by my second patch up for
>> review. I think with that patch, we won't need LOCATOR_TYPE_DISTRIBUTED.
>> While reviewing that patch, can you please also review if this is true.
>>
>>
>>> -------
>>>
>>> Is pgxc_is_expr_shippable(equi_join_expr) necessary ? Won't this qual
>>> be examined in is_query_shippable() walker ?
>>>
>>
>> This code will get executed in standard_planner() as well, so it's
>> possible that some of the join quals will be shippable and some are not.
>> While this is fine for an inner join, we want to make sure the a qual which
>> implies collocation of rows is shippable. This check is more from future
>> extension perspective than anything else.
>>
>>
>
> Ok. Understood all the comments above.
>
>
>>
>>> --------
>>>
>>> If both tables reside on a single datanode, every join case should be
>>> shippable, which doesn't seem to be happening :
>>> postgres=# create table tab2 (id2 int, v varchar) distribute by
>>> replication to node (datanode_1);
>>> postgres=# create table tab1 (id1 int, v varchar) to node (datanode_1);
>>> postgres=# explain select * from (tab1 full outer join tab2 on id1 = id2
>>> ) ;
>>>                                            QUERY PLAN
>>>
>>> -------------------------------------------------------------------------------------------------
>>>  Hash Full Join  (cost=0.12..0.26 rows=10 width=72)
>>>    Hash Cond: (tab1.id1 = tab2.id2)
>>>    ->  Data Node Scan on tab1 "_REMOTE_TABLE_QUERY_"  (cost=0.00..0.00
>>> rows=1000 width=36)
>>>          Node/s: datanode_1
>>>    ->  Hash  (cost=0.00..0.00 rows=1000 width=36)
>>>          ->  Data Node Scan on tab2 "_REMOTE_TABLE_QUERY_"
>>> (cost=0.00..0.00 rows=1000 width=36)
>>>                Node/s: datanode_1
>>>
>>> Probably you need to take out the following statement out of the
>>> distributed case and apply it as a general rule:
>>>                 /* If there is only single node, try merging the nodes */
>>>                 if (list_length(inner_en->nodeList) == 1 &&
>>>                         list_length(outer_en->nodeList) == 1)
>>>                         merge_nodes = true;
>>>
>>>
>> I am thinking about this and actually thought that we should mark a
>> single node ExecNodes as REPLICATED, so that it doesn't need any special
>> handling. What do you think?
>>
>
> I am concerned about loss of information that the underlying table is
> actually distributed. Also, there is a function
> IsReturningDMLOnReplicatedTable() which is using this information, although
> not sure how much it's making use of that information. I leave that to you
> for deciding which option to choose. I personally feel it's always good to
> be explicit while checking for this condition.
>
>
>>
>>
>>>
>>> >>> --
>>> >>> Best Wishes,
>>> >>> Ashutosh Bapat
>>> >>> EntepriseDB Corporation
>>> >>> The Enterprise Postgres Company
>>> >>
>>> >>
>>> >>
>>> >>
>>> >> --
>>> >> Best Wishes,
>>> >> Ashutosh Bapat
>>> >> EntepriseDB Corporation
>>> >> The Enterprise Postgres Company
>>> >
>>> >
>>> >
>>> >
>>> > --
>>> > Best Wishes,
>>> > Ashutosh Bapat
>>> > EntepriseDB Corporation
>>> > The Enterprise Postgres Company
>>> >
>>> >
>>> ------------------------------------------------------------------------------
>>> > Free Next-Gen Firewall Hardware Offer
>>> > Buy your Sophos next-gen firewall before the end March 2013
>>> > and get the hardware for free! Learn more.
>>> > http://p.sf.net/sfu/sophos-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
>>
>
>


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