{"push_id":2894008445,"size":4,"distinct_size":4,"ref":"refs/heads/DML_gather","head":"700d431a82998d4cd2836a78f6cf1991c4a7c188","before":"34a2397adf6f3778cc61f103982d06c209eba5be","commits":[{"sha":"d14e61d7117f822e71170d69951e36cdc153b25f","author":{"name":"Bhuvnesh Chaudhary","email":"42e1caa12835fdb01374f9f3057a95ba5351c8b1@pivotal.io"},"message":"Enforce Redistribute Motion on DML Insert if required\n\nIn case of Insert on Randomly distributed relations, a random / redistribute\nmotion must exists to ensure randomness of the data inserted, else there will\nbe skew on 1 segment.\n\nConsider the below scenario:\nScenario 1:\n```\nCREATE TABLE t1_random (a int) DISTRIBUTED RANDOMLY;\nINSERT INTO t1_random VALUES (1), (2);\nSET optimizer_print_plan=on;\n\nEXPLAIN INSERT INTO t1_random VALUES (1), (2);\nPhysical plan:\n+--CPhysicalDML (Insert, \"t1_random\"), Source Columns: [\"column1\" (0)], Action: (\"ColRef_0001\" (1))   rows:2   width:4  rebinds:1   cost:0.020884   origin: [Grp:1, GrpExpr:2]\n   +--CPhysicalComputeScalar\n      |--CPhysicalMotionRandom  ==> Random Motion Inserted (2)\n      |  +--CPhysicalConstTableGet Columns: [\"column1\" (0)] Values: [(1); (2)] ==> Delivers Universal Spec (1)\n      +--CScalarProjectList\n         +--CScalarProjectElement \"ColRef_0001\" (1)\n            +--CScalarConst (1)\n\",\n                                       QUERY PLAN\n----------------------------------------------------------------------------------------\n Insert  (cost=0.00..0.02 rows=1 width=4)\n   ->  Result  (cost=0.00..0.00 rows=1 width=8)\n         ->  Result  (cost=0.00..0.00 rows=1 width=4) ==> Random Motion Converted to a Result Node with Hash Filter to avoid duplicates (4)\n               ->  Values Scan on \"Values\"  (cost=0.00..0.00 rows=1 width=4) ==> Delivers Universal Spec (3)\n Optimizer: PQO version 2.70.0\n```\n\nWhen an Insert is requested on t1_random from a Universal Source, Optimization\nframework does add a Random Motion / CPhysicalMotionRandom (See (2) above) to\nredistribute the data. However, since CPhysicalConstTableGet / Values Scan\ndelivers Universal Spec, it is converted to a Result Node with hash filter to\navoid duplicates in DXL to Planned Statement (See (4) above). Now, since there\nis no redistribute motion to spray the data randomly on the segments, due to\nthe result node with hash filters, data from only one segment is allowed to\npropagate further, which is inserted by the DML node. This results in all the\ndata being inserted on to 1 segment only.\n\nScenario 2:\n```\nCREATE TABLE t1_random (a int) DISTRIBUTED RANDOMLY;\nCREATE TABLE t2_random (a int) DISTRIBUTED RANDOMLY;\nEXPLAIN INSERT INTO t1_random SELECT * FROM t2_random;\nPhysical plan:\n+--CPhysicalDML (Insert, \"t1_random\"), Source Columns: [\"a\" (0)], Action: (\"ColRef_0008\" (8))   rows:1   width:34  rebinds:1   cost:431.010436   origin: [Grp:1, GrpExpr:2]\n   +--CPhysicalComputeScalar   rows:1   width:1  rebinds:1   cost:431.000011   origin: [Grp:5, GrpExpr:1]\n      |--CPhysicalTableScan \"t2_random\" (\"t2_random\")   rows:1   width:34  rebinds:1   cost:431.000006   origin: [Grp:0, GrpExpr:1]\n      +--CScalarProjectList   origin: [Grp:4, GrpExpr:0]\n         +--CScalarProjectElement \"ColRef_0008\" (8)   origin: [Grp:3, GrpExpr:0]\n            +--CScalarConst (1)   origin: [Grp:2, GrpExpr:0]\n\",\n                                        QUERY PLAN\n------------------------------------------------------------------------------------------\n Insert  (cost=0.00..431.01 rows=1 width=4)\n   ->  Result  (cost=0.00..431.00 rows=1 width=8)\n         ->  Table Scan on t2_random  (cost=0.00..431.00 rows=1 width=4)\n Optimizer: PQO version 2.70.0\n```\n\nWhen an Insert is request on t1_random (randomly distributed table) from\nanother randomly distributed table, optimization framework does not add a\nredistribute motion because CPhysicalDML requested Random Spec and the source\nt2_random delivers random distribution spec matching / satisfying the requested\nspec.\n\nSo, in summary, there are 2 causes for skewness in the data inserted into randomly distributed table.\nScenario 1: Where the Random Motion is converted into a Result Node to Hash Filters\nScneario 2: Where the requested and derived spec matches.\n\nThis patch fixes the issue by ensuring that if an insert is performed on a\nrandomly distributed table, there must exists a random motion which has been\nenforcing strict random distribution spec.  This is achived in 2 steps:\n1. CPhysicalDML / Insert on Randomly Distributed table must request a Strict Random Spec\n2. Append Enforcer for Random Spec must enforce a strict random spec for the motion node\nwhen the child of motion does not deliver universal spec, else enforce a\nrandom spec.\n\nCharacteristic of Strict Random Spec:\n1. Strict Random Spec satisfy Strict Random Spec and Random Spec.\nSatisfying Random Spec ensures that another random motion with\nrandom spec is not enforced.\n2. Random Spec does not satisfy Strict Random Spec. This ensures that\nif Strict Random Spec is requested and if the child delivers random spec\na motion node with strict random spec is inserted.\n\nCPhysicalDML always has a CPhysicalComputeScalar node below it which projects\nthe additional column indicating if it's an Insert. The request for Strict\nMotion is not propagated down the CPhysicalComputeScalar node,\nCPhysicalComputeScalar requests Random Spec from its child instead. This is to\nmandate the existence of a random motion delivering strict random spec\neither by the childs of CPhysicalDML node or it should be inserted between\nCPhysicalDML and CPhysicalComputeScalar.\nIf there is any motion in the same group holding CPhysicalComputeScalar,\nit should also request Random Spec from its childs.\n\nPlans after the fix\nScenario 1:\n```\nEXPLAIN INSERT INTO t1_random VALUES (1), (2);\nPhysical plan:\n+--CPhysicalDML (Insert, \"t1_random\"), Source Columns: [\"column1\" (0)], Action: (\"ColRef_0001\" (1))   rows:2   width:4  rebinds:1   cost:0.020884   origin: [Grp:1, GrpExpr:2]\n   +--CPhysicalMotionRandom   rows:2   width:1  rebinds:1   cost:0.000051   origin: [Grp:5, GrpExpr:2] ==> Strict Random Motion Enforcing true randomness\n      +--CPhysicalComputeScalar   rows:2   width:1  rebinds:1   cost:0.000034   origin: [Grp:5, GrpExpr:1]\n         |--CPhysicalMotionRandom   rows:2   width:4  rebinds:1   cost:0.000029   origin: [Grp:0, GrpExpr:2]\n         |  +--CPhysicalConstTableGet Columns: [\"column1\" (0)] Values: [(1); (2)]   rows:2   width:4  rebinds:1   cost:0.000008   origin: [Grp:0, GrpExpr:1]\n         +--CScalarProjectList   origin: [Grp:4, GrpExpr:0]\n            +--CScalarProjectElement \"ColRef_0001\" (1)   origin: [Grp:3, GrpExpr:0]\n               +--CScalarConst (1)   origin: [Grp:2, GrpExpr:0]\n\",\n                                       QUERY PLAN\n----------------------------------------------------------------------------------------\n Insert  (cost=0.00..0.02 rows=1 width=4)\n   ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..0.00 rows=1 width=8) ==> Strict Random Motion Enforcing true randomness\n         ->  Result  (cost=0.00..0.00 rows=1 width=8)\n               ->  Result  (cost=0.00..0.00 rows=1 width=4)\n                     ->  Values Scan on \"Values\"  (cost=0.00..0.00 rows=1 width=4)\n Optimizer: PQO version 2\n\n```\n\nScenario 2:\n```\nEXPLAIN INSERT INTO t1_random SELECT * FROM t2_random;\nPhysical plan:\n+--CPhysicalDML (Insert, \"t1_random\"), Source Columns: [\"a\" (0)], Action: (\"ColRef_0008\" (8))   rows:2   width:34  rebinds:1   cost:431.020873   origin: [Grp:1, GrpExpr:2]\n   +--CPhysicalMotionRandom   rows:2   width:1  rebinds:1   cost:431.000039   origin: [Grp:5, GrpExpr:2] ==> Strict Random Motion Enforcing True Randomness\n      +--CPhysicalComputeScalar   rows:2   width:1  rebinds:1   cost:431.000023   origin: [Grp:5, GrpExpr:1]\n         |--CPhysicalTableScan \"t2random\" (\"t2_random\")   rows:2   width:34  rebinds:1   cost:431.000012   origin: [Grp:0, GrpExpr:1]\n         +--CScalarProjectList   origin: [Grp:4, GrpExpr:0]\n            +--CScalarProjectElement \"ColRef_0008\" (8)   origin: [Grp:3, GrpExpr:0]\n               +--CScalarConst (1)   origin: [Grp:2, GrpExpr:0]\n\",\n                                        QUERY PLAN\n------------------------------------------------------------------------------------------\n Insert  (cost=0.00..431.02 rows=1 width=4)\n   ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=8) ==> Strict Random Motion Enforcing true randomness\n         ->  Result  (cost=0.00..431.00 rows=1 width=8)\n               ->  Table Scan on t2_random  (cost=0.00..431.00 rows=1 width=4)\n Optimizer: PQO version 2.70.0\n(5 rows)\n```\n\nNote: If Insert is performed on a Randomly Distributed Table from a Hash\nDistributed Table (childs), an additional redistribute motion is not enforced\nbetween CPhysicalDML and CPhysicalComputeScalar as there exists a random motion\ndelivering strict random spec due to mismatch of random vs hash distributed spec.\n```\npivotal=# explain insert into t1_random select * from t1_hash;\nLOG:  statement: explain insert into t1_random select * from t1_hash;\nLOG:  2018-08-20 13:31:01:135438 PDT,THD000,TRACE,\"\nPhysical plan:\n+--CPhysicalDML (Insert, \"t1_random\"), Source Columns: [\"a\" (0)], Action: (\"ColRef_0008\" (8))   rows:100   width:34  rebinds:1   cost:432.043222   origin: [Grp:1, GrpExpr:2]\n   +--CPhysicalComputeScalar   rows:100   width:1  rebinds:1   cost:431.001555   origin: [Grp:5, GrpExpr:1]\n      |--CPhysicalMotionRandom   rows:100   width:34  rebinds:1   cost:431.001289   origin: [Grp:0, GrpExpr:2]\n      |  +--CPhysicalTableScan \"t1_hash\" (\"t1_hash\")   rows:100   width:34  rebinds:1   cost:431.000623   origin: [Grp:0, GrpExpr:1]\n      +--CScalarProjectList   origin: [Grp:4, GrpExpr:0]\n         +--CScalarProjectElement \"ColRef_0008\" (8)   origin: [Grp:3, GrpExpr:0]\n            +--CScalarConst (1)   origin: [Grp:2, GrpExpr:0]\n\",\n                                           QUERY PLAN\n-------------------------------------------------------------------------------------------------\n Insert  (cost=0.00..432.04 rows=34 width=4)\n   ->  Result  (cost=0.00..431.00 rows=34 width=8)\n         ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=34 width=4)\n               ->  Table Scan on t1_hash  (cost=0.00..431.00 rows=34 width=4)\n Optimizer: PQO version 2.70.0\n(5 rows)\n\nTime: 69.309 ms\n```\n\nBump ORCA to v2.75.0","distinct":true,"url":"https://api.github.com/repos/dhanashreek89/gporca/commits/d14e61d7117f822e71170d69951e36cdc153b25f"},{"sha":"38ea9c90bba6d0cc96ec15c07a7eeb63c028e1d8","author":{"name":"Sambitesh Dash","email":"0c774551a12bd2c108d5895626002000c1bb7d82@pivotal.io"},"message":"Optimize DML query by enforcing non-master gather\n\nWhen a DML query contains operator such as Limit or an Aggregate, such\noperator requested singleton-master distribution spec which led to a\ngather motion on master. For instance:\ncreate table test1 (a int, b int);\ninsert into test1 select generate_series(1,100000),generate_series(1,100000);\n\ncreate table test as select * from test1 limit 10000000;\n\n+--CPhysicalDML (Insert, \"fake ctas rel\")\n\t   +--CPhysicalComputeScalar\n\t      |--CPhysicalMotionRandom\n\t      |  +--CPhysicalLimit <empty> global\n\t      |     |--CPhysicalMotionGather(master)\n\t      |     |  +--CPhysicalTableScan \"test1\" (\"test1\")\n\t      |     |--CScalarConst (0)\n\t      |     +--CScalarConst (100000)\n\t      +--CScalarProjectList\n\t\t +--CScalarProjectElement \"ColRef_0009\" (9)\n\t\t    +--CScalarConst (1)\n\nThis behavior has a potential to cause resource contention on a master\nnode when the workload contains lot of such concurrent queries.  Unlike\nSELECT statements, DML queries do not have a Singleton distribution\nrequirement, hence by default we start with CDistributionSpecAny()\nrequirement at the top level. As DML query can be executed completely\nwithout master (with one exception described later), this commit\noptimizes such queries by enforcing a non-master gather motion whenever\na single host execution is needed.\n\nAll the derive() and required() distribution spec routines now will\nproduce a singleton segment when optimizing a DML query. This\noptimization is not performed if -\n(1) DML query that contains a master-only table such as below :\n\nset optimizer_enable_master_only_queries = on;\ndelete from x where exists (select port from gp_segment_configuration limit 10);\n\nIn such scenarios, we refrain from producing singleton segment\ndistribution spec as the tree containing master-only table can only be\nexecuted on a master node.\n\n(2) DML query that contains a function (STABLE or VOLATILE) that\nperforms a SQL access. We can not determine if the SQL access is\nperformed on a master-only table; hence we can not enforce non-master\ngather safely.\n\nFor volatile functions with no SQL access, such as random(), we will\nproduce singleton-segment spec and enforce non-master gather.\n\nSigned-off-by: Dhanashree Kashid <dkashid@pivotal.io>","distinct":true,"url":"https://api.github.com/repos/dhanashreek89/gporca/commits/38ea9c90bba6d0cc96ec15c07a7eeb63c028e1d8"},{"sha":"85a9c182674f8a34dc061327a3cb02e7be70c3c5","author":{"name":"Sambitesh Dash","email":"0c774551a12bd2c108d5895626002000c1bb7d82@pivotal.io"},"message":"Fix test cases and add new tests\n\nSigned-off-by: Dhanashree Kashid <dkashid@pivotal.io>","distinct":true,"url":"https://api.github.com/repos/dhanashreek89/gporca/commits/85a9c182674f8a34dc061327a3cb02e7be70c3c5"},{"sha":"700d431a82998d4cd2836a78f6cf1991c4a7c188","author":{"name":"Sambitesh Dash","email":"0c774551a12bd2c108d5895626002000c1bb7d82@pivotal.io"},"message":"Bump ORCA version to v3.0.0\n\nSigned-off-by: Dhanashree Kashid <dkashid@pivotal.io>","distinct":true,"url":"https://api.github.com/repos/dhanashreek89/gporca/commits/700d431a82998d4cd2836a78f6cf1991c4a7c188"}]}
{"actor":{"display_login":"bhuvnesh2703"}}