强制走merge join

• 复制
  复制成功

postgres=# create table hint_t6(f1 integer,f2 integer) ;
CREATE TABLE
postgres=# insert into hint_t6 select t as f1,t as f2 from generate_series(1,10000) as t; 
INSERT 0 10000
postgres=# create index hint_t6_f1_idx on hint_t6(f1);      
CREATE INDEX
postgres=# vacuum ANALYZE hint_t6;
VACUUM
postgres=# create table hint_t7(f1 integer,f2 integer) ;
CREATE TABLE 
postgres=# insert into hint_t7 select t as f1,t as f2 from generate_series(1,10000) as t; 
INSERT 0 10000
postgres=# create index hint_t7_f1_idx on hint_t7(f1);      
CREATE INDEX
postgres=# vacuum ANALYZE hint_t7;
VACUUM
postgres=# 
postgres=# explain select * from hint_t6 t,hint_t7 t1 where t.f1=t1.f1 and t.f1>9999 ; 
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Remote Subquery Scan on all (dn001)  (cost=0.32..8.37 rows=1 width=16)
   ->  Nested Loop  (cost=0.32..8.37 rows=1 width=16)
         ->  Index Scan using hint_t6_f1_idx on hint_t6 t  (cost=0.16..4.18 rows=1 width=8)
               Index Cond: (f1 > 9999)
         ->  Index Scan using hint_t7_f1_idx on hint_t7 t1  (cost=0.16..4.18 rows=1 width=8)
               Index Cond: (f1 = t.f1)
(6 rows)
postgres=# explain select /*+use_merge(t,t1) */ * from hint_t6 t,hint_t7 t1 where t.f1=t1.f1 and t.f1>9999 ; 
                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Remote Subquery Scan on all (dn001)  (cost=0.32..257.35 rows=1 width=16)
   ->  Merge Join  (cost=0.32..257.35 rows=1 width=16)
         Merge Cond: (t.f1 = t1.f1)
         ->  Index Scan using hint_t6_f1_idx on hint_t6 t  (cost=0.16..4.18 rows=1 width=8)
               Index Cond: (f1 > 9999)
         ->  Index Scan using hint_t7_f1_idx on hint_t7 t1  (cost=0.16..228.16 rows=10000 width=8)
(6 rows)
postgres=#