diff options
| author | Iuliia Sidorina <yulia@ydb.tech> | 2024-10-07 17:34:19 +0200 |
|---|---|---|
| committer | GitHub <noreply@github.com> | 2024-10-07 17:34:19 +0200 |
| commit | a91cf35875165a1e7b20cb79925892e304c7b911 (patch) | |
| tree | 7e4572f20c2978ee8d6175077883deab2104d5ef | |
| parent | e95ff277a4d6628c7ad21a0f1ef55c50affe6e12 (diff) | |
| download | ydb-a91cf35875165a1e7b20cb79925892e304c7b911.tar.gz | |
feat(kqp): enable stream join for data query (#9568)
65 files changed, 1947 insertions, 4091 deletions
diff --git a/ydb/core/kqp/ut/view/input/cases/aggregates_and_window/etalon_query.sql b/ydb/core/kqp/ut/view/input/cases/aggregates_and_window/etalon_query.sql index c701bd2d26..d0a45e7825 100644 --- a/ydb/core/kqp/ut/view/input/cases/aggregates_and_window/etalon_query.sql +++ b/ydb/core/kqp/ut/view/input/cases/aggregates_and_window/etalon_query.sql @@ -43,4 +43,4 @@ FROM ( JOIN `/Root/series` AS series USING (series_id) -); +) ORDER BY series; diff --git a/ydb/core/kqp/ut/view/input/cases/aggregates_and_window/select_from_view.sql b/ydb/core/kqp/ut/view/input/cases/aggregates_and_window/select_from_view.sql index 0cc947dd86..f0ffc56329 100644 --- a/ydb/core/kqp/ut/view/input/cases/aggregates_and_window/select_from_view.sql +++ b/ydb/core/kqp/ut/view/input/cases/aggregates_and_window/select_from_view.sql @@ -1,3 +1,4 @@ SELECT * -FROM `/Root/aggregates_and_window`; +FROM `/Root/aggregates_and_window` +ORDER BY series; diff --git a/ydb/core/kqp/ut/view/input/cases/count_episodes/etalon_query.sql b/ydb/core/kqp/ut/view/input/cases/count_episodes/etalon_query.sql index 8964c9ab76..1b0c08cc86 100644 --- a/ydb/core/kqp/ut/view/input/cases/count_episodes/etalon_query.sql +++ b/ydb/core/kqp/ut/view/input/cases/count_episodes/etalon_query.sql @@ -9,4 +9,4 @@ FROM ( GROUP BY series_id, season_id -); +) ORDER BY series_id, season_id; diff --git a/ydb/core/kqp/ut/view/input/cases/count_episodes/select_from_view.sql b/ydb/core/kqp/ut/view/input/cases/count_episodes/select_from_view.sql index 153ad494f1..d097b9bf89 100644 --- a/ydb/core/kqp/ut/view/input/cases/count_episodes/select_from_view.sql +++ b/ydb/core/kqp/ut/view/input/cases/count_episodes/select_from_view.sql @@ -1,3 +1,4 @@ SELECT * -FROM `/Root/count_episodes`; +FROM `/Root/count_episodes` +ORDER BY series_id, season_id; diff --git a/ydb/core/kqp/ut/view/input/cases/count_episodes_with_titles/etalon_query.sql b/ydb/core/kqp/ut/view/input/cases/count_episodes_with_titles/etalon_query.sql index faa472a66b..3125d2c5d6 100644 --- a/ydb/core/kqp/ut/view/input/cases/count_episodes_with_titles/etalon_query.sql +++ b/ydb/core/kqp/ut/view/input/cases/count_episodes_with_titles/etalon_query.sql @@ -22,4 +22,4 @@ FROM ( JOIN `/Root/seasons` AS seasons ON episodes.series_id == seasons.series_id AND episodes.season_id == seasons.season_id -); +) ORDER BY series, season; diff --git a/ydb/core/kqp/ut/view/input/cases/count_episodes_with_titles/select_from_view.sql b/ydb/core/kqp/ut/view/input/cases/count_episodes_with_titles/select_from_view.sql index b84299a26d..7a55adb289 100644 --- a/ydb/core/kqp/ut/view/input/cases/count_episodes_with_titles/select_from_view.sql +++ b/ydb/core/kqp/ut/view/input/cases/count_episodes_with_titles/select_from_view.sql @@ -1,3 +1,4 @@ SELECT * -FROM `/Root/count_episodes_with_titles`; +FROM `/Root/count_episodes_with_titles` +ORDER BY series, season; diff --git a/ydb/core/kqp/ut/view/input/cases/multiple_tables/etalon_query.sql b/ydb/core/kqp/ut/view/input/cases/multiple_tables/etalon_query.sql index f123a6a1dc..023d53c349 100644 --- a/ydb/core/kqp/ut/view/input/cases/multiple_tables/etalon_query.sql +++ b/ydb/core/kqp/ut/view/input/cases/multiple_tables/etalon_query.sql @@ -18,4 +18,4 @@ FROM ( ) AS seasons_and_episodes ON series.series_id == seasons_and_episodes.series_id -); +) ORDER BY series_id; diff --git a/ydb/core/kqp/ut/view/input/cases/multiple_tables/select_from_view.sql b/ydb/core/kqp/ut/view/input/cases/multiple_tables/select_from_view.sql index aedc8541a5..b6fc8f6471 100644 --- a/ydb/core/kqp/ut/view/input/cases/multiple_tables/select_from_view.sql +++ b/ydb/core/kqp/ut/view/input/cases/multiple_tables/select_from_view.sql @@ -1,3 +1,4 @@ SELECT * -FROM `/Root/read_from_multiple_tables`; +FROM `/Root/read_from_multiple_tables` +ORDER BY series_id; diff --git a/ydb/core/kqp/ut/view/input/cases/multiple_views/etalon_query.sql b/ydb/core/kqp/ut/view/input/cases/multiple_views/etalon_query.sql index c0ba9c284e..804ac48db3 100644 --- a/ydb/core/kqp/ut/view/input/cases/multiple_views/etalon_query.sql +++ b/ydb/core/kqp/ut/view/input/cases/multiple_views/etalon_query.sql @@ -9,4 +9,4 @@ FROM ( JOIN `/Root/view_seasons` AS seasons ON series.series_id == seasons.series_id -); +) ORDER BY series_title, seasons_title; diff --git a/ydb/core/kqp/ut/view/input/cases/multiple_views/select_from_view.sql b/ydb/core/kqp/ut/view/input/cases/multiple_views/select_from_view.sql index eb1962a050..57930e8f51 100644 --- a/ydb/core/kqp/ut/view/input/cases/multiple_views/select_from_view.sql +++ b/ydb/core/kqp/ut/view/input/cases/multiple_views/select_from_view.sql @@ -1,3 +1,4 @@ SELECT * -FROM `/Root/read_from_multiple_views`; +FROM `/Root/read_from_multiple_views` +ORDER BY series_title, seasons_title; diff --git a/ydb/core/kqp/ut/view/input/cases/one_table/etalon_query.sql b/ydb/core/kqp/ut/view/input/cases/one_table/etalon_query.sql index 46bcd9b2ea..aaf7b0eae1 100644 --- a/ydb/core/kqp/ut/view/input/cases/one_table/etalon_query.sql +++ b/ydb/core/kqp/ut/view/input/cases/one_table/etalon_query.sql @@ -4,4 +4,4 @@ FROM ( SELECT * FROM `/Root/series` -); +) ORDER BY series_id; diff --git a/ydb/core/kqp/ut/view/input/cases/one_table/select_from_view.sql b/ydb/core/kqp/ut/view/input/cases/one_table/select_from_view.sql index 7018220ad0..d246b5ea76 100644 --- a/ydb/core/kqp/ut/view/input/cases/one_table/select_from_view.sql +++ b/ydb/core/kqp/ut/view/input/cases/one_table/select_from_view.sql @@ -1,3 +1,4 @@ SELECT * -FROM `/Root/read_from_one_table`; +FROM `/Root/read_from_one_table` +ORDER BY series_id; diff --git a/ydb/core/kqp/ut/view/input/cases/one_view/etalon_query.sql b/ydb/core/kqp/ut/view/input/cases/one_view/etalon_query.sql index 46bcd9b2ea..aaf7b0eae1 100644 --- a/ydb/core/kqp/ut/view/input/cases/one_view/etalon_query.sql +++ b/ydb/core/kqp/ut/view/input/cases/one_view/etalon_query.sql @@ -4,4 +4,4 @@ FROM ( SELECT * FROM `/Root/series` -); +) ORDER BY series_id; diff --git a/ydb/core/kqp/ut/view/input/cases/one_view/select_from_view.sql b/ydb/core/kqp/ut/view/input/cases/one_view/select_from_view.sql index 0a3b65acf0..50a4382c94 100644 --- a/ydb/core/kqp/ut/view/input/cases/one_view/select_from_view.sql +++ b/ydb/core/kqp/ut/view/input/cases/one_view/select_from_view.sql @@ -1,3 +1,4 @@ SELECT * -FROM `/Root/read_from_one_view`; +FROM `/Root/read_from_one_view` +ORDER BY series_id; diff --git a/ydb/core/kqp/ut/view/input/cases/two_tables/etalon_query.sql b/ydb/core/kqp/ut/view/input/cases/two_tables/etalon_query.sql index f6be743ed1..25cfef4b2b 100644 --- a/ydb/core/kqp/ut/view/input/cases/two_tables/etalon_query.sql +++ b/ydb/core/kqp/ut/view/input/cases/two_tables/etalon_query.sql @@ -9,4 +9,4 @@ FROM ( JOIN `/Root/seasons` AS seasons ON series.series_id == seasons.series_id -); +) ORDER BY series_title, seasons_title; diff --git a/ydb/core/kqp/ut/view/input/cases/two_tables/select_from_view.sql b/ydb/core/kqp/ut/view/input/cases/two_tables/select_from_view.sql index 922bc55ebe..fab190ffa1 100644 --- a/ydb/core/kqp/ut/view/input/cases/two_tables/select_from_view.sql +++ b/ydb/core/kqp/ut/view/input/cases/two_tables/select_from_view.sql @@ -1,3 +1,4 @@ SELECT * -FROM `/Root/read_from_two_tables`; +FROM `/Root/read_from_two_tables` +ORDER BY series_title, seasons_title; diff --git a/ydb/core/protos/table_service_config.proto b/ydb/core/protos/table_service_config.proto index 3d793a9c7f..921e6821f3 100644 --- a/ydb/core/protos/table_service_config.proto +++ b/ydb/core/protos/table_service_config.proto @@ -238,7 +238,7 @@ message TTableServiceConfig { reserved 33; // optional bool EnableKqpDataQueryStreamPointLookup = 33 [default = false]; optional bool EnablePublishKqpProxyByRM = 34 [default = true]; optional bool EnableKqpScanQueryStreamIdxLookupJoin = 35 [default = false]; - optional bool EnableKqpDataQueryStreamIdxLookupJoin = 49 [default = false]; + optional bool EnableKqpDataQueryStreamIdxLookupJoin = 49 [default = true]; reserved 36; //optional bool EnablePredicateExtractForScanQueries = 36 [default = true]; reserved 37; //optional bool EnablePredicateExtractForDataQueries = 37 [default = true]; reserved 38; // optional bool EnableKqpImmediateEffects = 38 [default = true]; diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_group_by_lookup.script-script_/join_group_by_lookup.script.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_group_by_lookup.script-script_/join_group_by_lookup.script.plan index 384f82f567..a3859bdee8 100644 --- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_group_by_lookup.script-script_/join_group_by_lookup.script.plan +++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_group_by_lookup.script-script_/join_group_by_lookup.script.plan @@ -10,8 +10,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_2", - "PlanNodeId": 21, + "Node Type": "ResultSet", + "PlanNodeId": 11, "PlanNodeType": "ResultSet", "Plans": [ { @@ -20,27 +20,30 @@ { "Inputs": [ { - "ExternalPlanNodeId": 19 + "ExternalPlanNodeId": 9 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 20, + "PlanNodeId": 10, "Plans": [ { "Node Type": "Merge", - "PlanNodeId": 19, + "PlanNodeId": 9, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "TopSort-LeftJoin (MapJoin)", + "Node Type": "TopSort-TopSort", "Operators": [ { "Inputs": [ { "InternalOperatorId": 1 + }, + { + "ExternalPlanNodeId": 7 } ], "Limit": "1001", @@ -48,91 +51,101 @@ "TopSortBy": "" }, { - "Condition": "a.Group = t.Group", "Inputs": [ { - "ExternalPlanNodeId": 17 - }, - { - "ExternalPlanNodeId": 15 + "ExternalPlanNodeId": 7 } ], - "Name": "LeftJoin (MapJoin)" + "Limit": "1001", + "Name": "TopSort", + "TopSortBy": "" } ], - "PlanNodeId": 18, + "PlanNodeId": 8, "Plans": [ { - "Node Type": "Map", - "PlanNodeId": 17, - "PlanNodeType": "Connection", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "ConstantExpr", - "Operators": [ - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_0_0" - } - ], - "PlanNodeId": 16 - } - ] - }, - { - "Node Type": "Broadcast", - "PlanNodeId": 15, + "Node Type": "UnionAll", + "PlanNodeId": 7, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "Stage", - "PlanNodeId": 14, + "Node Type": "Collect", + "PlanNodeId": 6, "Plans": [ { - "Node Type": "UnionAll", - "PlanNodeId": 13, + "Columns": [ + "Group", + "Value" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "Group" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 5, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_1_0", - "Node Type": "Filter-TablePointLookup-ConstantExpr", - "Operators": [ + "Node Type": "Stage", + "PlanNodeId": 4, + "Plans": [ { - "Inputs": [ - { - "InternalOperatorId": 1 - } + "KeyColumns": [ + "Group" ], - "Name": "Filter", - "Predicate": "Exist(item.Group)" - }, - { - "Inputs": [ + "Node Type": "HashShuffle", + "PlanNodeId": 3, + "PlanNodeType": "Connection", + "Plans": [ { - "InternalOperatorId": 2 + "Node Type": "Aggregate", + "Operators": [ + { + "Aggregation": "{_yql_agg_0: SUM(state._yql_agg_0,1)}", + "GroupBy": "item.Group", + "Inputs": [ + { + "ExternalPlanNodeId": 1 + } + ], + "Name": "Aggregate" + } + ], + "PlanNodeId": 2, + "Plans": [ + { + "Node Type": "TableFullScan", + "Operators": [ + { + "Inputs": [], + "Name": "TableFullScan", + "ReadColumns": [ + "Group" + ], + "ReadRanges": [ + "Group (-\u221e, +\u221e)", + "Name (-\u221e, +\u221e)" + ], + "ReadRangesPointPrefixLen": "0", + "Scan": "Parallel", + "Table": "base_join_group_by_lookup_script_script/Input1" + } + ], + "PlanNodeId": 1, + "Tables": [ + "base_join_group_by_lookup_script_script/Input1" + ] + } + ] } - ], - "Name": "TablePointLookup", - "ReadColumns": [ - "Group", - "Value" - ], - "Table": "base_join_group_by_lookup_script_script/Temp" - }, - { - "Inputs": [], - "Iterator": "precompute_1_0", - "Name": "Iterator" + ] } - ], - "PlanNodeId": 12, - "Tables": [ - "base_join_group_by_lookup_script_script/Temp" ] } - ] + ], + "Table": "base_join_group_by_lookup_script_script/Temp" } ] } @@ -150,106 +163,6 @@ ] } ] - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 10, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_0_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 9 - } - ], - "Subplan Name": "CTE precompute_1_0" - }, - { - "Node Type": "Precompute_0", - "Parent Relationship": "InitPlan", - "PlanNodeId": 7, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 6, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 5, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Stage", - "PlanNodeId": 4, - "Plans": [ - { - "KeyColumns": [ - "Group" - ], - "Node Type": "HashShuffle", - "PlanNodeId": 3, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Aggregate", - "Operators": [ - { - "Aggregation": "{_yql_agg_0: SUM(state._yql_agg_0,1)}", - "GroupBy": "item.Group", - "Inputs": [ - { - "ExternalPlanNodeId": 1 - } - ], - "Name": "Aggregate" - } - ], - "PlanNodeId": 2, - "Plans": [ - { - "Node Type": "TableFullScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableFullScan", - "ReadColumns": [ - "Group" - ], - "ReadRanges": [ - "Group (-\u221e, +\u221e)", - "Name (-\u221e, +\u221e)" - ], - "ReadRangesPointPrefixLen": "0", - "Scan": "Parallel", - "Table": "base_join_group_by_lookup_script_script/Input1" - } - ], - "PlanNodeId": 1, - "Tables": [ - "base_join_group_by_lookup_script_script/Input1" - ] - } - ] - } - ] - } - ] - } - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_0_0" } ], "Stats": { @@ -280,6 +193,9 @@ "Group", "Value" ], + "lookup_by": [ + "Group" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_double_lookup.sql-plan_/join_join_double_lookup.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_double_lookup.sql-plan_/join_join_double_lookup.sql.plan index 5f581d2bb8..d71e6dd8c9 100644 --- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_double_lookup.sql-plan_/join_join_double_lookup.sql.plan +++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_double_lookup.sql-plan_/join_join_double_lookup.sql.plan @@ -26,7 +26,10 @@ "Key1", "Key2" ], - "type": "Scan" + "lookup_by": [ + "Key1" + ], + "type": "Lookup" } ] } diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_dup_column_right.sql-plan_/join_join_dup_column_right.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_dup_column_right.sql-plan_/join_join_dup_column_right.sql.plan index 353e0e719d..156ba5e195 100644 --- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_dup_column_right.sql-plan_/join_join_dup_column_right.sql.plan +++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_dup_column_right.sql-plan_/join_join_dup_column_right.sql.plan @@ -28,7 +28,10 @@ "Key1", "Key2" ], - "type": "Scan" + "lookup_by": [ + "Key1" + ], + "type": "Lookup" } ] } diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_range_right.sql-plan_/join_join_range_right.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_range_right.sql-plan_/join_join_range_right.sql.plan index ab27c6a2d4..5598346a8a 100644 --- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_range_right.sql-plan_/join_join_range_right.sql.plan +++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_range_right.sql-plan_/join_join_range_right.sql.plan @@ -29,6 +29,10 @@ "Key2", "Value" ], + "lookup_by": [ + "Key1", + "Key2" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup.sql-plan_/join_join_to_idx_lookup.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup.sql-plan_/join_join_to_idx_lookup.sql.plan index fb8adb1aff..78e3aa37f4 100644 --- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup.sql-plan_/join_join_to_idx_lookup.sql.plan +++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup.sql-plan_/join_join_to_idx_lookup.sql.plan @@ -29,6 +29,10 @@ "Key1", "Key2" ], + "lookup_by": [ + "Key1", + "Key2" + ], "type": "Lookup" } ] @@ -41,6 +45,9 @@ "Key", "Value" ], + "lookup_by": [ + "Key" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_cast.sql-plan_/join_join_to_idx_lookup_cast.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_cast.sql-plan_/join_join_to_idx_lookup_cast.sql.plan index c4b1449d35..07dbd52ab1 100644 --- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_cast.sql-plan_/join_join_to_idx_lookup_cast.sql.plan +++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_cast.sql-plan_/join_join_to_idx_lookup_cast.sql.plan @@ -13,6 +13,10 @@ "Key2", "Value" ], + "lookup_by": [ + "Key1", + "Key2" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_cast2.sql-plan_/join_join_to_idx_lookup_cast2.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_cast2.sql-plan_/join_join_to_idx_lookup_cast2.sql.plan index fa88c7811f..a2a25f54ab 100644 --- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_cast2.sql-plan_/join_join_to_idx_lookup_cast2.sql.plan +++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_cast2.sql-plan_/join_join_to_idx_lookup_cast2.sql.plan @@ -30,7 +30,10 @@ "Key2", "Value" ], - "type": "Scan" + "lookup_by": [ + "Key1" + ], + "type": "Lookup" } ] } diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_dup_c_left.sql-plan_/join_join_to_idx_lookup_dup_c_left.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_dup_c_left.sql-plan_/join_join_to_idx_lookup_dup_c_left.sql.plan index 72375bfab2..191c472eaa 100644 --- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_dup_c_left.sql-plan_/join_join_to_idx_lookup_dup_c_left.sql.plan +++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_dup_c_left.sql-plan_/join_join_to_idx_lookup_dup_c_left.sql.plan @@ -28,6 +28,10 @@ "Key2", "Value" ], + "lookup_by": [ + "Key1", + "Key2" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_equi.sql-plan_/join_join_to_idx_lookup_equi.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_equi.sql-plan_/join_join_to_idx_lookup_equi.sql.plan index 58663c896e..abbd2fdae1 100644 --- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_equi.sql-plan_/join_join_to_idx_lookup_equi.sql.plan +++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_equi.sql-plan_/join_join_to_idx_lookup_equi.sql.plan @@ -31,6 +31,10 @@ "Key2", "Value" ], + "lookup_by": [ + "Key1", + "Key2" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_inner.sql-plan_/join_join_to_idx_lookup_inner.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_inner.sql-plan_/join_join_to_idx_lookup_inner.sql.plan index 9425366fa0..7504dfff41 100644 --- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_inner.sql-plan_/join_join_to_idx_lookup_inner.sql.plan +++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_inner.sql-plan_/join_join_to_idx_lookup_inner.sql.plan @@ -29,6 +29,10 @@ "Key1", "Key2" ], + "lookup_by": [ + "Key1", + "Key2" + ], "type": "Lookup" } ] @@ -41,6 +45,9 @@ "Key", "Value" ], + "lookup_by": [ + "Key" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_leftonly.sql-plan_/join_join_to_idx_lookup_leftonly.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_leftonly.sql-plan_/join_join_to_idx_lookup_leftonly.sql.plan index e3cd0cc87d..664b99a71d 100644 --- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_leftonly.sql-plan_/join_join_to_idx_lookup_leftonly.sql.plan +++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_leftonly.sql-plan_/join_join_to_idx_lookup_leftonly.sql.plan @@ -27,6 +27,9 @@ "columns": [ "Key" ], + "lookup_by": [ + "Key" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_leftsemi.sql-plan_/join_join_to_idx_lookup_leftsemi.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_leftsemi.sql-plan_/join_join_to_idx_lookup_leftsemi.sql.plan index af8b7b3f2b..7077a27cbf 100644 --- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_leftsemi.sql-plan_/join_join_to_idx_lookup_leftsemi.sql.plan +++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_leftsemi.sql-plan_/join_join_to_idx_lookup_leftsemi.sql.plan @@ -28,6 +28,10 @@ "Key1", "Key2" ], + "lookup_by": [ + "Key1", + "Key2" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_partial_inner.sql-plan_/join_join_to_idx_lookup_partial_inner.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_partial_inner.sql-plan_/join_join_to_idx_lookup_partial_inner.sql.plan index f587e23ada..228b647872 100644 --- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_partial_inner.sql-plan_/join_join_to_idx_lookup_partial_inner.sql.plan +++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_partial_inner.sql-plan_/join_join_to_idx_lookup_partial_inner.sql.plan @@ -28,7 +28,10 @@ "Key1", "Value" ], - "type": "Scan" + "lookup_by": [ + "Key1" + ], + "type": "Lookup" } ] }, @@ -40,6 +43,9 @@ "Key", "Value" ], + "lookup_by": [ + "Key" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_partial_lefonly.sql-plan_/join_join_to_idx_lookup_partial_lefonly.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_partial_lefonly.sql-plan_/join_join_to_idx_lookup_partial_lefonly.sql.plan index ca810a2811..b3693d239a 100644 --- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_partial_lefonly.sql-plan_/join_join_to_idx_lookup_partial_lefonly.sql.plan +++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_partial_lefonly.sql-plan_/join_join_to_idx_lookup_partial_lefonly.sql.plan @@ -27,7 +27,10 @@ "columns": [ "Key1" ], - "type": "Scan" + "lookup_by": [ + "Key1" + ], + "type": "Lookup" } ] } diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_partial_lefsemi.sql-plan_/join_join_to_idx_lookup_partial_lefsemi.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_partial_lefsemi.sql-plan_/join_join_to_idx_lookup_partial_lefsemi.sql.plan index 16429766bc..fb567ac281 100644 --- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_partial_lefsemi.sql-plan_/join_join_to_idx_lookup_partial_lefsemi.sql.plan +++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_partial_lefsemi.sql-plan_/join_join_to_idx_lookup_partial_lefsemi.sql.plan @@ -27,7 +27,10 @@ "columns": [ "Key1" ], - "type": "Scan" + "lookup_by": [ + "Key1" + ], + "type": "Lookup" } ] } diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_partial_left.sql-plan_/join_join_to_idx_lookup_partial_left.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_partial_left.sql-plan_/join_join_to_idx_lookup_partial_left.sql.plan index 7f10521ce1..d06fc759eb 100644 --- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_partial_left.sql-plan_/join_join_to_idx_lookup_partial_left.sql.plan +++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_partial_left.sql-plan_/join_join_to_idx_lookup_partial_left.sql.plan @@ -28,7 +28,10 @@ "Key1", "Value" ], - "type": "Scan" + "lookup_by": [ + "Key1" + ], + "type": "Lookup" } ] }, @@ -40,6 +43,9 @@ "Key", "Value" ], + "lookup_by": [ + "Key" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_predicate_left_null.sql-plan_/join_join_to_idx_lookup_predicate_left_null.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_predicate_left_null.sql-plan_/join_join_to_idx_lookup_predicate_left_null.sql.plan index e61f3066a7..0f52621bae 100644 --- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_predicate_left_null.sql-plan_/join_join_to_idx_lookup_predicate_left_null.sql.plan +++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_predicate_left_null.sql-plan_/join_join_to_idx_lookup_predicate_left_null.sql.plan @@ -27,7 +27,10 @@ "Key1", "Value" ], - "type": "Scan" + "lookup_by": [ + "Key1" + ], + "type": "Lookup" } ] } diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_predicate_right.sql-plan_/join_join_to_idx_lookup_predicate_right.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_predicate_right.sql-plan_/join_join_to_idx_lookup_predicate_right.sql.plan index 8de4469d03..42e9978b4b 100644 --- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_predicate_right.sql-plan_/join_join_to_idx_lookup_predicate_right.sql.plan +++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_predicate_right.sql-plan_/join_join_to_idx_lookup_predicate_right.sql.plan @@ -30,6 +30,10 @@ "Key2", "Value" ], + "lookup_by": [ + "Key1", + "Key2" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_predicate_right_2.sql-plan_/join_join_to_idx_lookup_predicate_right_2.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_predicate_right_2.sql-plan_/join_join_to_idx_lookup_predicate_right_2.sql.plan index 28918880a4..144b7ed9d3 100644 --- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_predicate_right_2.sql-plan_/join_join_to_idx_lookup_predicate_right_2.sql.plan +++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_predicate_right_2.sql-plan_/join_join_to_idx_lookup_predicate_right_2.sql.plan @@ -29,6 +29,10 @@ "Key2", "Value" ], + "lookup_by": [ + "Key1", + "Key2" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_range_left.sql-plan_/join_join_to_idx_lookup_range_left.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_range_left.sql-plan_/join_join_to_idx_lookup_range_left.sql.plan index c101dfe9e4..5f5d16671d 100644 --- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_range_left.sql-plan_/join_join_to_idx_lookup_range_left.sql.plan +++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_range_left.sql-plan_/join_join_to_idx_lookup_range_left.sql.plan @@ -29,6 +29,10 @@ "Key2", "Value" ], + "lookup_by": [ + "Key1", + "Key2" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_right_key_range.sql-plan_/join_join_to_idx_lookup_right_key_range.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_right_key_range.sql-plan_/join_join_to_idx_lookup_right_key_range.sql.plan index 849e8d9dab..de1c9803fc 100644 --- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_right_key_range.sql-plan_/join_join_to_idx_lookup_right_key_range.sql.plan +++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_right_key_range.sql-plan_/join_join_to_idx_lookup_right_key_range.sql.plan @@ -27,6 +27,10 @@ "Key2", "Value" ], + "lookup_by": [ + "Key1", + "Key2" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_simple_c.sql-plan_/join_join_to_idx_lookup_simple_c.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_simple_c.sql-plan_/join_join_to_idx_lookup_simple_c.sql.plan index 7773bbb2e5..f43d16bc6a 100644 --- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_simple_c.sql-plan_/join_join_to_idx_lookup_simple_c.sql.plan +++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_to_idx_lookup_simple_c.sql-plan_/join_join_to_idx_lookup_simple_c.sql.plan @@ -30,6 +30,10 @@ "Key2", "Value" ], + "lookup_by": [ + "Key1", + "Key2" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_using_index.sql-plan_/join_join_using_index.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_using_index.sql-plan_/join_join_using_index.sql.plan index 260cd52696..291c67e6ef 100644 --- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_using_index.sql-plan_/join_join_using_index.sql.plan +++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_using_index.sql-plan_/join_join_using_index.sql.plan @@ -27,6 +27,10 @@ "Fk2", "Value" ], + "lookup_by": [ + "Key1", + "Key2" + ], "type": "Lookup" } ] @@ -39,7 +43,10 @@ "Key1", "Key2" ], - "type": "Scan" + "lookup_by": [ + "Fk2" + ], + "type": "Lookup" } ] } diff --git a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_with_agg.sql-plan_/join_join_with_agg.sql.plan b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_with_agg.sql-plan_/join_join_with_agg.sql.plan index 801dbcd678..dd881584da 100644 --- a/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_with_agg.sql-plan_/join_join_with_agg.sql.plan +++ b/ydb/tests/functional/canonical/canondata/test_sql.TestCanonicalFolder1.test_case_join_join_with_agg.sql-plan_/join_join_with_agg.sql.plan @@ -19,6 +19,12 @@ }, { "columns": [ + "Fk21", + "Fk22", + "Key", + "Value" + ], + "lookup_by": [ "Key" ], "type": "Lookup" diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_coalesce-and-join.test_/query_1.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_coalesce-and-join.test_/query_1.plan index b7928791ae..7965ce6111 100644 --- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_coalesce-and-join.test_/query_1.plan +++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_coalesce-and-join.test_/query_1.plan @@ -4,8 +4,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_4", - "PlanNodeId": 33, + "Node Type": "ResultSet", + "PlanNodeId": 13, "PlanNodeType": "ResultSet", "Plans": [ { @@ -14,27 +14,30 @@ { "Inputs": [ { - "ExternalPlanNodeId": 31 + "ExternalPlanNodeId": 11 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 32, + "PlanNodeId": 12, "Plans": [ { "Node Type": "Merge", - "PlanNodeId": 31, + "PlanNodeId": 11, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "TopSort-LeftJoin (MapJoin)", + "Node Type": "TopSort-TopSort", "Operators": [ { "Inputs": [ { "InternalOperatorId": 1 + }, + { + "ExternalPlanNodeId": 9 } ], "Limit": "1001", @@ -42,90 +45,109 @@ "TopSortBy": "argument.qr.y" }, { - "Condition": "qr._equijoin_column_0 = pv.x", "Inputs": [ { - "ExternalPlanNodeId": 29 - }, - { - "ExternalPlanNodeId": 27 + "ExternalPlanNodeId": 9 } ], - "Name": "LeftJoin (MapJoin)" + "Limit": "1001", + "Name": "TopSort", + "TopSortBy": "argument.qr.y" } ], - "PlanNodeId": 30, + "PlanNodeId": 10, "Plans": [ { - "Node Type": "Map", - "PlanNodeId": 29, - "PlanNodeType": "Connection", - "Plans": [ - { - "CTE Name": "precompute_2_0", - "Node Type": "ConstantExpr", - "Operators": [ - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_2_0" - } - ], - "PlanNodeId": 28 - } - ] - }, - { - "Node Type": "Broadcast", - "PlanNodeId": 27, + "Node Type": "UnionAll", + "PlanNodeId": 9, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "Stage", - "PlanNodeId": 26, + "Node Type": "Collect", + "PlanNodeId": 8, "Plans": [ { - "Node Type": "UnionAll", - "PlanNodeId": 25, + "Columns": [ + "x" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "x" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 7, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_3_0", - "Node Type": "Filter-TablePointLookup-ConstantExpr", - "Operators": [ - { - "Inputs": [ - { - "InternalOperatorId": 1 - } - ], - "Name": "Filter", - "Predicate": "Exist(item.x)" - }, + "Node Type": "Stage", + "PlanNodeId": 6, + "Plans": [ { - "Inputs": [ + "Node Type": "UnionAll", + "PlanNodeId": 5, + "PlanNodeType": "Connection", + "Plans": [ { - "InternalOperatorId": 2 + "Node Type": "Collect", + "PlanNodeId": 4, + "Plans": [ + { + "Columns": [ + "x" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "x" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 3, + "PlanNodeType": "Connection", + "Plans": [ + { + "Node Type": "Stage", + "PlanNodeId": 2, + "Plans": [ + { + "Node Type": "TableFullScan", + "Operators": [ + { + "Inputs": [], + "Name": "TableFullScan", + "ReadColumns": [ + "x", + "y" + ], + "ReadRanges": [ + "x (-\u221e, +\u221e)", + "y (-\u221e, +\u221e)" + ], + "ReadRangesPointPrefixLen": "0", + "Scan": "Parallel", + "Table": "postgres_jointest/coalesce-and-join.test_plan/a" + } + ], + "PlanNodeId": 1, + "Tables": [ + "postgres_jointest/coalesce-and-join.test_plan/a" + ] + } + ] + } + ], + "Table": "postgres_jointest/coalesce-and-join.test_plan/b" + } + ] } - ], - "Name": "TablePointLookup", - "ReadColumns": [ - "x" - ], - "Table": "postgres_jointest/coalesce-and-join.test_plan/b" - }, - { - "Inputs": [], - "Iterator": "precompute_3_0", - "Name": "Iterator" + ] } - ], - "PlanNodeId": 24, - "Tables": [ - "postgres_jointest/coalesce-and-join.test_plan/b" ] } - ] + ], + "Table": "postgres_jointest/coalesce-and-join.test_plan/b" } ] } @@ -141,217 +163,6 @@ ] } ] - }, - { - "Node Type": "Precompute_3", - "Parent Relationship": "InitPlan", - "PlanNodeId": 22, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_2_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_2_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 21 - } - ], - "Subplan Name": "CTE precompute_3_0" - }, - { - "Node Type": "Precompute_2", - "Parent Relationship": "InitPlan", - "PlanNodeId": 19, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 18, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 17, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "LeftJoin (MapJoin)", - "Operators": [ - { - "Condition": "a.x = qt.x", - "Inputs": [ - { - "ExternalPlanNodeId": 15 - }, - { - "ExternalPlanNodeId": 13 - } - ], - "Name": "LeftJoin (MapJoin)" - } - ], - "PlanNodeId": 16, - "Plans": [ - { - "Node Type": "Map", - "PlanNodeId": 15, - "PlanNodeType": "Connection", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "ConstantExpr", - "Operators": [ - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_0_0" - } - ], - "PlanNodeId": 14 - } - ] - }, - { - "Node Type": "Broadcast", - "PlanNodeId": 13, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Stage", - "PlanNodeId": 12, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 11, - "PlanNodeType": "Connection", - "Plans": [ - { - "CTE Name": "precompute_1_0", - "Node Type": "Filter-TablePointLookup-ConstantExpr", - "Operators": [ - { - "Inputs": [ - { - "InternalOperatorId": 1 - } - ], - "Name": "Filter", - "Predicate": "Exist(item.x)" - }, - { - "Inputs": [ - { - "InternalOperatorId": 2 - } - ], - "Name": "TablePointLookup", - "ReadColumns": [ - "x" - ], - "Table": "postgres_jointest/coalesce-and-join.test_plan/b" - }, - { - "Inputs": [], - "Iterator": "precompute_1_0", - "Name": "Iterator" - } - ], - "PlanNodeId": 10, - "Tables": [ - "postgres_jointest/coalesce-and-join.test_plan/b" - ] - } - ] - } - ] - } - ] - } - ] - } - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_2_0" - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 8, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_0_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 7 - } - ], - "Subplan Name": "CTE precompute_1_0" - }, - { - "Node Type": "Precompute_0", - "Parent Relationship": "InitPlan", - "PlanNodeId": 5, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 4, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 3, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 2, - "Plans": [ - { - "Node Type": "TableFullScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableFullScan", - "ReadColumns": [ - "x", - "y" - ], - "ReadRanges": [ - "x (-\u221e, +\u221e)", - "y (-\u221e, +\u221e)" - ], - "ReadRangesPointPrefixLen": "0", - "Scan": "Parallel", - "Table": "postgres_jointest/coalesce-and-join.test_plan/a" - } - ], - "PlanNodeId": 1, - "Tables": [ - "postgres_jointest/coalesce-and-join.test_plan/a" - ] - } - ] - } - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_0_0" } ], "Stats": { @@ -386,12 +197,18 @@ "columns": [ "x" ], + "lookup_by": [ + "x" + ], "type": "Lookup" }, { "columns": [ "x" ], + "lookup_by": [ + "x" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_coalesce-and-join.test_/query_2.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_coalesce-and-join.test_/query_2.plan index c5e4facea7..e176b66871 100644 --- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_coalesce-and-join.test_/query_2.plan +++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_coalesce-and-join.test_/query_2.plan @@ -4,8 +4,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_2", - "PlanNodeId": 19, + "Node Type": "ResultSet", + "PlanNodeId": 9, "PlanNodeType": "ResultSet", "Plans": [ { @@ -14,27 +14,30 @@ { "Inputs": [ { - "ExternalPlanNodeId": 17 + "ExternalPlanNodeId": 7 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 18, + "PlanNodeId": 8, "Plans": [ { "Node Type": "Merge", - "PlanNodeId": 17, + "PlanNodeId": 7, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "TopSort-LeftJoin (MapJoin)", + "Node Type": "TopSort-TopSort", "Operators": [ { "Inputs": [ { "InternalOperatorId": 1 + }, + { + "ExternalPlanNodeId": 5 } ], "Limit": "1001", @@ -42,90 +45,73 @@ "TopSortBy": "argument.a.y" }, { - "Condition": "a.x = pv.x", "Inputs": [ { - "ExternalPlanNodeId": 15 - }, - { - "ExternalPlanNodeId": 13 + "ExternalPlanNodeId": 5 } ], - "Name": "LeftJoin (MapJoin)" + "Limit": "1001", + "Name": "TopSort", + "TopSortBy": "argument.a.y" } ], - "PlanNodeId": 16, + "PlanNodeId": 6, "Plans": [ { - "Node Type": "Map", - "PlanNodeId": 15, + "Node Type": "UnionAll", + "PlanNodeId": 5, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_0_0", - "Node Type": "ConstantExpr", - "Operators": [ - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_0_0" - } - ], - "PlanNodeId": 14 - } - ] - }, - { - "Node Type": "Broadcast", - "PlanNodeId": 13, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Stage", - "PlanNodeId": 12, + "Node Type": "Collect", + "PlanNodeId": 4, "Plans": [ { - "Node Type": "UnionAll", - "PlanNodeId": 11, + "Columns": [ + "x" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "x" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 3, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_1_0", - "Node Type": "Filter-TablePointLookup-ConstantExpr", - "Operators": [ + "Node Type": "Stage", + "PlanNodeId": 2, + "Plans": [ { - "Inputs": [ + "Node Type": "TableFullScan", + "Operators": [ { - "InternalOperatorId": 1 + "Inputs": [], + "Name": "TableFullScan", + "ReadColumns": [ + "x", + "y" + ], + "ReadRanges": [ + "x (-\u221e, +\u221e)", + "y (-\u221e, +\u221e)" + ], + "ReadRangesPointPrefixLen": "0", + "Scan": "Parallel", + "Table": "postgres_jointest/coalesce-and-join.test_plan/a" } ], - "Name": "Filter", - "Predicate": "Exist(item.x)" - }, - { - "Inputs": [ - { - "InternalOperatorId": 2 - } - ], - "Name": "TablePointLookup", - "ReadColumns": [ - "x" - ], - "Table": "postgres_jointest/coalesce-and-join.test_plan/b" - }, - { - "Inputs": [], - "Iterator": "precompute_1_0", - "Name": "Iterator" + "PlanNodeId": 1, + "Tables": [ + "postgres_jointest/coalesce-and-join.test_plan/a" + ] } - ], - "PlanNodeId": 10, - "Tables": [ - "postgres_jointest/coalesce-and-join.test_plan/b" ] } - ] + ], + "Table": "postgres_jointest/coalesce-and-join.test_plan/b" } ] } @@ -141,79 +127,6 @@ ] } ] - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 8, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_0_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 7 - } - ], - "Subplan Name": "CTE precompute_1_0" - }, - { - "Node Type": "Precompute_0", - "Parent Relationship": "InitPlan", - "PlanNodeId": 5, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 4, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 3, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 2, - "Plans": [ - { - "Node Type": "TableFullScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableFullScan", - "ReadColumns": [ - "x", - "y" - ], - "ReadRanges": [ - "x (-\u221e, +\u221e)", - "y (-\u221e, +\u221e)" - ], - "ReadRangesPointPrefixLen": "0", - "Scan": "Parallel", - "Table": "postgres_jointest/coalesce-and-join.test_plan/a" - } - ], - "PlanNodeId": 1, - "Tables": [ - "postgres_jointest/coalesce-and-join.test_plan/a" - ] - } - ] - } - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_0_0" } ], "Stats": { @@ -248,6 +161,9 @@ "columns": [ "x" ], + "lookup_by": [ + "x" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_coalesce-and-join.test_/query_3.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_coalesce-and-join.test_/query_3.plan index b0177e9574..0b02b3e2e7 100644 --- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_coalesce-and-join.test_/query_3.plan +++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_coalesce-and-join.test_/query_3.plan @@ -4,8 +4,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_2", - "PlanNodeId": 19, + "Node Type": "ResultSet", + "PlanNodeId": 9, "PlanNodeType": "ResultSet", "Plans": [ { @@ -14,27 +14,30 @@ { "Inputs": [ { - "ExternalPlanNodeId": 17 + "ExternalPlanNodeId": 7 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 18, + "PlanNodeId": 8, "Plans": [ { "Node Type": "Merge", - "PlanNodeId": 17, + "PlanNodeId": 7, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "TopSort-LeftJoin (MapJoin)", + "Node Type": "TopSort-TopSort", "Operators": [ { "Inputs": [ { "InternalOperatorId": 1 + }, + { + "ExternalPlanNodeId": 5 } ], "Limit": "1001", @@ -42,91 +45,72 @@ "TopSortBy": "argument.pv.x" }, { - "Condition": "pv.x = a.x", "Inputs": [ { - "ExternalPlanNodeId": 15 - }, - { - "ExternalPlanNodeId": 13 + "ExternalPlanNodeId": 5 } ], - "Name": "LeftJoin (MapJoin)" + "Limit": "1001", + "Name": "TopSort", + "TopSortBy": "argument.pv.x" } ], - "PlanNodeId": 16, + "PlanNodeId": 6, "Plans": [ { - "Node Type": "Map", - "PlanNodeId": 15, + "Node Type": "UnionAll", + "PlanNodeId": 5, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_0_0", - "Node Type": "ConstantExpr", - "Operators": [ - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_0_0" - } - ], - "PlanNodeId": 14 - } - ] - }, - { - "Node Type": "Broadcast", - "PlanNodeId": 13, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Stage", - "PlanNodeId": 12, + "Node Type": "Collect", + "PlanNodeId": 4, "Plans": [ { - "Node Type": "UnionAll", - "PlanNodeId": 11, + "Columns": [ + "x", + "y" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "x" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 3, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_1_0", - "Node Type": "Filter-TableRangeScan-ConstantExpr", - "Operators": [ + "Node Type": "Stage", + "PlanNodeId": 2, + "Plans": [ { - "Inputs": [ + "Node Type": "TableFullScan", + "Operators": [ { - "InternalOperatorId": 1 + "Inputs": [], + "Name": "TableFullScan", + "ReadColumns": [ + "x" + ], + "ReadRanges": [ + "x (-\u221e, +\u221e)" + ], + "ReadRangesPointPrefixLen": "0", + "Scan": "Parallel", + "Table": "postgres_jointest/coalesce-and-join.test_plan/b" } ], - "Name": "Filter", - "Predicate": "Exist(item.x)" - }, - { - "Inputs": [ - { - "InternalOperatorId": 2 - } - ], - "Name": "TableRangeScan", - "ReadColumns": [ - "x", - "y" - ], - "Table": "postgres_jointest/coalesce-and-join.test_plan/a" - }, - { - "Inputs": [], - "Iterator": "precompute_1_0", - "Name": "Iterator" + "PlanNodeId": 1, + "Tables": [ + "postgres_jointest/coalesce-and-join.test_plan/b" + ] } - ], - "PlanNodeId": 10, - "Tables": [ - "postgres_jointest/coalesce-and-join.test_plan/a" ] } - ] + ], + "Table": "postgres_jointest/coalesce-and-join.test_plan/a" } ] } @@ -142,77 +126,6 @@ ] } ] - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 8, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_0_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 7 - } - ], - "Subplan Name": "CTE precompute_1_0" - }, - { - "Node Type": "Precompute_0", - "Parent Relationship": "InitPlan", - "PlanNodeId": 5, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 4, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 3, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 2, - "Plans": [ - { - "Node Type": "TableFullScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableFullScan", - "ReadColumns": [ - "x" - ], - "ReadRanges": [ - "x (-\u221e, +\u221e)" - ], - "ReadRangesPointPrefixLen": "0", - "Scan": "Parallel", - "Table": "postgres_jointest/coalesce-and-join.test_plan/b" - } - ], - "PlanNodeId": 1, - "Tables": [ - "postgres_jointest/coalesce-and-join.test_plan/b" - ] - } - ] - } - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_0_0" } ], "Stats": { @@ -232,7 +145,10 @@ "x", "y" ], - "type": "Scan" + "lookup_by": [ + "x" + ], + "type": "Lookup" } ] }, diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_coalesce-and-join.test_/query_4.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_coalesce-and-join.test_/query_4.plan index 4a3117acf7..5943704629 100644 --- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_coalesce-and-join.test_/query_4.plan +++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_coalesce-and-join.test_/query_4.plan @@ -4,8 +4,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_2", - "PlanNodeId": 19, + "Node Type": "ResultSet", + "PlanNodeId": 9, "PlanNodeType": "ResultSet", "Plans": [ { @@ -14,27 +14,30 @@ { "Inputs": [ { - "ExternalPlanNodeId": 17 + "ExternalPlanNodeId": 7 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 18, + "PlanNodeId": 8, "Plans": [ { "Node Type": "Merge", - "PlanNodeId": 17, + "PlanNodeId": 7, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "TopSort-LeftJoin (MapJoin)", + "Node Type": "TopSort-TopSort", "Operators": [ { "Inputs": [ { "InternalOperatorId": 1 + }, + { + "ExternalPlanNodeId": 5 } ], "Limit": "1001", @@ -42,90 +45,73 @@ "TopSortBy": "argument.yy.pkyy" }, { - "Condition": "yy._equijoin_column_0 = xx.pkxx", "Inputs": [ { - "ExternalPlanNodeId": 15 - }, - { - "ExternalPlanNodeId": 13 + "ExternalPlanNodeId": 5 } ], - "Name": "LeftJoin (MapJoin)" + "Limit": "1001", + "Name": "TopSort", + "TopSortBy": "argument.yy.pkyy" } ], - "PlanNodeId": 16, + "PlanNodeId": 6, "Plans": [ { - "Node Type": "Map", - "PlanNodeId": 15, + "Node Type": "UnionAll", + "PlanNodeId": 5, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_0_0", - "Node Type": "ConstantExpr", - "Operators": [ - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_0_0" - } - ], - "PlanNodeId": 14 - } - ] - }, - { - "Node Type": "Broadcast", - "PlanNodeId": 13, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Stage", - "PlanNodeId": 12, + "Node Type": "Collect", + "PlanNodeId": 4, "Plans": [ { - "Node Type": "UnionAll", - "PlanNodeId": 11, + "Columns": [ + "pkxx" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "pkxx" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 3, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_1_0", - "Node Type": "Filter-TablePointLookup-ConstantExpr", - "Operators": [ + "Node Type": "Stage", + "PlanNodeId": 2, + "Plans": [ { - "Inputs": [ + "Node Type": "TableFullScan", + "Operators": [ { - "InternalOperatorId": 1 + "Inputs": [], + "Name": "TableFullScan", + "ReadColumns": [ + "pkxx", + "pkyy" + ], + "ReadRanges": [ + "pkxx (-\u221e, +\u221e)", + "pkyy (-\u221e, +\u221e)" + ], + "ReadRangesPointPrefixLen": "0", + "Scan": "Parallel", + "Table": "postgres_jointest/coalesce-and-join.test_plan/yy" } ], - "Name": "Filter", - "Predicate": "Exist(item.pkxx)" - }, - { - "Inputs": [ - { - "InternalOperatorId": 2 - } - ], - "Name": "TablePointLookup", - "ReadColumns": [ - "pkxx" - ], - "Table": "postgres_jointest/coalesce-and-join.test_plan/xx" - }, - { - "Inputs": [], - "Iterator": "precompute_1_0", - "Name": "Iterator" + "PlanNodeId": 1, + "Tables": [ + "postgres_jointest/coalesce-and-join.test_plan/yy" + ] } - ], - "PlanNodeId": 10, - "Tables": [ - "postgres_jointest/coalesce-and-join.test_plan/xx" ] } - ] + ], + "Table": "postgres_jointest/coalesce-and-join.test_plan/xx" } ] } @@ -141,79 +127,6 @@ ] } ] - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 8, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_0_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 7 - } - ], - "Subplan Name": "CTE precompute_1_0" - }, - { - "Node Type": "Precompute_0", - "Parent Relationship": "InitPlan", - "PlanNodeId": 5, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 4, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 3, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Stage", - "PlanNodeId": 2, - "Plans": [ - { - "Node Type": "TableFullScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableFullScan", - "ReadColumns": [ - "pkxx", - "pkyy" - ], - "ReadRanges": [ - "pkxx (-\u221e, +\u221e)", - "pkyy (-\u221e, +\u221e)" - ], - "ReadRangesPointPrefixLen": "0", - "Scan": "Parallel", - "Table": "postgres_jointest/coalesce-and-join.test_plan/yy" - } - ], - "PlanNodeId": 1, - "Tables": [ - "postgres_jointest/coalesce-and-join.test_plan/yy" - ] - } - ] - } - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_0_0" } ], "Stats": { @@ -232,6 +145,9 @@ "columns": [ "pkxx" ], + "lookup_by": [ + "pkxx" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join-group-by-with-null.test_/query_2.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join-group-by-with-null.test_/query_2.plan index 582445d870..5e7076d612 100644 --- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join-group-by-with-null.test_/query_2.plan +++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join-group-by-with-null.test_/query_2.plan @@ -4,8 +4,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_2", - "PlanNodeId": 21, + "Node Type": "ResultSet", + "PlanNodeId": 11, "PlanNodeType": "ResultSet", "Plans": [ { @@ -14,18 +14,18 @@ { "Inputs": [ { - "ExternalPlanNodeId": 19 + "ExternalPlanNodeId": 9 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 20, + "PlanNodeId": 10, "Plans": [ { "Node Type": "Merge", - "PlanNodeId": 19, + "PlanNodeId": 9, "PlanNodeType": "Connection", "Plans": [ { @@ -34,7 +34,7 @@ { "Inputs": [ { - "ExternalPlanNodeId": 17 + "ExternalPlanNodeId": 7 } ], "Limit": "1001", @@ -42,114 +42,88 @@ "TopSortBy": "argument.t1.q2" } ], - "PlanNodeId": 18, + "PlanNodeId": 8, "Plans": [ { "KeyColumns": [ "t1.q2" ], "Node Type": "HashShuffle", - "PlanNodeId": 17, + "PlanNodeId": 7, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "Aggregate-LeftJoin (MapJoin)", + "Node Type": "Aggregate", "Operators": [ { "Aggregation": "{_yql_agg_0: COUNT(item.t1.q1,state._yql_agg_0)}", "GroupBy": "item.t1.q2", "Inputs": [ { - "InternalOperatorId": 1 + "ExternalPlanNodeId": 5 } ], "Name": "Aggregate" - }, - { - "Condition": "t1.q2 = t2.q1", - "Inputs": [ - { - "ExternalPlanNodeId": 15 - }, - { - "ExternalPlanNodeId": 13 - } - ], - "Name": "LeftJoin (MapJoin)" } ], - "PlanNodeId": 16, + "PlanNodeId": 6, "Plans": [ { - "Node Type": "Map", - "PlanNodeId": 15, - "PlanNodeType": "Connection", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "ConstantExpr", - "Operators": [ - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_0_0" - } - ], - "PlanNodeId": 14 - } - ] - }, - { - "Node Type": "Broadcast", - "PlanNodeId": 13, + "Node Type": "UnionAll", + "PlanNodeId": 5, "PlanNodeType": "Connection", "Plans": [ { "Node Type": "Stage", - "PlanNodeId": 12, + "PlanNodeId": 4, "Plans": [ { - "Node Type": "UnionAll", - "PlanNodeId": 11, + "Columns": [ + "q1", + "q2" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "q1" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 3, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_1_0", - "Node Type": "Filter-TableRangeScan-ConstantExpr", - "Operators": [ + "Node Type": "Stage", + "PlanNodeId": 2, + "Plans": [ { - "Inputs": [ + "Node Type": "TableFullScan", + "Operators": [ { - "InternalOperatorId": 1 + "Inputs": [], + "Name": "TableFullScan", + "ReadColumns": [ + "q1", + "q2" + ], + "ReadRanges": [ + "q1 (-\u221e, +\u221e)", + "q2 (-\u221e, +\u221e)" + ], + "ReadRangesPointPrefixLen": "0", + "Scan": "Parallel", + "Table": "postgres_jointest/join-group-by-with-null.test_plan/int8_tbl" } ], - "Name": "Filter", - "Predicate": "Exist(item.q1)" - }, - { - "Inputs": [ - { - "InternalOperatorId": 2 - } - ], - "Name": "TableRangeScan", - "ReadColumns": [ - "q1" - ], - "Table": "postgres_jointest/join-group-by-with-null.test_plan/int8_tbl" - }, - { - "Inputs": [], - "Iterator": "precompute_1_0", - "Name": "Iterator" + "PlanNodeId": 1, + "Tables": [ + "postgres_jointest/join-group-by-with-null.test_plan/int8_tbl" + ] } - ], - "PlanNodeId": 10, - "Tables": [ - "postgres_jointest/join-group-by-with-null.test_plan/int8_tbl" ] } - ] + ], + "Table": "postgres_jointest/join-group-by-with-null.test_plan/int8_tbl" } ] } @@ -169,79 +143,6 @@ ] } ] - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 8, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_0_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 7 - } - ], - "Subplan Name": "CTE precompute_1_0" - }, - { - "Node Type": "Precompute_0", - "Parent Relationship": "InitPlan", - "PlanNodeId": 5, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 4, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 3, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 2, - "Plans": [ - { - "Node Type": "TableFullScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableFullScan", - "ReadColumns": [ - "q1", - "q2" - ], - "ReadRanges": [ - "q1 (-\u221e, +\u221e)", - "q2 (-\u221e, +\u221e)" - ], - "ReadRangesPointPrefixLen": "0", - "Scan": "Parallel", - "Table": "postgres_jointest/join-group-by-with-null.test_plan/int8_tbl" - } - ], - "PlanNodeId": 1, - "Tables": [ - "postgres_jointest/join-group-by-with-null.test_plan/int8_tbl" - ] - } - ] - } - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_0_0" } ], "Stats": { @@ -269,9 +170,13 @@ }, { "columns": [ + "q1", + "q2" + ], + "lookup_by": [ "q1" ], - "type": "Scan" + "type": "Lookup" } ] } diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join-group-by-with-null.test_/query_3.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join-group-by-with-null.test_/query_3.plan index f3dad04328..f457ccde5f 100644 --- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join-group-by-with-null.test_/query_3.plan +++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join-group-by-with-null.test_/query_3.plan @@ -4,8 +4,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_2", - "PlanNodeId": 21, + "Node Type": "ResultSet", + "PlanNodeId": 11, "PlanNodeType": "ResultSet", "Plans": [ { @@ -14,18 +14,18 @@ { "Inputs": [ { - "ExternalPlanNodeId": 19 + "ExternalPlanNodeId": 9 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 20, + "PlanNodeId": 10, "Plans": [ { "Node Type": "Merge", - "PlanNodeId": 19, + "PlanNodeId": 9, "PlanNodeType": "Connection", "Plans": [ { @@ -34,7 +34,7 @@ { "Inputs": [ { - "ExternalPlanNodeId": 17 + "ExternalPlanNodeId": 7 } ], "Limit": "1001", @@ -42,114 +42,87 @@ "TopSortBy": "argument.t1.q2" } ], - "PlanNodeId": 18, + "PlanNodeId": 8, "Plans": [ { "KeyColumns": [ "t1.q2" ], "Node Type": "HashShuffle", - "PlanNodeId": 17, + "PlanNodeId": 7, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "Aggregate-LeftJoin (MapJoin)", + "Node Type": "Aggregate", "Operators": [ { "Aggregation": "{_yql_agg_0: COUNT(item.t2.q1,state._yql_agg_0)}", "GroupBy": "item.t1.q2", "Inputs": [ { - "InternalOperatorId": 1 + "ExternalPlanNodeId": 5 } ], "Name": "Aggregate" - }, - { - "Condition": "t1.q2 = t2.q1", - "Inputs": [ - { - "ExternalPlanNodeId": 15 - }, - { - "ExternalPlanNodeId": 13 - } - ], - "Name": "LeftJoin (MapJoin)" } ], - "PlanNodeId": 16, + "PlanNodeId": 6, "Plans": [ { - "Node Type": "Map", - "PlanNodeId": 15, - "PlanNodeType": "Connection", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "ConstantExpr", - "Operators": [ - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_0_0" - } - ], - "PlanNodeId": 14 - } - ] - }, - { - "Node Type": "Broadcast", - "PlanNodeId": 13, + "Node Type": "UnionAll", + "PlanNodeId": 5, "PlanNodeType": "Connection", "Plans": [ { "Node Type": "Stage", - "PlanNodeId": 12, + "PlanNodeId": 4, "Plans": [ { - "Node Type": "UnionAll", - "PlanNodeId": 11, + "Columns": [ + "q1", + "q2" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "q1" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 3, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_1_0", - "Node Type": "Filter-TableRangeScan-ConstantExpr", - "Operators": [ + "Node Type": "Stage", + "PlanNodeId": 2, + "Plans": [ { - "Inputs": [ + "Node Type": "TableFullScan", + "Operators": [ { - "InternalOperatorId": 1 + "Inputs": [], + "Name": "TableFullScan", + "ReadColumns": [ + "q2" + ], + "ReadRanges": [ + "q1 (-\u221e, +\u221e)", + "q2 (-\u221e, +\u221e)" + ], + "ReadRangesPointPrefixLen": "0", + "Scan": "Parallel", + "Table": "postgres_jointest/join-group-by-with-null.test_plan/int8_tbl" } ], - "Name": "Filter", - "Predicate": "Exist(item.q1)" - }, - { - "Inputs": [ - { - "InternalOperatorId": 2 - } - ], - "Name": "TableRangeScan", - "ReadColumns": [ - "q1" - ], - "Table": "postgres_jointest/join-group-by-with-null.test_plan/int8_tbl" - }, - { - "Inputs": [], - "Iterator": "precompute_1_0", - "Name": "Iterator" + "PlanNodeId": 1, + "Tables": [ + "postgres_jointest/join-group-by-with-null.test_plan/int8_tbl" + ] } - ], - "PlanNodeId": 10, - "Tables": [ - "postgres_jointest/join-group-by-with-null.test_plan/int8_tbl" ] } - ] + ], + "Table": "postgres_jointest/join-group-by-with-null.test_plan/int8_tbl" } ] } @@ -169,78 +142,6 @@ ] } ] - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 8, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_0_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 7 - } - ], - "Subplan Name": "CTE precompute_1_0" - }, - { - "Node Type": "Precompute_0", - "Parent Relationship": "InitPlan", - "PlanNodeId": 5, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 4, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 3, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 2, - "Plans": [ - { - "Node Type": "TableFullScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableFullScan", - "ReadColumns": [ - "q2" - ], - "ReadRanges": [ - "q1 (-\u221e, +\u221e)", - "q2 (-\u221e, +\u221e)" - ], - "ReadRangesPointPrefixLen": "0", - "Scan": "Parallel", - "Table": "postgres_jointest/join-group-by-with-null.test_plan/int8_tbl" - } - ], - "PlanNodeId": 1, - "Tables": [ - "postgres_jointest/join-group-by-with-null.test_plan/int8_tbl" - ] - } - ] - } - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_0_0" } ], "Stats": { @@ -267,9 +168,13 @@ }, { "columns": [ + "q1", + "q2" + ], + "lookup_by": [ "q1" ], - "type": "Scan" + "type": "Lookup" } ] } diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join0.test_/query_6.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join0.test_/query_6.plan index 7cbcb564c4..5319a8afc7 100644 --- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join0.test_/query_6.plan +++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join0.test_/query_6.plan @@ -4,8 +4,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_2", - "PlanNodeId": 40, + "Node Type": "ResultSet", + "PlanNodeId": 30, "PlanNodeType": "ResultSet", "Plans": [ { @@ -14,22 +14,22 @@ { "Inputs": [ { - "ExternalPlanNodeId": 38 + "ExternalPlanNodeId": 28 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 39, + "PlanNodeId": 29, "Plans": [ { "Node Type": "UnionAll", - "PlanNodeId": 38, + "PlanNodeId": 28, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "Limit-Filter-LeftJoin (MapJoin)", + "Node Type": "Limit-Filter", "Operators": [ { "Inputs": [ @@ -52,282 +52,189 @@ { "Inputs": [ { - "InternalOperatorId": 2 + "ExternalPlanNodeId": 26 } ], "Name": "Filter", "Predicate": "item.t1.stringu1 > item.t2.stringu2" - }, - { - "Condition": "subq1.y1 = t2.unique1", - "Inputs": [ - { - "ExternalPlanNodeId": 36 - }, - { - "ExternalPlanNodeId": 34 - } - ], - "Name": "LeftJoin (MapJoin)" } ], - "PlanNodeId": 37, + "PlanNodeId": 27, "Plans": [ { - "Node Type": "Map", - "PlanNodeId": 36, - "PlanNodeType": "Connection", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "ConstantExpr", - "Operators": [ - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_0_0" - } - ], - "PlanNodeId": 35 - } - ] - }, - { - "Node Type": "Broadcast", - "PlanNodeId": 34, + "Node Type": "UnionAll", + "PlanNodeId": 26, "PlanNodeType": "Connection", "Plans": [ { "Node Type": "Stage", - "PlanNodeId": 33, + "PlanNodeId": 25, "Plans": [ { - "Node Type": "UnionAll", - "PlanNodeId": 32, + "Columns": [ + "even", + "fivethous", + "four", + "hundred", + "odd", + "string4", + "stringu1", + "stringu2", + "ten", + "tenthous", + "thousand", + "twenty", + "two", + "twothousand", + "unique1", + "unique2" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "unique1" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 24, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_1_0", - "Node Type": "Filter-TableRangeScan-ConstantExpr", + "Node Type": "InnerJoin (MapJoin)-Filter", "Operators": [ { + "Condition": "t1.unique2 = subq1.d1", "Inputs": [ { "InternalOperatorId": 1 + }, + { + "ExternalPlanNodeId": 19 } ], - "Name": "Filter", - "Predicate": "Exist(item.unique1)" + "Name": "InnerJoin (MapJoin)" }, { "Inputs": [ { - "InternalOperatorId": 2 + "ExternalPlanNodeId": 22 } ], - "Name": "TableRangeScan", - "ReadColumns": [ - "stringu2", - "unique1" - ], - "Table": "postgres_jointest/join0.test_plan/tenk1" - }, - { - "Inputs": [], - "Iterator": "precompute_1_0", - "Name": "Iterator" + "Name": "Filter", + "Predicate": "Exist(item.unique2)" } ], - "PlanNodeId": 31, - "Tables": [ - "postgres_jointest/join0.test_plan/tenk1" - ] - } - ] - } - ] - } - ] - } - ] - } - ] - } - ] - } - ] - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 29, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_0_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 28 - } - ], - "Subplan Name": "CTE precompute_1_0" - }, - { - "Node Type": "Precompute_0", - "Parent Relationship": "InitPlan", - "PlanNodeId": 26, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 25, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 24, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "InnerJoin (MapJoin)-Filter", - "Operators": [ - { - "Condition": "t1.unique2 = subq1.d1", - "Inputs": [ - { - "InternalOperatorId": 1 - }, - { - "ExternalPlanNodeId": 19 - } - ], - "Name": "InnerJoin (MapJoin)" - }, - { - "Inputs": [ - { - "ExternalPlanNodeId": 22 - } - ], - "Name": "Filter", - "Predicate": "Exist(item.unique2)" - } - ], - "PlanNodeId": 23, - "Plans": [ - { - "Node Type": "Broadcast", - "PlanNodeId": 19, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Stage", - "PlanNodeId": 18, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 17, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "LeftJoin (MapJoin)", - "Operators": [ + "PlanNodeId": 23, + "Plans": [ { - "Condition": "i1.f1 = subq1.v1.x2", - "Inputs": [ - { - "ExternalPlanNodeId": 15 - }, + "Node Type": "Map", + "PlanNodeId": 22, + "PlanNodeType": "Connection", + "Plans": [ { - "ExternalPlanNodeId": 12 + "Node Type": "Filter", + "Operators": [ + { + "Inputs": [ + { + "ExternalPlanNodeId": 20 + } + ], + "Name": "Filter", + "Predicate": "Exist(item.unique2) And item.unique2 < 42" + } + ], + "PlanNodeId": 21, + "Plans": [ + { + "Node Type": "TableFullScan", + "Operators": [ + { + "Inputs": [], + "Name": "TableFullScan", + "ReadColumns": [ + "stringu1", + "unique2" + ], + "ReadRanges": [ + "unique1 (-\u221e, +\u221e)", + "unique2 (-\u221e, +\u221e)" + ], + "ReadRangesPointPrefixLen": "0", + "Scan": "Parallel", + "Table": "postgres_jointest/join0.test_plan/tenk1" + } + ], + "PlanNodeId": 20, + "Tables": [ + "postgres_jointest/join0.test_plan/tenk1" + ] + } + ] } - ], - "Name": "LeftJoin (MapJoin)" - } - ], - "PlanNodeId": 16, - "Plans": [ + ] + }, { "Node Type": "Broadcast", - "PlanNodeId": 12, + "PlanNodeId": 19, "PlanNodeType": "Connection", "Plans": [ { "Node Type": "Stage", - "PlanNodeId": 11, + "PlanNodeId": 18, "Plans": [ { "Node Type": "UnionAll", - "PlanNodeId": 10, + "PlanNodeId": 17, "PlanNodeType": "Connection", "Plans": [ { "Node Type": "LeftJoin (MapJoin)", "Operators": [ { - "Condition": "v1.x1 = v2.y2", + "Condition": "i1.f1 = subq1.v1.x2", "Inputs": [ { - "ExternalPlanNodeId": 8 + "ExternalPlanNodeId": 15 }, { - "ExternalPlanNodeId": 5 + "ExternalPlanNodeId": 12 } ], "Name": "LeftJoin (MapJoin)" } ], - "PlanNodeId": 9, + "PlanNodeId": 16, "Plans": [ { - "Node Type": "Broadcast", - "PlanNodeId": 5, + "Node Type": "Map", + "PlanNodeId": 15, "PlanNodeType": "Connection", "Plans": [ { "Node Type": "Stage", - "PlanNodeId": 4, + "PlanNodeId": 14, "Plans": [ { - "Node Type": "UnionAll", - "PlanNodeId": 3, - "PlanNodeType": "Connection", - "Plans": [ + "Node Type": "TableFullScan", + "Operators": [ { - "Node Type": "Stage", - "PlanNodeId": 2, - "Plans": [ - { - "Node Type": "TableFullScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableFullScan", - "ReadColumns": [ - "y1", - "y2" - ], - "ReadRanges": [ - "y1 (-\u221e, +\u221e)", - "y2 (-\u221e, +\u221e)" - ], - "ReadRangesPointPrefixLen": "0", - "Scan": "Parallel", - "Table": "postgres_jointest/join0.test_plan/ononequery2" - } - ], - "PlanNodeId": 1, - "Tables": [ - "postgres_jointest/join0.test_plan/ononequery2" - ] - } - ] + "Inputs": [], + "Name": "TableFullScan", + "ReadColumns": [ + "f1" + ], + "ReadRanges": [ + "f1 (-\u221e, +\u221e)" + ], + "ReadRangesPointPrefixLen": "0", + "Scan": "Parallel", + "Table": "postgres_jointest/join0.test_plan/int4_tbl" } + ], + "PlanNodeId": 13, + "Tables": [ + "postgres_jointest/join0.test_plan/int4_tbl" ] } ] @@ -335,36 +242,126 @@ ] }, { - "Node Type": "Map", - "PlanNodeId": 8, + "Node Type": "Broadcast", + "PlanNodeId": 12, "PlanNodeType": "Connection", "Plans": [ { "Node Type": "Stage", - "PlanNodeId": 7, + "PlanNodeId": 11, "Plans": [ { - "Node Type": "TableFullScan", - "Operators": [ + "Node Type": "UnionAll", + "PlanNodeId": 10, + "PlanNodeType": "Connection", + "Plans": [ { - "Inputs": [], - "Name": "TableFullScan", - "ReadColumns": [ - "x1", - "x2" - ], - "ReadRanges": [ - "x1 (-\u221e, +\u221e)", - "x2 (-\u221e, +\u221e)" + "Node Type": "LeftJoin (MapJoin)", + "Operators": [ + { + "Condition": "v1.x1 = v2.y2", + "Inputs": [ + { + "ExternalPlanNodeId": 8 + }, + { + "ExternalPlanNodeId": 5 + } + ], + "Name": "LeftJoin (MapJoin)" + } ], - "ReadRangesPointPrefixLen": "0", - "Scan": "Parallel", - "Table": "postgres_jointest/join0.test_plan/ononequery1" + "PlanNodeId": 9, + "Plans": [ + { + "Node Type": "Map", + "PlanNodeId": 8, + "PlanNodeType": "Connection", + "Plans": [ + { + "Node Type": "Stage", + "PlanNodeId": 7, + "Plans": [ + { + "Node Type": "TableFullScan", + "Operators": [ + { + "Inputs": [], + "Name": "TableFullScan", + "ReadColumns": [ + "x1", + "x2" + ], + "ReadRanges": [ + "x1 (-\u221e, +\u221e)", + "x2 (-\u221e, +\u221e)" + ], + "ReadRangesPointPrefixLen": "0", + "Scan": "Parallel", + "Table": "postgres_jointest/join0.test_plan/ononequery1" + } + ], + "PlanNodeId": 6, + "Tables": [ + "postgres_jointest/join0.test_plan/ononequery1" + ] + } + ] + } + ] + }, + { + "Node Type": "Broadcast", + "PlanNodeId": 5, + "PlanNodeType": "Connection", + "Plans": [ + { + "Node Type": "Stage", + "PlanNodeId": 4, + "Plans": [ + { + "Node Type": "UnionAll", + "PlanNodeId": 3, + "PlanNodeType": "Connection", + "Plans": [ + { + "Node Type": "Stage", + "PlanNodeId": 2, + "Plans": [ + { + "Node Type": "TableFullScan", + "Operators": [ + { + "Inputs": [], + "Name": "TableFullScan", + "ReadColumns": [ + "y1", + "y2" + ], + "ReadRanges": [ + "y1 (-\u221e, +\u221e)", + "y2 (-\u221e, +\u221e)" + ], + "ReadRangesPointPrefixLen": "0", + "Scan": "Parallel", + "Table": "postgres_jointest/join0.test_plan/ononequery2" + } + ], + "PlanNodeId": 1, + "Tables": [ + "postgres_jointest/join0.test_plan/ononequery2" + ] + } + ] + } + ] + } + ] + } + ] + } + ] } - ], - "PlanNodeId": 6, - "Tables": [ - "postgres_jointest/join0.test_plan/ononequery1" ] } ] @@ -378,93 +375,11 @@ ] } ] - }, - { - "Node Type": "Map", - "PlanNodeId": 15, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Stage", - "PlanNodeId": 14, - "Plans": [ - { - "Node Type": "TableFullScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableFullScan", - "ReadColumns": [ - "f1" - ], - "ReadRanges": [ - "f1 (-\u221e, +\u221e)" - ], - "ReadRangesPointPrefixLen": "0", - "Scan": "Parallel", - "Table": "postgres_jointest/join0.test_plan/int4_tbl" - } - ], - "PlanNodeId": 13, - "Tables": [ - "postgres_jointest/join0.test_plan/int4_tbl" - ] - } - ] - } - ] } ] } - ] - } - ] - } - ] - }, - { - "Node Type": "Map", - "PlanNodeId": 22, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Filter", - "Operators": [ - { - "Inputs": [ - { - "ExternalPlanNodeId": 20 - } - ], - "Name": "Filter", - "Predicate": "Exist(item.unique2) And item.unique2 < 42" - } - ], - "PlanNodeId": 21, - "Plans": [ - { - "Node Type": "TableFullScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableFullScan", - "ReadColumns": [ - "stringu1", - "unique2" - ], - "ReadRanges": [ - "unique1 (-\u221e, +\u221e)", - "unique2 (-\u221e, +\u221e)" - ], - "ReadRangesPointPrefixLen": "0", - "Scan": "Parallel", - "Table": "postgres_jointest/join0.test_plan/tenk1" - } ], - "PlanNodeId": 20, - "Tables": [ - "postgres_jointest/join0.test_plan/tenk1" - ] + "Table": "postgres_jointest/join0.test_plan/tenk1" } ] } @@ -476,8 +391,7 @@ } ] } - ], - "Subplan Name": "CTE precompute_0_0" + ] } ], "Stats": { @@ -551,10 +465,27 @@ }, { "columns": [ + "even", + "fivethous", + "four", + "hundred", + "odd", + "string4", + "stringu1", "stringu2", + "ten", + "tenthous", + "thousand", + "twenty", + "two", + "twothousand", + "unique1", + "unique2" + ], + "lookup_by": [ "unique1" ], - "type": "Scan" + "type": "Lookup" } ] } diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_1.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_1.plan index 87a8713ef4..45a0b61990 100644 --- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_1.plan +++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_1.plan @@ -4,8 +4,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_2", - "PlanNodeId": 13, + "Node Type": "ResultSet", + "PlanNodeId": 9, "PlanNodeType": "ResultSet", "Plans": [ { @@ -14,28 +14,30 @@ { "Inputs": [ { - "ExternalPlanNodeId": 11 + "ExternalPlanNodeId": 7 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 12, + "PlanNodeId": 8, "Plans": [ { "Node Type": "Merge", - "PlanNodeId": 11, + "PlanNodeId": 7, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_1_0", - "Node Type": "TopSort-InnerJoin (MapJoin)-ConstantExpr-Filter-TableRangeScan-ConstantExpr", + "Node Type": "TopSort-TopSort", "Operators": [ { "Inputs": [ { "InternalOperatorId": 1 + }, + { + "ExternalPlanNodeId": 5 } ], "Limit": "1001", @@ -43,53 +45,79 @@ "TopSortBy": "" }, { - "Condition": "t2.i = t1.i", "Inputs": [ { - "InternalOperatorId": 3 - }, - { - "InternalOperatorId": 2 + "ExternalPlanNodeId": 5 } ], - "Name": "InnerJoin (MapJoin)" - }, - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_0_0" - }, - { - "Inputs": [ - { - "InternalOperatorId": 4 - } - ], - "Name": "Filter", - "Predicate": "Exist(item.i)" - }, + "Limit": "1001", + "Name": "TopSort", + "TopSortBy": "" + } + ], + "PlanNodeId": 6, + "Plans": [ { - "Inputs": [ + "Node Type": "UnionAll", + "PlanNodeId": 5, + "PlanNodeType": "Connection", + "Plans": [ { - "InternalOperatorId": 5 + "Node Type": "Collect", + "PlanNodeId": 4, + "Plans": [ + { + "Columns": [ + "i", + "k" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "i" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 3, + "PlanNodeType": "Connection", + "Plans": [ + { + "Node Type": "Stage", + "PlanNodeId": 2, + "Plans": [ + { + "Node Type": "TableFullScan", + "Operators": [ + { + "Inputs": [], + "Name": "TableFullScan", + "ReadColumns": [ + "i" + ], + "ReadRanges": [ + "i (-\u221e, +\u221e)", + "j (-\u221e, +\u221e)", + "t (-\u221e, +\u221e)" + ], + "ReadRangesPointPrefixLen": "0", + "Scan": "Parallel", + "Table": "postgres_jointest/join1.test_plan/J1_TBL" + } + ], + "PlanNodeId": 1, + "Tables": [ + "postgres_jointest/join1.test_plan/J1_TBL" + ] + } + ] + } + ], + "Table": "postgres_jointest/join1.test_plan/J2_TBL" + } + ] } - ], - "Name": "TableRangeScan", - "ReadColumns": [ - "i", - "k" - ], - "Table": "postgres_jointest/join1.test_plan/J2_TBL" - }, - { - "Inputs": [], - "Iterator": "precompute_1_0", - "Name": "Iterator" + ] } - ], - "PlanNodeId": 10, - "Tables": [ - "postgres_jointest/join1.test_plan/J2_TBL" ] } ], @@ -101,79 +129,6 @@ ] } ] - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 8, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_0_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 7 - } - ], - "Subplan Name": "CTE precompute_1_0" - }, - { - "Node Type": "Precompute_0", - "Parent Relationship": "InitPlan", - "PlanNodeId": 5, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 4, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 3, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 2, - "Plans": [ - { - "Node Type": "TableFullScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableFullScan", - "ReadColumns": [ - "i" - ], - "ReadRanges": [ - "i (-\u221e, +\u221e)", - "j (-\u221e, +\u221e)", - "t (-\u221e, +\u221e)" - ], - "ReadRangesPointPrefixLen": "0", - "Scan": "Parallel", - "Table": "postgres_jointest/join1.test_plan/J1_TBL" - } - ], - "PlanNodeId": 1, - "Tables": [ - "postgres_jointest/join1.test_plan/J1_TBL" - ] - } - ] - } - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_0_0" } ], "Stats": { @@ -209,7 +164,10 @@ "i", "k" ], - "type": "Scan" + "lookup_by": [ + "i" + ], + "type": "Lookup" } ] } diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_13.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_13.plan index 16524dab1e..0d1571bbda 100644 --- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_13.plan +++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_13.plan @@ -4,8 +4,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_2", - "PlanNodeId": 19, + "Node Type": "ResultSet", + "PlanNodeId": 9, "PlanNodeType": "ResultSet", "Plans": [ { @@ -14,22 +14,22 @@ { "Inputs": [ { - "ExternalPlanNodeId": 17 + "ExternalPlanNodeId": 7 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 18, + "PlanNodeId": 8, "Plans": [ { "Node Type": "UnionAll", - "PlanNodeId": 17, + "PlanNodeId": 7, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "Limit-Filter-LeftJoin (MapJoin)", + "Node Type": "Limit-Filter", "Operators": [ { "Inputs": [ @@ -52,98 +52,73 @@ { "Inputs": [ { - "InternalOperatorId": 2 + "ExternalPlanNodeId": 5 } ], "Name": "Filter", "Predicate": "item.J2_TBL.k == 1" - }, - { - "Condition": "J1_TBL.i = J2_TBL.i", - "Inputs": [ - { - "ExternalPlanNodeId": 15 - }, - { - "ExternalPlanNodeId": 13 - } - ], - "Name": "LeftJoin (MapJoin)" } ], - "PlanNodeId": 16, + "PlanNodeId": 6, "Plans": [ { - "Node Type": "Map", - "PlanNodeId": 15, + "Node Type": "UnionAll", + "PlanNodeId": 5, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_0_0", - "Node Type": "ConstantExpr", - "Operators": [ - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_0_0" - } - ], - "PlanNodeId": 14 - } - ] - }, - { - "Node Type": "Broadcast", - "PlanNodeId": 13, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Stage", - "PlanNodeId": 12, + "Node Type": "Collect", + "PlanNodeId": 4, "Plans": [ { - "Node Type": "UnionAll", - "PlanNodeId": 11, + "Columns": [ + "i", + "k" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "i" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 3, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_1_0", - "Node Type": "Filter-TableRangeScan-ConstantExpr", - "Operators": [ - { - "Inputs": [ - { - "InternalOperatorId": 1 - } - ], - "Name": "Filter", - "Predicate": "Exist(item.i)" - }, + "Node Type": "Stage", + "PlanNodeId": 2, + "Plans": [ { - "Inputs": [ + "Node Type": "TableFullScan", + "Operators": [ { - "InternalOperatorId": 2 + "Inputs": [], + "Name": "TableFullScan", + "ReadColumns": [ + "i", + "j", + "t" + ], + "ReadRanges": [ + "i (-\u221e, +\u221e)", + "j (-\u221e, +\u221e)", + "t (-\u221e, +\u221e)" + ], + "ReadRangesPointPrefixLen": "0", + "Scan": "Parallel", + "Table": "postgres_jointest/join1.test_plan/J1_TBL" } ], - "Name": "TableRangeScan", - "ReadColumns": [ - "i", - "k" - ], - "Table": "postgres_jointest/join1.test_plan/J2_TBL" - }, - { - "Inputs": [], - "Iterator": "precompute_1_0", - "Name": "Iterator" + "PlanNodeId": 1, + "Tables": [ + "postgres_jointest/join1.test_plan/J1_TBL" + ] } - ], - "PlanNodeId": 10, - "Tables": [ - "postgres_jointest/join1.test_plan/J2_TBL" ] } - ] + ], + "Table": "postgres_jointest/join1.test_plan/J2_TBL" } ] } @@ -156,81 +131,6 @@ ] } ] - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 8, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_0_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 7 - } - ], - "Subplan Name": "CTE precompute_1_0" - }, - { - "Node Type": "Precompute_0", - "Parent Relationship": "InitPlan", - "PlanNodeId": 5, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 4, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 3, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 2, - "Plans": [ - { - "Node Type": "TableFullScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableFullScan", - "ReadColumns": [ - "i", - "j", - "t" - ], - "ReadRanges": [ - "i (-\u221e, +\u221e)", - "j (-\u221e, +\u221e)", - "t (-\u221e, +\u221e)" - ], - "ReadRangesPointPrefixLen": "0", - "Scan": "Parallel", - "Table": "postgres_jointest/join1.test_plan/J1_TBL" - } - ], - "PlanNodeId": 1, - "Tables": [ - "postgres_jointest/join1.test_plan/J1_TBL" - ] - } - ] - } - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_0_0" } ], "Stats": { @@ -268,7 +168,10 @@ "i", "k" ], - "type": "Scan" + "lookup_by": [ + "i" + ], + "type": "Lookup" } ] } diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_14.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_14.plan index 170a32b33c..5823923e8b 100644 --- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_14.plan +++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_14.plan @@ -4,8 +4,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_2", - "PlanNodeId": 19, + "Node Type": "ResultSet", + "PlanNodeId": 9, "PlanNodeType": "ResultSet", "Plans": [ { @@ -14,118 +14,92 @@ { "Inputs": [ { - "ExternalPlanNodeId": 17 + "ExternalPlanNodeId": 7 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 18, + "PlanNodeId": 8, "Plans": [ { "Node Type": "UnionAll", - "PlanNodeId": 17, + "PlanNodeId": 7, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "Limit-LeftJoin (MapJoin)", + "Node Type": "Limit", "Operators": [ { "Inputs": [ { - "InternalOperatorId": 1 + "ExternalPlanNodeId": 5 } ], "Limit": "1001", "Name": "Limit" - }, - { - "Condition": "J1_TBL.i = J2_TBL.i", - "Inputs": [ - { - "ExternalPlanNodeId": 15 - }, - { - "ExternalPlanNodeId": 13 - } - ], - "Name": "LeftJoin (MapJoin)" } ], - "PlanNodeId": 16, + "PlanNodeId": 6, "Plans": [ { - "Node Type": "Map", - "PlanNodeId": 15, - "PlanNodeType": "Connection", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "ConstantExpr", - "Operators": [ - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_0_0" - } - ], - "PlanNodeId": 14 - } - ] - }, - { - "Node Type": "Broadcast", - "PlanNodeId": 13, + "Node Type": "UnionAll", + "PlanNodeId": 5, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "Stage", - "PlanNodeId": 12, + "Node Type": "Collect", + "PlanNodeId": 4, "Plans": [ { - "Node Type": "UnionAll", - "PlanNodeId": 11, + "Columns": [ + "i", + "k" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "i" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 3, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_1_0", - "Node Type": "Filter-TableRangeScan-ConstantExpr", - "Operators": [ + "Node Type": "Stage", + "PlanNodeId": 2, + "Plans": [ { - "Inputs": [ + "Node Type": "TableRangeScan", + "Operators": [ { - "InternalOperatorId": 1 + "Inputs": [], + "Name": "TableRangeScan", + "ReadColumns": [ + "i", + "j", + "t" + ], + "ReadRange": [ + "i (1)", + "j (-\u221e, +\u221e)", + "t (-\u221e, +\u221e)" + ], + "Scan": "Parallel", + "Table": "postgres_jointest/join1.test_plan/J1_TBL" } ], - "Name": "Filter", - "Predicate": "Exist(item.i)" - }, - { - "Inputs": [ - { - "InternalOperatorId": 2 - } - ], - "Name": "TableRangeScan", - "ReadColumns": [ - "i", - "k" - ], - "Table": "postgres_jointest/join1.test_plan/J2_TBL" - }, - { - "Inputs": [], - "Iterator": "precompute_1_0", - "Name": "Iterator" + "PlanNodeId": 1, + "Tables": [ + "postgres_jointest/join1.test_plan/J1_TBL" + ] } - ], - "PlanNodeId": 10, - "Tables": [ - "postgres_jointest/join1.test_plan/J2_TBL" ] } - ] + ], + "Table": "postgres_jointest/join1.test_plan/J2_TBL" } ] } @@ -138,80 +112,6 @@ ] } ] - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 8, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_0_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 7 - } - ], - "Subplan Name": "CTE precompute_1_0" - }, - { - "Node Type": "Precompute_0", - "Parent Relationship": "InitPlan", - "PlanNodeId": 5, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 4, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 3, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 2, - "Plans": [ - { - "Node Type": "TableRangeScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableRangeScan", - "ReadColumns": [ - "i", - "j", - "t" - ], - "ReadRange": [ - "i (1)", - "j (-\u221e, +\u221e)", - "t (-\u221e, +\u221e)" - ], - "Scan": "Parallel", - "Table": "postgres_jointest/join1.test_plan/J1_TBL" - } - ], - "PlanNodeId": 1, - "Tables": [ - "postgres_jointest/join1.test_plan/J1_TBL" - ] - } - ] - } - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_0_0" } ], "Stats": { @@ -251,7 +151,10 @@ "i", "k" ], - "type": "Scan" + "lookup_by": [ + "i" + ], + "type": "Lookup" } ] } diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_2.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_2.plan index b93a929eaf..79cd8c5e26 100644 --- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_2.plan +++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_2.plan @@ -4,8 +4,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_2", - "PlanNodeId": 13, + "Node Type": "ResultSet", + "PlanNodeId": 9, "PlanNodeType": "ResultSet", "Plans": [ { @@ -14,28 +14,30 @@ { "Inputs": [ { - "ExternalPlanNodeId": 11 + "ExternalPlanNodeId": 7 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 12, + "PlanNodeId": 8, "Plans": [ { "Node Type": "Merge", - "PlanNodeId": 11, + "PlanNodeId": 7, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_1_0", - "Node Type": "TopSort-InnerJoin (MapJoin)-ConstantExpr-Filter-TableRangeScan-ConstantExpr", + "Node Type": "TopSort-TopSort", "Operators": [ { "Inputs": [ { "InternalOperatorId": 1 + }, + { + "ExternalPlanNodeId": 5 } ], "Limit": "1001", @@ -43,53 +45,81 @@ "TopSortBy": "" }, { - "Condition": "J2_TBL.i = J1_TBL.i", - "Inputs": [ - { - "InternalOperatorId": 3 - }, - { - "InternalOperatorId": 2 - } - ], - "Name": "InnerJoin (MapJoin)" - }, - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_0_0" - }, - { "Inputs": [ { - "InternalOperatorId": 4 + "ExternalPlanNodeId": 5 } ], - "Name": "Filter", - "Predicate": "Exist(item.i)" - }, + "Limit": "1001", + "Name": "TopSort", + "TopSortBy": "" + } + ], + "PlanNodeId": 6, + "Plans": [ { - "Inputs": [ + "Node Type": "UnionAll", + "PlanNodeId": 5, + "PlanNodeType": "Connection", + "Plans": [ { - "InternalOperatorId": 5 + "Node Type": "Collect", + "PlanNodeId": 4, + "Plans": [ + { + "Columns": [ + "i", + "k" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "i" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 3, + "PlanNodeType": "Connection", + "Plans": [ + { + "Node Type": "Stage", + "PlanNodeId": 2, + "Plans": [ + { + "Node Type": "TableFullScan", + "Operators": [ + { + "Inputs": [], + "Name": "TableFullScan", + "ReadColumns": [ + "i", + "j", + "t" + ], + "ReadRanges": [ + "i (-\u221e, +\u221e)", + "j (-\u221e, +\u221e)", + "t (-\u221e, +\u221e)" + ], + "ReadRangesPointPrefixLen": "0", + "Scan": "Parallel", + "Table": "postgres_jointest/join1.test_plan/J1_TBL" + } + ], + "PlanNodeId": 1, + "Tables": [ + "postgres_jointest/join1.test_plan/J1_TBL" + ] + } + ] + } + ], + "Table": "postgres_jointest/join1.test_plan/J2_TBL" + } + ] } - ], - "Name": "TableRangeScan", - "ReadColumns": [ - "i", - "k" - ], - "Table": "postgres_jointest/join1.test_plan/J2_TBL" - }, - { - "Inputs": [], - "Iterator": "precompute_1_0", - "Name": "Iterator" + ] } - ], - "PlanNodeId": 10, - "Tables": [ - "postgres_jointest/join1.test_plan/J2_TBL" ] } ], @@ -103,81 +133,6 @@ ] } ] - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 8, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_0_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 7 - } - ], - "Subplan Name": "CTE precompute_1_0" - }, - { - "Node Type": "Precompute_0", - "Parent Relationship": "InitPlan", - "PlanNodeId": 5, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 4, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 3, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 2, - "Plans": [ - { - "Node Type": "TableFullScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableFullScan", - "ReadColumns": [ - "i", - "j", - "t" - ], - "ReadRanges": [ - "i (-\u221e, +\u221e)", - "j (-\u221e, +\u221e)", - "t (-\u221e, +\u221e)" - ], - "ReadRangesPointPrefixLen": "0", - "Scan": "Parallel", - "Table": "postgres_jointest/join1.test_plan/J1_TBL" - } - ], - "PlanNodeId": 1, - "Tables": [ - "postgres_jointest/join1.test_plan/J1_TBL" - ] - } - ] - } - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_0_0" } ], "Stats": { @@ -215,7 +170,10 @@ "i", "k" ], - "type": "Scan" + "lookup_by": [ + "i" + ], + "type": "Lookup" } ] } diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_3.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_3.plan index b93a929eaf..79cd8c5e26 100644 --- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_3.plan +++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_3.plan @@ -4,8 +4,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_2", - "PlanNodeId": 13, + "Node Type": "ResultSet", + "PlanNodeId": 9, "PlanNodeType": "ResultSet", "Plans": [ { @@ -14,28 +14,30 @@ { "Inputs": [ { - "ExternalPlanNodeId": 11 + "ExternalPlanNodeId": 7 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 12, + "PlanNodeId": 8, "Plans": [ { "Node Type": "Merge", - "PlanNodeId": 11, + "PlanNodeId": 7, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_1_0", - "Node Type": "TopSort-InnerJoin (MapJoin)-ConstantExpr-Filter-TableRangeScan-ConstantExpr", + "Node Type": "TopSort-TopSort", "Operators": [ { "Inputs": [ { "InternalOperatorId": 1 + }, + { + "ExternalPlanNodeId": 5 } ], "Limit": "1001", @@ -43,53 +45,81 @@ "TopSortBy": "" }, { - "Condition": "J2_TBL.i = J1_TBL.i", - "Inputs": [ - { - "InternalOperatorId": 3 - }, - { - "InternalOperatorId": 2 - } - ], - "Name": "InnerJoin (MapJoin)" - }, - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_0_0" - }, - { "Inputs": [ { - "InternalOperatorId": 4 + "ExternalPlanNodeId": 5 } ], - "Name": "Filter", - "Predicate": "Exist(item.i)" - }, + "Limit": "1001", + "Name": "TopSort", + "TopSortBy": "" + } + ], + "PlanNodeId": 6, + "Plans": [ { - "Inputs": [ + "Node Type": "UnionAll", + "PlanNodeId": 5, + "PlanNodeType": "Connection", + "Plans": [ { - "InternalOperatorId": 5 + "Node Type": "Collect", + "PlanNodeId": 4, + "Plans": [ + { + "Columns": [ + "i", + "k" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "i" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 3, + "PlanNodeType": "Connection", + "Plans": [ + { + "Node Type": "Stage", + "PlanNodeId": 2, + "Plans": [ + { + "Node Type": "TableFullScan", + "Operators": [ + { + "Inputs": [], + "Name": "TableFullScan", + "ReadColumns": [ + "i", + "j", + "t" + ], + "ReadRanges": [ + "i (-\u221e, +\u221e)", + "j (-\u221e, +\u221e)", + "t (-\u221e, +\u221e)" + ], + "ReadRangesPointPrefixLen": "0", + "Scan": "Parallel", + "Table": "postgres_jointest/join1.test_plan/J1_TBL" + } + ], + "PlanNodeId": 1, + "Tables": [ + "postgres_jointest/join1.test_plan/J1_TBL" + ] + } + ] + } + ], + "Table": "postgres_jointest/join1.test_plan/J2_TBL" + } + ] } - ], - "Name": "TableRangeScan", - "ReadColumns": [ - "i", - "k" - ], - "Table": "postgres_jointest/join1.test_plan/J2_TBL" - }, - { - "Inputs": [], - "Iterator": "precompute_1_0", - "Name": "Iterator" + ] } - ], - "PlanNodeId": 10, - "Tables": [ - "postgres_jointest/join1.test_plan/J2_TBL" ] } ], @@ -103,81 +133,6 @@ ] } ] - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 8, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_0_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 7 - } - ], - "Subplan Name": "CTE precompute_1_0" - }, - { - "Node Type": "Precompute_0", - "Parent Relationship": "InitPlan", - "PlanNodeId": 5, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 4, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 3, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 2, - "Plans": [ - { - "Node Type": "TableFullScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableFullScan", - "ReadColumns": [ - "i", - "j", - "t" - ], - "ReadRanges": [ - "i (-\u221e, +\u221e)", - "j (-\u221e, +\u221e)", - "t (-\u221e, +\u221e)" - ], - "ReadRangesPointPrefixLen": "0", - "Scan": "Parallel", - "Table": "postgres_jointest/join1.test_plan/J1_TBL" - } - ], - "PlanNodeId": 1, - "Tables": [ - "postgres_jointest/join1.test_plan/J1_TBL" - ] - } - ] - } - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_0_0" } ], "Stats": { @@ -215,7 +170,10 @@ "i", "k" ], - "type": "Scan" + "lookup_by": [ + "i" + ], + "type": "Lookup" } ] } diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_5.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_5.plan index b93a929eaf..79cd8c5e26 100644 --- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_5.plan +++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_5.plan @@ -4,8 +4,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_2", - "PlanNodeId": 13, + "Node Type": "ResultSet", + "PlanNodeId": 9, "PlanNodeType": "ResultSet", "Plans": [ { @@ -14,28 +14,30 @@ { "Inputs": [ { - "ExternalPlanNodeId": 11 + "ExternalPlanNodeId": 7 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 12, + "PlanNodeId": 8, "Plans": [ { "Node Type": "Merge", - "PlanNodeId": 11, + "PlanNodeId": 7, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_1_0", - "Node Type": "TopSort-InnerJoin (MapJoin)-ConstantExpr-Filter-TableRangeScan-ConstantExpr", + "Node Type": "TopSort-TopSort", "Operators": [ { "Inputs": [ { "InternalOperatorId": 1 + }, + { + "ExternalPlanNodeId": 5 } ], "Limit": "1001", @@ -43,53 +45,81 @@ "TopSortBy": "" }, { - "Condition": "J2_TBL.i = J1_TBL.i", - "Inputs": [ - { - "InternalOperatorId": 3 - }, - { - "InternalOperatorId": 2 - } - ], - "Name": "InnerJoin (MapJoin)" - }, - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_0_0" - }, - { "Inputs": [ { - "InternalOperatorId": 4 + "ExternalPlanNodeId": 5 } ], - "Name": "Filter", - "Predicate": "Exist(item.i)" - }, + "Limit": "1001", + "Name": "TopSort", + "TopSortBy": "" + } + ], + "PlanNodeId": 6, + "Plans": [ { - "Inputs": [ + "Node Type": "UnionAll", + "PlanNodeId": 5, + "PlanNodeType": "Connection", + "Plans": [ { - "InternalOperatorId": 5 + "Node Type": "Collect", + "PlanNodeId": 4, + "Plans": [ + { + "Columns": [ + "i", + "k" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "i" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 3, + "PlanNodeType": "Connection", + "Plans": [ + { + "Node Type": "Stage", + "PlanNodeId": 2, + "Plans": [ + { + "Node Type": "TableFullScan", + "Operators": [ + { + "Inputs": [], + "Name": "TableFullScan", + "ReadColumns": [ + "i", + "j", + "t" + ], + "ReadRanges": [ + "i (-\u221e, +\u221e)", + "j (-\u221e, +\u221e)", + "t (-\u221e, +\u221e)" + ], + "ReadRangesPointPrefixLen": "0", + "Scan": "Parallel", + "Table": "postgres_jointest/join1.test_plan/J1_TBL" + } + ], + "PlanNodeId": 1, + "Tables": [ + "postgres_jointest/join1.test_plan/J1_TBL" + ] + } + ] + } + ], + "Table": "postgres_jointest/join1.test_plan/J2_TBL" + } + ] } - ], - "Name": "TableRangeScan", - "ReadColumns": [ - "i", - "k" - ], - "Table": "postgres_jointest/join1.test_plan/J2_TBL" - }, - { - "Inputs": [], - "Iterator": "precompute_1_0", - "Name": "Iterator" + ] } - ], - "PlanNodeId": 10, - "Tables": [ - "postgres_jointest/join1.test_plan/J2_TBL" ] } ], @@ -103,81 +133,6 @@ ] } ] - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 8, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_0_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 7 - } - ], - "Subplan Name": "CTE precompute_1_0" - }, - { - "Node Type": "Precompute_0", - "Parent Relationship": "InitPlan", - "PlanNodeId": 5, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 4, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 3, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 2, - "Plans": [ - { - "Node Type": "TableFullScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableFullScan", - "ReadColumns": [ - "i", - "j", - "t" - ], - "ReadRanges": [ - "i (-\u221e, +\u221e)", - "j (-\u221e, +\u221e)", - "t (-\u221e, +\u221e)" - ], - "ReadRangesPointPrefixLen": "0", - "Scan": "Parallel", - "Table": "postgres_jointest/join1.test_plan/J1_TBL" - } - ], - "PlanNodeId": 1, - "Tables": [ - "postgres_jointest/join1.test_plan/J1_TBL" - ] - } - ] - } - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_0_0" } ], "Stats": { @@ -215,7 +170,10 @@ "i", "k" ], - "type": "Scan" + "lookup_by": [ + "i" + ], + "type": "Lookup" } ] } diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_7.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_7.plan index b572c17132..d15534020a 100644 --- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_7.plan +++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_7.plan @@ -4,8 +4,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_2", - "PlanNodeId": 19, + "Node Type": "ResultSet", + "PlanNodeId": 9, "PlanNodeType": "ResultSet", "Plans": [ { @@ -14,27 +14,30 @@ { "Inputs": [ { - "ExternalPlanNodeId": 17 + "ExternalPlanNodeId": 7 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 18, + "PlanNodeId": 8, "Plans": [ { "Node Type": "Merge", - "PlanNodeId": 17, + "PlanNodeId": 7, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "TopSort-LeftJoin (MapJoin)", + "Node Type": "TopSort-TopSort", "Operators": [ { "Inputs": [ { "InternalOperatorId": 1 + }, + { + "ExternalPlanNodeId": 5 } ], "Limit": "1001", @@ -42,91 +45,76 @@ "TopSortBy": "" }, { - "Condition": "J1_TBL.i = J2_TBL.i", "Inputs": [ { - "ExternalPlanNodeId": 15 - }, - { - "ExternalPlanNodeId": 13 + "ExternalPlanNodeId": 5 } ], - "Name": "LeftJoin (MapJoin)" + "Limit": "1001", + "Name": "TopSort", + "TopSortBy": "" } ], - "PlanNodeId": 16, + "PlanNodeId": 6, "Plans": [ { - "Node Type": "Map", - "PlanNodeId": 15, - "PlanNodeType": "Connection", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "ConstantExpr", - "Operators": [ - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_0_0" - } - ], - "PlanNodeId": 14 - } - ] - }, - { - "Node Type": "Broadcast", - "PlanNodeId": 13, + "Node Type": "UnionAll", + "PlanNodeId": 5, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "Stage", - "PlanNodeId": 12, + "Node Type": "Collect", + "PlanNodeId": 4, "Plans": [ { - "Node Type": "UnionAll", - "PlanNodeId": 11, + "Columns": [ + "i", + "k" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "i" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 3, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_1_0", - "Node Type": "Filter-TableRangeScan-ConstantExpr", - "Operators": [ - { - "Inputs": [ - { - "InternalOperatorId": 1 - } - ], - "Name": "Filter", - "Predicate": "Exist(item.i)" - }, + "Node Type": "Stage", + "PlanNodeId": 2, + "Plans": [ { - "Inputs": [ + "Node Type": "TableFullScan", + "Operators": [ { - "InternalOperatorId": 2 + "Inputs": [], + "Name": "TableFullScan", + "ReadColumns": [ + "i", + "j", + "t" + ], + "ReadRanges": [ + "i (-\u221e, +\u221e)", + "j (-\u221e, +\u221e)", + "t (-\u221e, +\u221e)" + ], + "ReadRangesPointPrefixLen": "0", + "Scan": "Parallel", + "Table": "postgres_jointest/join1.test_plan/J1_TBL" } ], - "Name": "TableRangeScan", - "ReadColumns": [ - "i", - "k" - ], - "Table": "postgres_jointest/join1.test_plan/J2_TBL" - }, - { - "Inputs": [], - "Iterator": "precompute_1_0", - "Name": "Iterator" + "PlanNodeId": 1, + "Tables": [ + "postgres_jointest/join1.test_plan/J1_TBL" + ] } - ], - "PlanNodeId": 10, - "Tables": [ - "postgres_jointest/join1.test_plan/J2_TBL" ] } - ] + ], + "Table": "postgres_jointest/join1.test_plan/J2_TBL" } ] } @@ -145,81 +133,6 @@ ] } ] - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 8, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_0_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 7 - } - ], - "Subplan Name": "CTE precompute_1_0" - }, - { - "Node Type": "Precompute_0", - "Parent Relationship": "InitPlan", - "PlanNodeId": 5, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 4, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 3, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 2, - "Plans": [ - { - "Node Type": "TableFullScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableFullScan", - "ReadColumns": [ - "i", - "j", - "t" - ], - "ReadRanges": [ - "i (-\u221e, +\u221e)", - "j (-\u221e, +\u221e)", - "t (-\u221e, +\u221e)" - ], - "ReadRangesPointPrefixLen": "0", - "Scan": "Parallel", - "Table": "postgres_jointest/join1.test_plan/J1_TBL" - } - ], - "PlanNodeId": 1, - "Tables": [ - "postgres_jointest/join1.test_plan/J1_TBL" - ] - } - ] - } - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_0_0" } ], "Stats": { @@ -257,7 +170,10 @@ "i", "k" ], - "type": "Scan" + "lookup_by": [ + "i" + ], + "type": "Lookup" } ] } diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_8.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_8.plan index b572c17132..d15534020a 100644 --- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_8.plan +++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join1.test_/query_8.plan @@ -4,8 +4,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_2", - "PlanNodeId": 19, + "Node Type": "ResultSet", + "PlanNodeId": 9, "PlanNodeType": "ResultSet", "Plans": [ { @@ -14,27 +14,30 @@ { "Inputs": [ { - "ExternalPlanNodeId": 17 + "ExternalPlanNodeId": 7 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 18, + "PlanNodeId": 8, "Plans": [ { "Node Type": "Merge", - "PlanNodeId": 17, + "PlanNodeId": 7, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "TopSort-LeftJoin (MapJoin)", + "Node Type": "TopSort-TopSort", "Operators": [ { "Inputs": [ { "InternalOperatorId": 1 + }, + { + "ExternalPlanNodeId": 5 } ], "Limit": "1001", @@ -42,91 +45,76 @@ "TopSortBy": "" }, { - "Condition": "J1_TBL.i = J2_TBL.i", "Inputs": [ { - "ExternalPlanNodeId": 15 - }, - { - "ExternalPlanNodeId": 13 + "ExternalPlanNodeId": 5 } ], - "Name": "LeftJoin (MapJoin)" + "Limit": "1001", + "Name": "TopSort", + "TopSortBy": "" } ], - "PlanNodeId": 16, + "PlanNodeId": 6, "Plans": [ { - "Node Type": "Map", - "PlanNodeId": 15, - "PlanNodeType": "Connection", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "ConstantExpr", - "Operators": [ - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_0_0" - } - ], - "PlanNodeId": 14 - } - ] - }, - { - "Node Type": "Broadcast", - "PlanNodeId": 13, + "Node Type": "UnionAll", + "PlanNodeId": 5, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "Stage", - "PlanNodeId": 12, + "Node Type": "Collect", + "PlanNodeId": 4, "Plans": [ { - "Node Type": "UnionAll", - "PlanNodeId": 11, + "Columns": [ + "i", + "k" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "i" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 3, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_1_0", - "Node Type": "Filter-TableRangeScan-ConstantExpr", - "Operators": [ - { - "Inputs": [ - { - "InternalOperatorId": 1 - } - ], - "Name": "Filter", - "Predicate": "Exist(item.i)" - }, + "Node Type": "Stage", + "PlanNodeId": 2, + "Plans": [ { - "Inputs": [ + "Node Type": "TableFullScan", + "Operators": [ { - "InternalOperatorId": 2 + "Inputs": [], + "Name": "TableFullScan", + "ReadColumns": [ + "i", + "j", + "t" + ], + "ReadRanges": [ + "i (-\u221e, +\u221e)", + "j (-\u221e, +\u221e)", + "t (-\u221e, +\u221e)" + ], + "ReadRangesPointPrefixLen": "0", + "Scan": "Parallel", + "Table": "postgres_jointest/join1.test_plan/J1_TBL" } ], - "Name": "TableRangeScan", - "ReadColumns": [ - "i", - "k" - ], - "Table": "postgres_jointest/join1.test_plan/J2_TBL" - }, - { - "Inputs": [], - "Iterator": "precompute_1_0", - "Name": "Iterator" + "PlanNodeId": 1, + "Tables": [ + "postgres_jointest/join1.test_plan/J1_TBL" + ] } - ], - "PlanNodeId": 10, - "Tables": [ - "postgres_jointest/join1.test_plan/J2_TBL" ] } - ] + ], + "Table": "postgres_jointest/join1.test_plan/J2_TBL" } ] } @@ -145,81 +133,6 @@ ] } ] - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 8, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_0_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 7 - } - ], - "Subplan Name": "CTE precompute_1_0" - }, - { - "Node Type": "Precompute_0", - "Parent Relationship": "InitPlan", - "PlanNodeId": 5, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 4, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 3, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 2, - "Plans": [ - { - "Node Type": "TableFullScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableFullScan", - "ReadColumns": [ - "i", - "j", - "t" - ], - "ReadRanges": [ - "i (-\u221e, +\u221e)", - "j (-\u221e, +\u221e)", - "t (-\u221e, +\u221e)" - ], - "ReadRangesPointPrefixLen": "0", - "Scan": "Parallel", - "Table": "postgres_jointest/join1.test_plan/J1_TBL" - } - ], - "PlanNodeId": 1, - "Tables": [ - "postgres_jointest/join1.test_plan/J1_TBL" - ] - } - ] - } - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_0_0" } ], "Stats": { @@ -257,7 +170,10 @@ "i", "k" ], - "type": "Scan" + "lookup_by": [ + "i" + ], + "type": "Lookup" } ] } diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join3.test_/query_2.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join3.test_/query_2.plan index 65ab3f0569..6d3cfbf76a 100644 --- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join3.test_/query_2.plan +++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join3.test_/query_2.plan @@ -4,8 +4,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_2", - "PlanNodeId": 19, + "Node Type": "ResultSet", + "PlanNodeId": 9, "PlanNodeType": "ResultSet", "Plans": [ { @@ -14,27 +14,30 @@ { "Inputs": [ { - "ExternalPlanNodeId": 17 + "ExternalPlanNodeId": 7 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 18, + "PlanNodeId": 8, "Plans": [ { "Node Type": "Merge", - "PlanNodeId": 17, + "PlanNodeId": 7, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "TopSort-LeftJoin (MapJoin)", + "Node Type": "TopSort-TopSort", "Operators": [ { "Inputs": [ { "InternalOperatorId": 1 + }, + { + "ExternalPlanNodeId": 5 } ], "Limit": "1001", @@ -42,90 +45,72 @@ "TopSortBy": "argument.p.k" }, { - "Condition": "p.k = c.k", "Inputs": [ { - "ExternalPlanNodeId": 15 - }, - { - "ExternalPlanNodeId": 13 + "ExternalPlanNodeId": 5 } ], - "Name": "LeftJoin (MapJoin)" + "Limit": "1001", + "Name": "TopSort", + "TopSortBy": "argument.p.k" } ], - "PlanNodeId": 16, + "PlanNodeId": 6, "Plans": [ { - "Node Type": "Map", - "PlanNodeId": 15, + "Node Type": "UnionAll", + "PlanNodeId": 5, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_0_0", - "Node Type": "ConstantExpr", - "Operators": [ - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_0_0" - } - ], - "PlanNodeId": 14 - } - ] - }, - { - "Node Type": "Broadcast", - "PlanNodeId": 13, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Stage", - "PlanNodeId": 12, + "Node Type": "Collect", + "PlanNodeId": 4, "Plans": [ { - "Node Type": "UnionAll", - "PlanNodeId": 11, + "Columns": [ + "k" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "k" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 3, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_1_0", - "Node Type": "Filter-TablePointLookup-ConstantExpr", - "Operators": [ + "Node Type": "Stage", + "PlanNodeId": 2, + "Plans": [ { - "Inputs": [ + "Node Type": "TableFullScan", + "Operators": [ { - "InternalOperatorId": 1 + "Inputs": [], + "Name": "TableFullScan", + "ReadColumns": [ + "k", + "pd" + ], + "ReadRanges": [ + "k (-\u221e, +\u221e)" + ], + "ReadRangesPointPrefixLen": "0", + "Scan": "Parallel", + "Table": "postgres_jointest/join3.test_plan/parent" } ], - "Name": "Filter", - "Predicate": "Exist(item.k)" - }, - { - "Inputs": [ - { - "InternalOperatorId": 2 - } - ], - "Name": "TablePointLookup", - "ReadColumns": [ - "k" - ], - "Table": "postgres_jointest/join3.test_plan/child" - }, - { - "Inputs": [], - "Iterator": "precompute_1_0", - "Name": "Iterator" + "PlanNodeId": 1, + "Tables": [ + "postgres_jointest/join3.test_plan/parent" + ] } - ], - "PlanNodeId": 10, - "Tables": [ - "postgres_jointest/join3.test_plan/child" ] } - ] + ], + "Table": "postgres_jointest/join3.test_plan/child" } ] } @@ -141,78 +126,6 @@ ] } ] - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 8, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_0_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 7 - } - ], - "Subplan Name": "CTE precompute_1_0" - }, - { - "Node Type": "Precompute_0", - "Parent Relationship": "InitPlan", - "PlanNodeId": 5, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 4, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 3, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 2, - "Plans": [ - { - "Node Type": "TableFullScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableFullScan", - "ReadColumns": [ - "k", - "pd" - ], - "ReadRanges": [ - "k (-\u221e, +\u221e)" - ], - "ReadRangesPointPrefixLen": "0", - "Scan": "Parallel", - "Table": "postgres_jointest/join3.test_plan/parent" - } - ], - "PlanNodeId": 1, - "Tables": [ - "postgres_jointest/join3.test_plan/parent" - ] - } - ] - } - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_0_0" } ], "Stats": { @@ -231,6 +144,9 @@ "columns": [ "k" ], + "lookup_by": [ + "k" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join3.test_/query_4.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join3.test_/query_4.plan index 7707f2c82d..75b8111d8e 100644 --- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join3.test_/query_4.plan +++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join3.test_/query_4.plan @@ -4,8 +4,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_3", - "PlanNodeId": 19, + "Node Type": "ResultSet_1", + "PlanNodeId": 9, "PlanNodeType": "ResultSet", "Plans": [ { @@ -14,117 +14,90 @@ { "Inputs": [ { - "ExternalPlanNodeId": 17 + "ExternalPlanNodeId": 7 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 18, + "PlanNodeId": 8, "Plans": [ { "Node Type": "UnionAll", - "PlanNodeId": 17, + "PlanNodeId": 7, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "Limit-LeftJoin (MapJoin)", + "Node Type": "Limit", "Operators": [ { "Inputs": [ { - "InternalOperatorId": 1 + "ExternalPlanNodeId": 5 } ], "Limit": "1001", "Name": "Limit" - }, - { - "Condition": "p.k = c.k", - "Inputs": [ - { - "ExternalPlanNodeId": 15 - }, - { - "ExternalPlanNodeId": 13 - } - ], - "Name": "LeftJoin (MapJoin)" } ], - "PlanNodeId": 16, + "PlanNodeId": 6, "Plans": [ { - "Node Type": "Map", - "PlanNodeId": 15, - "PlanNodeType": "Connection", - "Plans": [ - { - "CTE Name": "precompute_1_0", - "Node Type": "ConstantExpr", - "Operators": [ - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_1_0" - } - ], - "PlanNodeId": 14 - } - ] - }, - { - "Node Type": "Broadcast", - "PlanNodeId": 13, + "Node Type": "UnionAll", + "PlanNodeId": 5, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "Stage", - "PlanNodeId": 12, + "Node Type": "Collect", + "PlanNodeId": 4, "Plans": [ { - "Node Type": "UnionAll", - "PlanNodeId": 11, + "Columns": [ + "k" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "k" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 3, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_2_0", - "Node Type": "Filter-TablePointLookup-ConstantExpr", - "Operators": [ + "Node Type": "Stage", + "PlanNodeId": 2, + "Plans": [ { - "Inputs": [ + "Node Type": "TableRangeScan", + "Operators": [ { - "InternalOperatorId": 1 + "Inputs": [], + "Name": "TableRangeScan", + "ReadColumns": [ + "k", + "pd" + ], + "ReadRangesExpectedSize": "1", + "ReadRangesKeys": [ + "k" + ], + "ReadRangesPointPrefixLen": "1", + "Scan": "Parallel", + "Table": "postgres_jointest/join3.test_plan/parent" } ], - "Name": "Filter", - "Predicate": "Exist(item.k)" - }, - { - "Inputs": [ - { - "InternalOperatorId": 2 - } - ], - "Name": "TablePointLookup", - "ReadColumns": [ - "k" - ], - "Table": "postgres_jointest/join3.test_plan/child" - }, - { - "Inputs": [], - "Iterator": "precompute_2_0", - "Name": "Iterator" + "PlanNodeId": 1, + "Tables": [ + "postgres_jointest/join3.test_plan/parent" + ] } - ], - "PlanNodeId": 10, - "Tables": [ - "postgres_jointest/join3.test_plan/child" ] } - ] + ], + "Table": "postgres_jointest/join3.test_plan/child" } ] } @@ -137,79 +110,6 @@ ] } ] - }, - { - "Node Type": "Precompute_2", - "Parent Relationship": "InitPlan", - "PlanNodeId": 8, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_1_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_1_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 7 - } - ], - "Subplan Name": "CTE precompute_2_0" - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 5, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 4, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 3, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 2, - "Plans": [ - { - "Node Type": "TableRangeScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableRangeScan", - "ReadColumns": [ - "k", - "pd" - ], - "ReadRangesExpectedSize": "1", - "ReadRangesKeys": [ - "k" - ], - "ReadRangesPointPrefixLen": "1", - "Scan": "Parallel", - "Table": "postgres_jointest/join3.test_plan/parent" - } - ], - "PlanNodeId": 1, - "Tables": [ - "postgres_jointest/join3.test_plan/parent" - ] - } - ] - } - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_1_0" } ], "Stats": { @@ -228,6 +128,9 @@ "columns": [ "k" ], + "lookup_by": [ + "k" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join3.test_/query_5.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join3.test_/query_5.plan index 1cc63767b3..29e3d9c1bc 100644 --- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join3.test_/query_5.plan +++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join3.test_/query_5.plan @@ -4,8 +4,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_5", - "PlanNodeId": 27, + "Node Type": "ResultSet_1", + "PlanNodeId": 13, "PlanNodeType": "ResultSet", "Plans": [ { @@ -14,212 +14,127 @@ { "Inputs": [ { - "ExternalPlanNodeId": 25 + "ExternalPlanNodeId": 11 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 26, + "PlanNodeId": 12, "Plans": [ { "Node Type": "UnionAll", - "PlanNodeId": 25, + "PlanNodeId": 11, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_4_0", - "Node Type": "Limit-InnerJoin (MapJoin)-ConstantExpr-Filter-TablePointLookup-ConstantExpr", + "Node Type": "Limit", "Operators": [ { "Inputs": [ { - "InternalOperatorId": 1 + "ExternalPlanNodeId": 9 } ], "Limit": "1001", "Name": "Limit" - }, - { - "Condition": "x.k = p_1.k", - "Inputs": [ - { - "InternalOperatorId": 3 - }, - { - "InternalOperatorId": 2 - } - ], - "Name": "InnerJoin (MapJoin)" - }, - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_3_0" - }, - { - "Inputs": [ - { - "InternalOperatorId": 4 - } - ], - "Name": "Filter", - "Predicate": "Exist(item.k)" - }, - { - "Inputs": [ - { - "InternalOperatorId": 5 - } - ], - "Name": "TablePointLookup", - "ReadColumns": [ - "k" - ], - "Table": "postgres_jointest/join3.test_plan/parent" - }, - { - "Inputs": [], - "Iterator": "precompute_4_0", - "Name": "Iterator" - } - ], - "PlanNodeId": 24, - "Tables": [ - "postgres_jointest/join3.test_plan/parent" - ] - } - ] - } - ] - } - ] - }, - { - "Node Type": "Precompute_4", - "Parent Relationship": "InitPlan", - "PlanNodeId": 22, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_3_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_3_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 21 - } - ], - "Subplan Name": "CTE precompute_4_0" - }, - { - "Node Type": "Precompute_3", - "Parent Relationship": "InitPlan", - "PlanNodeId": 19, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 18, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 17, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "LeftJoin (MapJoin)", - "Operators": [ - { - "Condition": "p_1.k = c.k", - "Inputs": [ - { - "ExternalPlanNodeId": 15 - }, - { - "ExternalPlanNodeId": 13 - } - ], - "Name": "LeftJoin (MapJoin)" } ], - "PlanNodeId": 16, + "PlanNodeId": 10, "Plans": [ { - "Node Type": "Map", - "PlanNodeId": 15, - "PlanNodeType": "Connection", - "Plans": [ - { - "CTE Name": "precompute_1_0", - "Node Type": "ConstantExpr", - "Operators": [ - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_1_0" - } - ], - "PlanNodeId": 14 - } - ] - }, - { - "Node Type": "Broadcast", - "PlanNodeId": 13, + "Node Type": "UnionAll", + "PlanNodeId": 9, "PlanNodeType": "Connection", "Plans": [ { "Node Type": "Stage", - "PlanNodeId": 12, + "PlanNodeId": 8, "Plans": [ { - "Node Type": "UnionAll", - "PlanNodeId": 11, + "Columns": [ + "k", + "pd" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "k" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 7, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_2_0", - "Node Type": "Filter-TablePointLookup-ConstantExpr", - "Operators": [ - { - "Inputs": [ - { - "InternalOperatorId": 1 - } - ], - "Name": "Filter", - "Predicate": "Exist(item.k)" - }, + "Node Type": "Stage", + "PlanNodeId": 6, + "Plans": [ { - "Inputs": [ + "Node Type": "UnionAll", + "PlanNodeId": 5, + "PlanNodeType": "Connection", + "Plans": [ { - "InternalOperatorId": 2 + "Node Type": "Collect", + "PlanNodeId": 4, + "Plans": [ + { + "Columns": [ + "k" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "k" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 3, + "PlanNodeType": "Connection", + "Plans": [ + { + "Node Type": "Stage", + "PlanNodeId": 2, + "Plans": [ + { + "Node Type": "TableRangeScan", + "Operators": [ + { + "Inputs": [], + "Name": "TableRangeScan", + "ReadColumns": [ + "k", + "pd" + ], + "ReadRangesExpectedSize": "1", + "ReadRangesKeys": [ + "k" + ], + "ReadRangesPointPrefixLen": "1", + "Scan": "Parallel", + "Table": "postgres_jointest/join3.test_plan/parent" + } + ], + "PlanNodeId": 1, + "Tables": [ + "postgres_jointest/join3.test_plan/parent" + ] + } + ] + } + ], + "Table": "postgres_jointest/join3.test_plan/child" + } + ] } - ], - "Name": "TablePointLookup", - "ReadColumns": [ - "k" - ], - "Table": "postgres_jointest/join3.test_plan/child" - }, - { - "Inputs": [], - "Iterator": "precompute_2_0", - "Name": "Iterator" + ] } - ], - "PlanNodeId": 10, - "Tables": [ - "postgres_jointest/join3.test_plan/child" ] } - ] + ], + "Table": "postgres_jointest/join3.test_plan/parent" } ] } @@ -231,81 +146,7 @@ } ] } - ], - "Subplan Name": "CTE precompute_3_0" - }, - { - "Node Type": "Precompute_2", - "Parent Relationship": "InitPlan", - "PlanNodeId": 8, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_1_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_1_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 7 - } - ], - "Subplan Name": "CTE precompute_2_0" - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 5, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 4, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 3, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 2, - "Plans": [ - { - "Node Type": "TableRangeScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableRangeScan", - "ReadColumns": [ - "k", - "pd" - ], - "ReadRangesExpectedSize": "1", - "ReadRangesKeys": [ - "k" - ], - "ReadRangesPointPrefixLen": "1", - "Scan": "Parallel", - "Table": "postgres_jointest/join3.test_plan/parent" - } - ], - "PlanNodeId": 1, - "Tables": [ - "postgres_jointest/join3.test_plan/parent" - ] - } - ] - } - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_1_0" + ] } ], "Stats": { @@ -324,6 +165,9 @@ "columns": [ "k" ], + "lookup_by": [ + "k" + ], "type": "Lookup" } ] @@ -340,6 +184,10 @@ }, { "columns": [ + "k", + "pd" + ], + "lookup_by": [ "k" ], "type": "Lookup" diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join3.test_/query_6.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join3.test_/query_6.plan index d8f725d85f..b5d3ac04a3 100644 --- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join3.test_/query_6.plan +++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join3.test_/query_6.plan @@ -4,8 +4,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_2", - "PlanNodeId": 19, + "Node Type": "ResultSet", + "PlanNodeId": 9, "PlanNodeType": "ResultSet", "Plans": [ { @@ -14,22 +14,22 @@ { "Inputs": [ { - "ExternalPlanNodeId": 17 + "ExternalPlanNodeId": 7 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 18, + "PlanNodeId": 8, "Plans": [ { "Node Type": "UnionAll", - "PlanNodeId": 17, + "PlanNodeId": 7, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "Limit-Filter-LeftJoin (MapJoin)", + "Node Type": "Limit-Filter", "Operators": [ { "Inputs": [ @@ -49,97 +49,69 @@ { "Inputs": [ { - "InternalOperatorId": 2 + "ExternalPlanNodeId": 5 } ], "Name": "Filter", "Predicate": "Not Exist(item.qa.id) Or item.qa.id > 0" - }, - { - "Condition": "qb.a_id = qa.id", - "Inputs": [ - { - "ExternalPlanNodeId": 15 - }, - { - "ExternalPlanNodeId": 13 - } - ], - "Name": "LeftJoin (MapJoin)" } ], - "PlanNodeId": 16, + "PlanNodeId": 6, "Plans": [ { - "Node Type": "Map", - "PlanNodeId": 15, - "PlanNodeType": "Connection", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "ConstantExpr", - "Operators": [ - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_0_0" - } - ], - "PlanNodeId": 14 - } - ] - }, - { - "Node Type": "Broadcast", - "PlanNodeId": 13, + "Node Type": "UnionAll", + "PlanNodeId": 5, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "Stage", - "PlanNodeId": 12, + "Node Type": "Collect", + "PlanNodeId": 4, "Plans": [ { - "Node Type": "UnionAll", - "PlanNodeId": 11, + "Columns": [ + "id" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "id" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 3, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_1_0", - "Node Type": "Filter-TablePointLookup-ConstantExpr", - "Operators": [ - { - "Inputs": [ - { - "InternalOperatorId": 1 - } - ], - "Name": "Filter", - "Predicate": "Exist(item.id)" - }, + "Node Type": "Stage", + "PlanNodeId": 2, + "Plans": [ { - "Inputs": [ + "Node Type": "TableFullScan", + "Operators": [ { - "InternalOperatorId": 2 + "Inputs": [], + "Name": "TableFullScan", + "ReadColumns": [ + "a_id", + "id" + ], + "ReadRanges": [ + "id (-\u221e, +\u221e)" + ], + "ReadRangesPointPrefixLen": "0", + "Scan": "Parallel", + "Table": "postgres_jointest/join3.test_plan/qb" } ], - "Name": "TablePointLookup", - "ReadColumns": [ - "id" - ], - "Table": "postgres_jointest/join3.test_plan/qa" - }, - { - "Inputs": [], - "Iterator": "precompute_1_0", - "Name": "Iterator" + "PlanNodeId": 1, + "Tables": [ + "postgres_jointest/join3.test_plan/qb" + ] } - ], - "PlanNodeId": 10, - "Tables": [ - "postgres_jointest/join3.test_plan/qa" ] } - ] + ], + "Table": "postgres_jointest/join3.test_plan/qa" } ] } @@ -152,78 +124,6 @@ ] } ] - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 8, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_0_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 7 - } - ], - "Subplan Name": "CTE precompute_1_0" - }, - { - "Node Type": "Precompute_0", - "Parent Relationship": "InitPlan", - "PlanNodeId": 5, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 4, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 3, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 2, - "Plans": [ - { - "Node Type": "TableFullScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableFullScan", - "ReadColumns": [ - "a_id", - "id" - ], - "ReadRanges": [ - "id (-\u221e, +\u221e)" - ], - "ReadRangesPointPrefixLen": "0", - "Scan": "Parallel", - "Table": "postgres_jointest/join3.test_plan/qb" - } - ], - "PlanNodeId": 1, - "Tables": [ - "postgres_jointest/join3.test_plan/qb" - ] - } - ] - } - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_0_0" } ], "Stats": { @@ -242,6 +142,9 @@ "columns": [ "id" ], + "lookup_by": [ + "id" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join3.test_/query_7.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join3.test_/query_7.plan index b5d92b0712..bcc3182081 100644 --- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join3.test_/query_7.plan +++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join3.test_/query_7.plan @@ -4,8 +4,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_2", - "PlanNodeId": 19, + "Node Type": "ResultSet", + "PlanNodeId": 9, "PlanNodeType": "ResultSet", "Plans": [ { @@ -14,22 +14,22 @@ { "Inputs": [ { - "ExternalPlanNodeId": 17 + "ExternalPlanNodeId": 7 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 18, + "PlanNodeId": 8, "Plans": [ { "Node Type": "UnionAll", - "PlanNodeId": 17, + "PlanNodeId": 7, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "Limit-Filter-LeftJoin (MapJoin)", + "Node Type": "Limit-Filter", "Operators": [ { "Inputs": [ @@ -46,97 +46,69 @@ { "Inputs": [ { - "InternalOperatorId": 2 + "ExternalPlanNodeId": 5 } ], "Name": "Filter", "Predicate": "Not Exist(item.qa.id) Or item.qa.id > 0" - }, - { - "Condition": "qb.a_id = qa.id", - "Inputs": [ - { - "ExternalPlanNodeId": 15 - }, - { - "ExternalPlanNodeId": 13 - } - ], - "Name": "LeftJoin (MapJoin)" } ], - "PlanNodeId": 16, + "PlanNodeId": 6, "Plans": [ { - "Node Type": "Map", - "PlanNodeId": 15, - "PlanNodeType": "Connection", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "ConstantExpr", - "Operators": [ - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_0_0" - } - ], - "PlanNodeId": 14 - } - ] - }, - { - "Node Type": "Broadcast", - "PlanNodeId": 13, + "Node Type": "UnionAll", + "PlanNodeId": 5, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "Stage", - "PlanNodeId": 12, + "Node Type": "Collect", + "PlanNodeId": 4, "Plans": [ { - "Node Type": "UnionAll", - "PlanNodeId": 11, + "Columns": [ + "id" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "id" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 3, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_1_0", - "Node Type": "Filter-TablePointLookup-ConstantExpr", - "Operators": [ - { - "Inputs": [ - { - "InternalOperatorId": 1 - } - ], - "Name": "Filter", - "Predicate": "Exist(item.id)" - }, + "Node Type": "Stage", + "PlanNodeId": 2, + "Plans": [ { - "Inputs": [ + "Node Type": "TableFullScan", + "Operators": [ { - "InternalOperatorId": 2 + "Inputs": [], + "Name": "TableFullScan", + "ReadColumns": [ + "a_id", + "id" + ], + "ReadRanges": [ + "id (-\u221e, +\u221e)" + ], + "ReadRangesPointPrefixLen": "0", + "Scan": "Parallel", + "Table": "postgres_jointest/join3.test_plan/qb" } ], - "Name": "TablePointLookup", - "ReadColumns": [ - "id" - ], - "Table": "postgres_jointest/join3.test_plan/qa" - }, - { - "Inputs": [], - "Iterator": "precompute_1_0", - "Name": "Iterator" + "PlanNodeId": 1, + "Tables": [ + "postgres_jointest/join3.test_plan/qb" + ] } - ], - "PlanNodeId": 10, - "Tables": [ - "postgres_jointest/join3.test_plan/qa" ] } - ] + ], + "Table": "postgres_jointest/join3.test_plan/qa" } ] } @@ -149,78 +121,6 @@ ] } ] - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 8, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_0_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 7 - } - ], - "Subplan Name": "CTE precompute_1_0" - }, - { - "Node Type": "Precompute_0", - "Parent Relationship": "InitPlan", - "PlanNodeId": 5, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 4, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 3, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 2, - "Plans": [ - { - "Node Type": "TableFullScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableFullScan", - "ReadColumns": [ - "a_id", - "id" - ], - "ReadRanges": [ - "id (-\u221e, +\u221e)" - ], - "ReadRangesPointPrefixLen": "0", - "Scan": "Parallel", - "Table": "postgres_jointest/join3.test_plan/qb" - } - ], - "PlanNodeId": 1, - "Tables": [ - "postgres_jointest/join3.test_plan/qb" - ] - } - ] - } - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_0_0" } ], "Stats": { @@ -239,6 +139,9 @@ "columns": [ "id" ], + "lookup_by": [ + "id" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join4.test_/query_13.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join4.test_/query_13.plan index 41bc2c77eb..f44986980d 100644 --- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join4.test_/query_13.plan +++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join4.test_/query_13.plan @@ -4,8 +4,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_2", - "PlanNodeId": 19, + "Node Type": "ResultSet", + "PlanNodeId": 9, "PlanNodeType": "ResultSet", "Plans": [ { @@ -14,27 +14,30 @@ { "Inputs": [ { - "ExternalPlanNodeId": 17 + "ExternalPlanNodeId": 7 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 18, + "PlanNodeId": 8, "Plans": [ { "Node Type": "Merge", - "PlanNodeId": 17, + "PlanNodeId": 7, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "TopSort-LeftJoin (MapJoin)", + "Node Type": "TopSort-TopSort", "Operators": [ { "Inputs": [ { "InternalOperatorId": 1 + }, + { + "ExternalPlanNodeId": 5 } ], "Limit": "1001", @@ -42,91 +45,74 @@ "TopSortBy": "" }, { - "Condition": "qrt1.a = qrt2.a", "Inputs": [ { - "ExternalPlanNodeId": 15 - }, - { - "ExternalPlanNodeId": 13 + "ExternalPlanNodeId": 5 } ], - "Name": "LeftJoin (MapJoin)" + "Limit": "1001", + "Name": "TopSort", + "TopSortBy": "" } ], - "PlanNodeId": 16, + "PlanNodeId": 6, "Plans": [ { - "Node Type": "Map", - "PlanNodeId": 15, - "PlanNodeType": "Connection", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "ConstantExpr", - "Operators": [ - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_0_0" - } - ], - "PlanNodeId": 14 - } - ] - }, - { - "Node Type": "Broadcast", - "PlanNodeId": 13, + "Node Type": "UnionAll", + "PlanNodeId": 5, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "Stage", - "PlanNodeId": 12, + "Node Type": "Collect", + "PlanNodeId": 4, "Plans": [ { - "Node Type": "UnionAll", - "PlanNodeId": 11, + "Columns": [ + "a", + "b" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "a" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 3, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_1_0", - "Node Type": "Filter-TableRangeScan-ConstantExpr", - "Operators": [ - { - "Inputs": [ - { - "InternalOperatorId": 1 - } - ], - "Name": "Filter", - "Predicate": "Exist(item.a)" - }, + "Node Type": "Stage", + "PlanNodeId": 2, + "Plans": [ { - "Inputs": [ + "Node Type": "TableFullScan", + "Operators": [ { - "InternalOperatorId": 2 + "Inputs": [], + "Name": "TableFullScan", + "ReadColumns": [ + "a", + "b" + ], + "ReadRanges": [ + "a (-\u221e, +\u221e)", + "b (-\u221e, +\u221e)" + ], + "ReadRangesPointPrefixLen": "0", + "Scan": "Parallel", + "Table": "postgres_jointest/join4.test_plan/qrt1" } ], - "Name": "TableRangeScan", - "ReadColumns": [ - "a", - "b" - ], - "Table": "postgres_jointest/join4.test_plan/qrt2" - }, - { - "Inputs": [], - "Iterator": "precompute_1_0", - "Name": "Iterator" + "PlanNodeId": 1, + "Tables": [ + "postgres_jointest/join4.test_plan/qrt1" + ] } - ], - "PlanNodeId": 10, - "Tables": [ - "postgres_jointest/join4.test_plan/qrt2" ] } - ] + ], + "Table": "postgres_jointest/join4.test_plan/qrt2" } ] } @@ -143,79 +129,6 @@ ] } ] - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 8, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_0_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 7 - } - ], - "Subplan Name": "CTE precompute_1_0" - }, - { - "Node Type": "Precompute_0", - "Parent Relationship": "InitPlan", - "PlanNodeId": 5, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 4, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 3, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 2, - "Plans": [ - { - "Node Type": "TableFullScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableFullScan", - "ReadColumns": [ - "a", - "b" - ], - "ReadRanges": [ - "a (-\u221e, +\u221e)", - "b (-\u221e, +\u221e)" - ], - "ReadRangesPointPrefixLen": "0", - "Scan": "Parallel", - "Table": "postgres_jointest/join4.test_plan/qrt1" - } - ], - "PlanNodeId": 1, - "Tables": [ - "postgres_jointest/join4.test_plan/qrt1" - ] - } - ] - } - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_0_0" } ], "Stats": { @@ -251,7 +164,10 @@ "a", "b" ], - "type": "Scan" + "lookup_by": [ + "a" + ], + "type": "Lookup" } ] } diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join4.test_/query_5.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join4.test_/query_5.plan index 06cdf8f0e5..b75dbc620d 100644 --- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join4.test_/query_5.plan +++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join4.test_/query_5.plan @@ -4,8 +4,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_2", - "PlanNodeId": 19, + "Node Type": "ResultSet", + "PlanNodeId": 9, "PlanNodeType": "ResultSet", "Plans": [ { @@ -14,118 +14,90 @@ { "Inputs": [ { - "ExternalPlanNodeId": 17 + "ExternalPlanNodeId": 7 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 18, + "PlanNodeId": 8, "Plans": [ { "Node Type": "UnionAll", - "PlanNodeId": 17, + "PlanNodeId": 7, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "Limit-LeftJoin (MapJoin)", + "Node Type": "Limit", "Operators": [ { "Inputs": [ { - "InternalOperatorId": 1 + "ExternalPlanNodeId": 5 } ], "Limit": "1001", "Name": "Limit" - }, - { - "Condition": "tqa.i,tqa.i,tqa.i = tqb.x,tqb.y,tqb.x", - "Inputs": [ - { - "ExternalPlanNodeId": 15 - }, - { - "ExternalPlanNodeId": 13 - } - ], - "Name": "LeftJoin (MapJoin)" } ], - "PlanNodeId": 16, + "PlanNodeId": 6, "Plans": [ { - "Node Type": "Map", - "PlanNodeId": 15, - "PlanNodeType": "Connection", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "ConstantExpr", - "Operators": [ - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_0_0" - } - ], - "PlanNodeId": 14 - } - ] - }, - { - "Node Type": "Broadcast", - "PlanNodeId": 13, + "Node Type": "UnionAll", + "PlanNodeId": 5, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "Stage", - "PlanNodeId": 12, + "Node Type": "Collect", + "PlanNodeId": 4, "Plans": [ { - "Node Type": "UnionAll", - "PlanNodeId": 11, + "Columns": [ + "x", + "y" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "x", + "y" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 3, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_1_0", - "Node Type": "Filter-TablePointLookup-ConstantExpr", - "Operators": [ + "Node Type": "Stage", + "PlanNodeId": 2, + "Plans": [ { - "Inputs": [ + "Node Type": "TableFullScan", + "Operators": [ { - "InternalOperatorId": 1 + "Inputs": [], + "Name": "TableFullScan", + "ReadColumns": [ + "i" + ], + "ReadRanges": [ + "i (-\u221e, +\u221e)" + ], + "ReadRangesPointPrefixLen": "0", + "Scan": "Parallel", + "Table": "postgres_jointest/join4.test_plan/tqa" } ], - "Name": "Filter", - "Predicate": "Exist(item.x) And Exist(item.y)" - }, - { - "Inputs": [ - { - "InternalOperatorId": 2 - } - ], - "Name": "TablePointLookup", - "ReadColumns": [ - "x", - "y" - ], - "Table": "postgres_jointest/join4.test_plan/tqb" - }, - { - "Inputs": [], - "Iterator": "precompute_1_0", - "Name": "Iterator" + "PlanNodeId": 1, + "Tables": [ + "postgres_jointest/join4.test_plan/tqa" + ] } - ], - "PlanNodeId": 10, - "Tables": [ - "postgres_jointest/join4.test_plan/tqb" ] } - ] + ], + "Table": "postgres_jointest/join4.test_plan/tqb" } ] } @@ -138,77 +110,6 @@ ] } ] - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 8, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_0_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 7 - } - ], - "Subplan Name": "CTE precompute_1_0" - }, - { - "Node Type": "Precompute_0", - "Parent Relationship": "InitPlan", - "PlanNodeId": 5, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 4, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 3, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 2, - "Plans": [ - { - "Node Type": "TableFullScan", - "Operators": [ - { - "Inputs": [], - "Name": "TableFullScan", - "ReadColumns": [ - "i" - ], - "ReadRanges": [ - "i (-\u221e, +\u221e)" - ], - "ReadRangesPointPrefixLen": "0", - "Scan": "Parallel", - "Table": "postgres_jointest/join4.test_plan/tqa" - } - ], - "PlanNodeId": 1, - "Tables": [ - "postgres_jointest/join4.test_plan/tqa" - ] - } - ] - } - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_0_0" } ], "Stats": { @@ -242,6 +143,10 @@ "x", "y" ], + "lookup_by": [ + "x", + "y" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join4.test_/query_6.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join4.test_/query_6.plan index 145782cdaf..7f6611e3e1 100644 --- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join4.test_/query_6.plan +++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join4.test_/query_6.plan @@ -4,8 +4,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_4", - "PlanNodeId": 31, + "Node Type": "ResultSet", + "PlanNodeId": 13, "PlanNodeType": "ResultSet", "Plans": [ { @@ -14,121 +14,127 @@ { "Inputs": [ { - "ExternalPlanNodeId": 29 + "ExternalPlanNodeId": 11 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 30, + "PlanNodeId": 12, "Plans": [ { "Node Type": "UnionAll", - "PlanNodeId": 29, + "PlanNodeId": 11, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "Limit-LeftJoin (MapJoin)", + "Node Type": "Limit", "Operators": [ { "Inputs": [ { - "InternalOperatorId": 1 + "ExternalPlanNodeId": 9 } ], "Limit": "1001", "Name": "Limit" - }, - { - "Condition": "zt3.f3 = zt1.f1", - "Inputs": [ - { - "ExternalPlanNodeId": 27 - }, - { - "ExternalPlanNodeId": 25 - } - ], - "Name": "LeftJoin (MapJoin)" } ], - "PlanNodeId": 28, + "PlanNodeId": 10, "Plans": [ { - "Node Type": "Broadcast", - "PlanNodeId": 25, + "Node Type": "UnionAll", + "PlanNodeId": 9, "PlanNodeType": "Connection", "Plans": [ { - "Node Type": "Stage", - "PlanNodeId": 24, + "Node Type": "Collect", + "PlanNodeId": 8, "Plans": [ { - "Node Type": "UnionAll", - "PlanNodeId": 23, + "Columns": [ + "f1" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "f1" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 7, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_3_0", - "Node Type": "Filter-TablePointLookup-ConstantExpr", - "Operators": [ - { - "Inputs": [ - { - "InternalOperatorId": 1 - } - ], - "Name": "Filter", - "Predicate": "Exist(item.f1)" - }, + "Node Type": "Stage", + "PlanNodeId": 6, + "Plans": [ { - "Inputs": [ + "Node Type": "UnionAll", + "PlanNodeId": 5, + "PlanNodeType": "Connection", + "Plans": [ { - "InternalOperatorId": 2 + "Node Type": "Collect", + "PlanNodeId": 4, + "Plans": [ + { + "Columns": [ + "f3" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "f3" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 3, + "PlanNodeType": "Connection", + "Plans": [ + { + "Node Type": "Stage", + "PlanNodeId": 2, + "Plans": [ + { + "Node Type": "TablePointLookup", + "Operators": [ + { + "Inputs": [], + "Name": "TablePointLookup", + "ReadColumns": [ + "f2" + ], + "ReadRange": [ + "f2 (53)" + ], + "Scan": "Parallel", + "Table": "postgres_jointest/join4.test_plan/zt2" + } + ], + "PlanNodeId": 1, + "Tables": [ + "postgres_jointest/join4.test_plan/zt2" + ] + } + ] + } + ], + "Table": "postgres_jointest/join4.test_plan/zt3" + } + ] } - ], - "Name": "TablePointLookup", - "ReadColumns": [ - "f1" - ], - "Table": "postgres_jointest/join4.test_plan/zt1" - }, - { - "Inputs": [], - "Iterator": "precompute_3_0", - "Name": "Iterator" + ] } - ], - "PlanNodeId": 22, - "Tables": [ - "postgres_jointest/join4.test_plan/zt1" ] } - ] + ], + "Table": "postgres_jointest/join4.test_plan/zt1" } ] } ] - }, - { - "Node Type": "Map", - "PlanNodeId": 27, - "PlanNodeType": "Connection", - "Plans": [ - { - "CTE Name": "precompute_2_0", - "Node Type": "ConstantExpr", - "Operators": [ - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_2_0" - } - ], - "PlanNodeId": 26 - } - ] } ] } @@ -137,201 +143,6 @@ ] } ] - }, - { - "Node Type": "Precompute_3", - "Parent Relationship": "InitPlan", - "PlanNodeId": 20, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_2_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_2_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 19 - } - ], - "Subplan Name": "CTE precompute_3_0" - }, - { - "Node Type": "Precompute_2", - "Parent Relationship": "InitPlan", - "PlanNodeId": 17, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 16, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 15, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "LeftJoin (MapJoin)", - "Operators": [ - { - "Condition": "zt2.f2 = zt3.f3", - "Inputs": [ - { - "ExternalPlanNodeId": 13 - }, - { - "ExternalPlanNodeId": 11 - } - ], - "Name": "LeftJoin (MapJoin)" - } - ], - "PlanNodeId": 14, - "Plans": [ - { - "Node Type": "Broadcast", - "PlanNodeId": 11, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Stage", - "PlanNodeId": 10, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 9, - "PlanNodeType": "Connection", - "Plans": [ - { - "CTE Name": "precompute_1_0", - "Node Type": "Filter-TablePointLookup-ConstantExpr", - "Operators": [ - { - "Inputs": [ - { - "InternalOperatorId": 1 - } - ], - "Name": "Filter", - "Predicate": "Exist(item.f3)" - }, - { - "Inputs": [ - { - "InternalOperatorId": 2 - } - ], - "Name": "TablePointLookup", - "ReadColumns": [ - "f3" - ], - "Table": "postgres_jointest/join4.test_plan/zt3" - }, - { - "Inputs": [], - "Iterator": "precompute_1_0", - "Name": "Iterator" - } - ], - "PlanNodeId": 8, - "Tables": [ - "postgres_jointest/join4.test_plan/zt3" - ] - } - ] - } - ] - } - ] - }, - { - "Node Type": "Map", - "PlanNodeId": 13, - "PlanNodeType": "Connection", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "ConstantExpr", - "Operators": [ - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_0_0" - } - ], - "PlanNodeId": 12 - } - ] - } - ] - } - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_2_0" - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 6, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_0_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 5 - } - ], - "Subplan Name": "CTE precompute_1_0" - }, - { - "Node Type": "Precompute_0", - "Parent Relationship": "InitPlan", - "PlanNodeId": 3, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 2, - "Plans": [ - { - "Node Type": "TablePointLookup", - "Operators": [ - { - "Inputs": [], - "Name": "TablePointLookup", - "ReadColumns": [ - "f2" - ], - "ReadRange": [ - "f2 (53)" - ], - "Scan": "Parallel", - "Table": "postgres_jointest/join4.test_plan/zt2" - } - ], - "PlanNodeId": 1, - "Tables": [ - "postgres_jointest/join4.test_plan/zt2" - ] - } - ] - } - ], - "Subplan Name": "CTE precompute_0_0" } ], "Stats": { @@ -350,6 +161,9 @@ "columns": [ "f1" ], + "lookup_by": [ + "f1" + ], "type": "Lookup" } ] @@ -375,6 +189,9 @@ "columns": [ "f3" ], + "lookup_by": [ + "f3" + ], "type": "Lookup" } ] diff --git a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join4.test_/query_7.plan b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join4.test_/query_7.plan index b9e59eb68f..cc69f3e676 100644 --- a/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join4.test_/query_7.plan +++ b/ydb/tests/functional/suite_tests/canondata/test_postgres.TestPGSQL.test_sql_suite_plan-jointest_join4.test_/query_7.plan @@ -4,8 +4,8 @@ "PlanNodeType": "Query", "Plans": [ { - "Node Type": "ResultSet_2", - "PlanNodeId": 13, + "Node Type": "ResultSet", + "PlanNodeId": 9, "PlanNodeType": "ResultSet", "Plans": [ { @@ -14,23 +14,22 @@ { "Inputs": [ { - "ExternalPlanNodeId": 11 + "ExternalPlanNodeId": 7 } ], "Limit": "1001", "Name": "Limit" } ], - "PlanNodeId": 12, + "PlanNodeId": 8, "Plans": [ { "Node Type": "UnionAll", - "PlanNodeId": 11, + "PlanNodeId": 7, "PlanNodeType": "Connection", "Plans": [ { - "CTE Name": "precompute_1_0", - "Node Type": "Limit-Filter-InnerJoin (MapJoin)-ConstantExpr-Filter-TableRangeScan-ConstantExpr", + "Node Type": "Limit-Filter", "Operators": [ { "Inputs": [ @@ -53,130 +52,74 @@ { "Inputs": [ { - "InternalOperatorId": 2 + "ExternalPlanNodeId": 5 } ], "Name": "Filter", "Predicate": "item.tt5.f1 == item.tt5.f2 - item.tt6.f2" - }, - { - "Condition": "tt6.f1 = tt5.f1", - "Inputs": [ - { - "InternalOperatorId": 4 - }, - { - "InternalOperatorId": 3 - } - ], - "Name": "InnerJoin (MapJoin)" - }, - { - "Inputs": [], - "Name": "ToFlow", - "ToFlow": "precompute_0_0" - }, - { - "Inputs": [ - { - "InternalOperatorId": 5 - } - ], - "Name": "Filter", - "Predicate": "Exist(item.f1)" - }, - { - "Inputs": [ - { - "InternalOperatorId": 6 - } - ], - "Name": "TableRangeScan", - "ReadColumns": [ - "f1", - "f2" - ], - "Table": "postgres_jointest/join4.test_plan/tt6" - }, - { - "Inputs": [], - "Iterator": "precompute_1_0", - "Name": "Iterator" } ], - "PlanNodeId": 10, - "Tables": [ - "postgres_jointest/join4.test_plan/tt6" - ] - } - ] - } - ] - } - ] - }, - { - "Node Type": "Precompute_1", - "Parent Relationship": "InitPlan", - "PlanNodeId": 8, - "PlanNodeType": "Materialize", - "Plans": [ - { - "CTE Name": "precompute_0_0", - "Node Type": "Aggregate", - "Operators": [ - { - "Input": "precompute_0_0", - "Inputs": [], - "Name": "PartitionByKey" - } - ], - "PlanNodeId": 7 - } - ], - "Subplan Name": "CTE precompute_1_0" - }, - { - "Node Type": "Precompute_0", - "Parent Relationship": "InitPlan", - "PlanNodeId": 5, - "PlanNodeType": "Materialize", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 4, - "Plans": [ - { - "Node Type": "UnionAll", - "PlanNodeId": 3, - "PlanNodeType": "Connection", - "Plans": [ - { - "Node Type": "Collect", - "PlanNodeId": 2, + "PlanNodeId": 6, "Plans": [ { - "Node Type": "TableFullScan", - "Operators": [ + "Node Type": "UnionAll", + "PlanNodeId": 5, + "PlanNodeType": "Connection", + "Plans": [ { - "Inputs": [], - "Name": "TableFullScan", - "ReadColumns": [ - "f1", - "f2" - ], - "ReadRanges": [ - "f1 (-\u221e, +\u221e)", - "f2 (-\u221e, +\u221e)" - ], - "ReadRangesPointPrefixLen": "0", - "Scan": "Parallel", - "Table": "postgres_jointest/join4.test_plan/tt5" + "Node Type": "Collect", + "PlanNodeId": 4, + "Plans": [ + { + "Columns": [ + "f1", + "f2" + ], + "E-Cost": "No estimate", + "E-Rows": "No estimate", + "E-Size": "No estimate", + "LookupKeyColumns": [ + "f1" + ], + "Node Type": "TableLookupJoin", + "PlanNodeId": 3, + "PlanNodeType": "Connection", + "Plans": [ + { + "Node Type": "Stage", + "PlanNodeId": 2, + "Plans": [ + { + "Node Type": "TableFullScan", + "Operators": [ + { + "Inputs": [], + "Name": "TableFullScan", + "ReadColumns": [ + "f1", + "f2" + ], + "ReadRanges": [ + "f1 (-\u221e, +\u221e)", + "f2 (-\u221e, +\u221e)" + ], + "ReadRangesPointPrefixLen": "0", + "Scan": "Parallel", + "Table": "postgres_jointest/join4.test_plan/tt5" + } + ], + "PlanNodeId": 1, + "Tables": [ + "postgres_jointest/join4.test_plan/tt5" + ] + } + ] + } + ], + "Table": "postgres_jointest/join4.test_plan/tt6" + } + ] } - ], - "PlanNodeId": 1, - "Tables": [ - "postgres_jointest/join4.test_plan/tt5" ] } ] @@ -185,8 +128,7 @@ } ] } - ], - "Subplan Name": "CTE precompute_0_0" + ] } ], "Stats": { @@ -222,7 +164,10 @@ "f1", "f2" ], - "type": "Scan" + "lookup_by": [ + "f1" + ], + "type": "Lookup" } ] } |