分布键join+limit优化 explain analyze select t1. from t1,t2 where t1.f1t2.f1 limit 10; QUERY PLAN Limit (cost0.25..1.65 rows10 width367) (actual time2675.437..2948.199 rows10 loops1) > Merge Join (cost0.25..140446.26 rows1000000 width367) (actual time2675.431..2675.508 rows10 loops1) Merge Cond: (t1.f1 t2.f1) > Remote Subquery Scan on all (dn01,dn02,dn03,dn04,dn05,dn06,dn07,dn08,dn09,dn10) (cost100.12..434823.13 rows1000000 width367) (actual time1.661..1.704 rows10 loops1) > Remote Subquery Scan on all (dn01,dn02,dn03,dn04,dn05,dn06,dn07,dn08,dn09,dn10) (cost100.12..71823.13 rows1000000 width4) (actual time2673.761..2673.783 rows10 loops1) Planning time: 0.358 ms Execution time: 2973.948 ms (7 rows) Time: 2976.008 ms (00:02.976) teledb 可以看到,join+limit写法,在preferolapoff的场景下,会拉大量的数据到CN节点进行计算、排序和limit过滤,消耗了大量的网络开销。 3. 优化后执行计划和耗时: 设置参数preferolapon(默认值),将join下推到DN节点执行: teledb set preferolap to on; SET Time: 0.291 ms teledb explain select t1. from t1,t2 where t1.f1t2.f1 limit 10; QUERY PLAN Limit (cost100.25..101.70 rows10 width367) > Remote Subquery Scan on all (dn01,dn02,dn03,dn04,dn05,dn06,dn07,dn08,dn09,dn10) (cost100.25..101.70 rows10 width367) > Limit (cost0.25..1.65 rows10 width367) > Merge Join (cost0.25..140446.26 rows1000000 width367) Merge Cond: (t1.f1 t2.f1) > Index Scan using t1f1key on t1 (cost0.12..62723.13 rows1000000 width367) > Index Only Scan using t2f1key on t2 (cost0.12..62723.13 rows1000000 width4) (7 rows) Time: 1.061 ms teledb