强制走hash 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_hash(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=4.19..216.70 rows=1 width=16)
   ->  Hash Join  (cost=4.19..216.70 rows=1 width=16)
         Hash Cond: (t1.f1 = t.f1)
         ->  Seq Scan on hint_t7 t1  (cost=0.00..175.00 rows=10000 width=8)
         ->  Hash  (cost=4.18..4.18 rows=1 width=8)
               ->  Index Scan using hint_t6_f1_idx on hint_t6 t  (cost=0.16..4.18 rows=1 width=8)
                     Index Cond: (f1 > 9999)
(7 rows)