非分布键关联，默认下推DN节点执行，DN节点之间会发生数据重分布，重分布数据量大的时候，性能会有明显下降。这种场景，通常使用Hash Join关联，性能会比Merge Join和Nested Loop更好。

而为了提高TP类业务查询的性能，我们经常需要对一些字段创建索引，使用有索引字段join时，优化器往往也会使用Merge Join和Nested Loop。针对此场景，可以通过会话级别关闭Merge Join和Nested Loop，让优化器走Hash Join，或通过hint指定Hash Join。

以下是关于该场景的测试情况：

1. 测试数据准备：

   teledb=# CREATE TABLE t1(f1 serial not null,f2 text,f3 text,f4 text,f5 text,f6 text,f7 text,f8 text,f9 text,f10 text,f11 text,f12 text) distribute by shard(f1); 
   CREATE TABLE
   Time: 481.042 ms
   
   teledb=# create index t1_f1_idx on t1(f2); 
   CREATE INDEX
   Time: 85.521 ms
   
   teledb=# CREATE TABLE t2(f1 serial not null,f2 text,f3 text,f4 text,f5 text,f6 text,f7 text,f8 text,f9 text,f10 text,f11 text,f12 text) distribute by shard(f1); 
   CREATE TABLE
   Time: 75.973 ms
   
   teledb=# create index t2_f1_idx on t2(f2);  
   CREATE INDEX
   Time: 29.890 ms
   
   teledb=# insert into t1 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 generate_series(1,1000000) as t;
   INSERT 0 1000000
   Time: 16450.623 ms (00:16.451)
   
   teledb=# 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 generate_series(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.f2=t2.f2 limit 10;
                                       QUERY PLAN                      
   -------------------------------------------------------------------------------------------------------------------------
    Limit  (cost=100.25..102.78 rows=10 width=367)
      ->  Remote Subquery Scan on all (dn001,dn002)  (cost=100.25..102.78 rows=10 width=367)
            ->  Limit  (cost=0.25..2.73 rows=10 width=367)
                  ->  Merge Join  (cost=0.25..248056.80 rows=1000000 width=367)
                        Merge Cond: (t1.f2 = t2.f2)
                        ->  Remote Subquery Scan on all (dn001,dn002)  (cost=100.12..487380.85 rows=1000000 width=367)
                              Distribute results by S: f2
                              ->  Index Scan using t1_f1_idx on t1  (cost=0.12..115280.85 rows=1000000 width=367)
                        ->  Materialize  (cost=100.12..155875.95 rows=1000000 width=33)
                              ->  Remote Subquery Scan on all (dn001,dn002)  (cost=100.12..153375.95 rows=1000000 width=33)
                                    Distribute results by S: f2
                                    ->  Index Only Scan using t2_f1_idx on t2  (cost=0.12..115275.95 rows=1000000 width=33)
   (12 rows)
   
   Time: 4.183 ms
   teledb=# explain analyze select t1.* from t1,t2 where t1.f2=t2.f2 limit 10;
                                             QUERY PLAN                               
   -------------------------------------------------------------------------------------------------------------------------------------------
    Limit  (cost=100.25..102.78 rows=10 width=367) (actual time=6555.346..6556.296 rows=10 loops=1)
      ->  Remote Subquery Scan on all (dn001,dn002)  (cost=100.25..102.78 rows=10 width=367) (actual time=6555.343..6555.349 rows=10 loops=1)
    Planning time: 0.473 ms
    Execution time: 6569.828 ms
   (4 rows)
   Time: 6614.439 ms (00:06.614)
   

3. 关闭Merge Join后的执行计划和耗时：

   可以看到，关闭Merge Join后，关联走了Nested Loop，耗时5.6秒。

   teledb=# set enable_mergejoin to off;
   SET
   Time: 0.422 ms
   teledb=# explain select t1.* from t1,t2 where t1.f2=t2.f2 limit 10;
                                             QUERY PLAN                                   
   --------------------------------------------------------------------------------------------------------------------
    Limit  (cost=100.12..103.57 rows=10 width=367)
      ->  Remote Subquery Scan on all (dn001,dn002)  (cost=100.12..103.57 rows=10 width=367)
            ->  Limit  (cost=0.12..3.52 rows=10 width=367)
                  ->  Nested Loop  (cost=0.12..339232.00 rows=1000000 width=367)
                        ->  Remote Subquery Scan on all (dn001,dn002)  (cost=100.00..434740.00 rows=1000000 width=367)
                              Distribute results by S: f2
                              ->  Seq Scan on t1  (cost=0.00..62640.00 rows=1000000 width=367)
                        ->  Materialize  (cost=100.12..100.31 rows=1 width=33)
                              ->  Remote Subquery Scan on all (dn001,dn002)  (cost=100.12..100.30 rows=1 width=33)
                                    Distribute results by S: f2
                                    ->  Index Only Scan using t2_f1_idx on t2  (cost=0.12..0.27 rows=1 width=33)
                                          Index Cond: (f2 = t1.f2)
   (12 rows)
   
   Time: 1.033 ms
   teledb=# explain analyze select t1.* from t1,t2 where t1.f2=t2.f2 limit 10;
                                                 QUERY PLAN                                               
   -------------------------------------------------------------------------------------------------------------------------------------------
    Limit  (cost=100.12..103.57 rows=10 width=367) (actual time=5608.326..5609.571 rows=10 loops=1)
      ->  Remote Subquery Scan on all (dn001,dn002)  (cost=100.12..103.57 rows=10 width=367) (actual time=5608.323..5608.349 rows=10 loops=1)
    Planning time: 0.347 ms
    Execution time: 5669.901 ms
   (4 rows)
   Time: 5672.584 ms (00:05.673)
   

4. 继续关闭Nested Loop后的执行计划和耗时：

   可以看到，继续关闭Nested Loop后，关联走了Hash Join，耗时1.1秒。

   teledb=# set enable_nestloop to off;
   SET
   Time: 0.436 ms
   teledb=#  explain select t1.* from t1,t2 where t1.f2=t2.f2 limit 10;
                                                QUERY PLAN                                                  
   -------------------------------------------------------------------------------------------------------------------------
    Limit  (cost=85983.00..85984.94 rows=10 width=367)
      ->  Remote Subquery Scan on all (dn001,dn002)  (cost=85983.00..85984.94 rows=10 width=367)
            ->  Limit  (cost=85883.00..85884.89 rows=10 width=367)
                  ->  Hash Join  (cost=85883.00..274580.00 rows=1000000 width=367)
                        Hash Cond: (t1.f2 = t2.f2)
                        ->  Remote Subquery Scan on all (dn001,dn002)  (cost=100.00..434740.00 rows=1000000 width=367)
                              Distribute results by S: f2
                              ->  Seq Scan on t1  (cost=0.00..62640.00 rows=1000000 width=367)
                        ->  Hash  (cost=100740.00..100740.00 rows=1000000 width=33)
                              ->  Remote Subquery Scan on all (dn001,dn002)  (cost=100.00..100740.00 rows=1000000 width=33)
                                    Distribute results by S: f2
                                    ->  Seq Scan on t2  (cost=0.00..62640.00 rows=1000000 width=33)
   (12 rows)
   Time: 1.141 ms
   teledb=#  explain analyze select t1.* from t1,t2 where t1.f2=t2.f2 limit 10;
                                                                     QUERY PLAN                                                             
   -----------------------------------------------------------------------------------------------------------------------------------------------
    Limit  (cost=85983.00..85984.94 rows=10 width=367) (actual time=1083.691..1085.962 rows=10 loops=1)
      ->  Remote Subquery Scan on all (dn001,dn002)  (cost=85983.00..85984.94 rows=10 width=367) (actual time=1083.688..1083.699 rows=10 loops=1)
    Planning time: 0.530 ms
    Execution time: 1108.830 ms
   (4 rows)
   Time: 1117.713 ms (00:01.118)
   teledb=#