非分布键 join通常使用hash join性能更好 insert into t2 select t,md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text),md5(t::text) from generateseries(1,1000000) as t; INSERT 0 1000000 Time: 17218.738 ms (00:17.219) teledb analyze t1; ANALYZE Time: 2219.341 ms (00:02.219) teledb analyze t2; ANALYZE Time: 1649.506 ms (00:01.650) teledb 2. 优化前SQL执行计划和耗时: 可以看到,关联走了Merge Join,耗时6.6秒。 teledb explain select t1. from t1,t2 where t1.f2t2.f2 limit 10; QUERY PLAN Limit (cost100.25..102.78 rows10 width367) > Remote Subquery Scan on all (dn001,dn002) (cost100.25..102.78 rows10 width367) > Limit (cost0.25..2.73 rows10 width367) > Merge Join (cost0.25..248056.80 rows1000000 width367) Merge Cond: (t1.f2 t2.f2) > Remote Subquery Scan on all (dn001,dn002) (cost100.12..487380.85 rows1000000 width367) Distribute results by S: f2 > Index Scan using t1f1idx on t1 (cost0.12..115280.85 rows1000000 width367) > Materialize (cost100.12..155875.95 rows1000000 width33) > Remote Subquery Scan on all (dn001,dn002) (cost100.12..153375.95 rows1000000 width33) Distribute results by S: f2 > Index Only Scan using t2f1idx on t2 (cost0.12..115275.95 rows1000000 width33) (12 rows) Time: 4.183 ms teledb
来自: