查询优化器对于子查询、复制查询如多表关联、外关联的支 持等较弱，特别是在关联时对于三大 join 技术：hash join、merge join、nestloop join 的支持方面，Mysql 只支持最后一种 nestloop join（据说未来会支持 hash join），而多个大表关联分析时 hash join 是必备的利器，缺少这些关键功能非常致命，将难于在 OLAP 领域充当大任。我们最近对基于 -executor，Mapreduce 缺乏灵 活的 Join 技术支持；MPP 数据库可以基于 COST 来自动选择 Hash join、Merger join 和 Nestloop join， 甚 至 可 以 在 Hash join 通 过 COST 选择小表做 Hash，在 NestloopJoin 中选择 index 提高 join 性 能等等。 MPP 数据库对于 Aggregation（聚合）提供 该操作会将数据在所有节点之间做一次重新分布，对于超大表或者网 络配置不是极高的环境应该尽量避免。不过，这个操作是不可能不发 生的，需要尽量避免大表重分布——这与分布键的选择有直接关系。 建议，不要选择组合分布键，因为，在两表 JOIN 时，只有关联字段 包含所有分布键，才能确保数据不需要任何移动就满足 LocalJoin 的 要求。 然后，再来说说，执行计划根据什么来决定数据在节点之间如何移动。 当两张表关联时，到底是选择重分布还是选择广播。需要分以下几种

1878 Sort Operator Configuration Parameters 1878 Aggregate Operator Configuration Parameters 1878 Join Operator Configuration Parameters 1878 Other Postgres Planner Configuration Parameters 1878 Query 1933 gpperfmon_port 1934 ignore_checksum_failure 1934 integer_datetimes 1934 IntervalStyle 1935 join_collapse_limit 1935 krb_caseins_users 1935 krb_server_keyfile 1935 lc_collate 1936 lc_ctype 1936 ce 1953 optimizer_force_comprehensive_join_implementation 1953 optimizer_force_multistage_agg 1953 optimizer_force_three_stage_scalar_dqa 1953 optimizer_join_arity_for_associativity_commutativity 1954

1837 Sort Operator Configuration Parameters 1837 Aggregate Operator Configuration Parameters 1838 Join Operator Configuration Parameters 1838 Other Postgres Planner Configuration Parameters 1838 Query 1892 gpperfmon_port 1893 ignore_checksum_failure 1893 integer_datetimes 1893 IntervalStyle 1893 join_collapse_limit 1894 krb_caseins_users 1894 krb_server_keyfile 1894 lc_collate 1894 lc_ctype 1895 ehensive_join_implementation 1912 optimizer_force_multistage_agg 1912 optimizer_force_three_stage_scalar_dqa 1912 optimizer_join_arity_for_associativity_commutativity 1912 optimizer_join_order 1913

1557 Sort Operator Configuration Parameters 1557 Aggregate Operator Configuration Parameters 1558 Join Operator Configuration Parameters 1558 Other Postgres Planner Configuration Parameters 1558 Query 1614 gpperfmon_port 1614 ignore_checksum_failure 1614 integer_datetimes 1615 IntervalStyle 1615 join_collapse_limit 1615 krb_caseins_users 1616 krb_server_keyfile 1616 lc_collate 1616 lc_ctype 1616 ehensive_join_implementation 1635 optimizer_force_multistage_agg 1635 optimizer_force_three_stage_scalar_dqa 1635 optimizer_join_arity_for_associativity_commutativity 1636 optimizer_join_order 1636

1548 Sort Operator Configuration Parameters 1548 Aggregate Operator Configuration Parameters 1549 Join Operator Configuration Parameters 1549 Other Postgres Planner Configuration Parameters 1549 Query 1603 gpperfmon_port 1604 ignore_checksum_failure 1604 integer_datetimes 1604 IntervalStyle 1605 join_collapse_limit 1605 krb_caseins_users 1605 krb_server_keyfile 1606 lc_collate 1606 lc_ctype 1606 optimizer_force_three_stage_scalar_dqa 1624 optimizer_join_arity_for_associativity_commutativity 1625 optimizer_join_order 1625 optimizer_join_order_threshold 1626 optimizer_mdcache_size 1626 opt

1844 Sort Operator Configuration Parameters 1844 Aggregate Operator Configuration Parameters 1844 Join Operator Configuration Parameters 1844 Other Postgres Planner Configuration Parameters 1844 Query 1898 gpperfmon_port 1898 ignore_checksum_failure 1899 integer_datetimes 1899 IntervalStyle 1899 join_collapse_limit 1900 krb_caseins_users 1900 krb_server_keyfile 1900 lc_collate 1900 lc_ctype 1900 ehensive_join_implementation 1917 optimizer_force_multistage_agg 1917 optimizer_force_three_stage_scalar_dqa 1918 optimizer_join_arity_for_associativity_commutativity 1918 optimizer_join_order 1918

1784 Sort Operator Configuration Parameters 1785 Aggregate Operator Configuration Parameters 1785 Join Operator Configuration Parameters 1785 Other Postgres Planner Configuration Parameters 1785 Query 1835 gpperfmon_port 1835 ignore_checksum_failure 1836 integer_datetimes 1836 IntervalStyle 1836 join_collapse_limit 1837 krb_caseins_users 1837 krb_server_keyfile 1837 lc_collate 1837 lc_ctype 1837 ehensive_join_implementation 1854 optimizer_force_multistage_agg 1854 optimizer_force_three_stage_scalar_dqa 1854 optimizer_join_arity_for_associativity_commutativity 1854 optimizer_join_order 1855

1776 Sort Operator Configuration Parameters 1776 Aggregate Operator Configuration Parameters 1776 Join Operator Configuration Parameters 1777 Other Postgres Planner Configuration Parameters 1777 Query 1827 gpperfmon_port 1827 ignore_checksum_failure 1828 integer_datetimes 1828 IntervalStyle 1828 join_collapse_limit 1829 krb_caseins_users 1829 krb_server_keyfile 1829 lc_collate 1829 lc_ctype 1829 ehensive_join_implementation 1846 optimizer_force_multistage_agg 1846 optimizer_force_three_stage_scalar_dqa 1846 optimizer_join_arity_for_associativity_commutativity 1847 optimizer_join_order 1847

1568 Sort Operator Configuration Parameters 1568 Aggregate Operator Configuration Parameters 1569 Join Operator Configuration Parameters 1569 Other Postgres Planner Configuration Parameters 1569 Query 1614 gpperfmon_port 1614 ignore_checksum_failure 1614 integer_datetimes 1614 IntervalStyle 1614 join_collapse_limit 1615 krb_caseins_users 1615 krb_server_keyfile 1615 lc_collate 1615 lc_ctype 1615 ehensive_join_implementation 1630 optimizer_force_multistage_agg 1630 optimizer_force_three_stage_scalar_dqa 1630 optimizer_join_arity_for_associativity_commutativity 1630 optimizer_join_order 1631

1569 Sort Operator Configuration Parameters 1569 Aggregate Operator Configuration Parameters 1569 Join Operator Configuration Parameters 1570 Other Postgres Planner Configuration Parameters 1570 Query 1626 gpperfmon_port 1626 ignore_checksum_failure 1627 integer_datetimes 1627 IntervalStyle 1627 join_collapse_limit 1628 krb_caseins_users 1628 krb_server_keyfile 1628 lc_collate 1628 lc_ctype 1629 ehensive_join_implementation 1648 optimizer_force_multistage_agg 1648 optimizer_force_three_stage_scalar_dqa 1648 optimizer_join_arity_for_associativity_commutativity 1648 optimizer_join_order 1649