强制走nest loop

• 复制
  复制成功

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=# vacuum ANALYZE hint_t6;
VACUUM
postgres=# 
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=# vacuum ANALYZE hint_t7;
VACUUM
postgres=# 
postgres=# explain select * from hint_t6 t,hint_t7 t1 where t.f1=t1.f1 and t1.f1>9999 ;  
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Remote Subquery Scan on all (dn001)  (cost=200.01..412.52 rows=1 width=16)
   ->  Hash Join  (cost=200.01..412.52 rows=1 width=16)
         Hash Cond: (t.f1 = t1.f1)
         ->  Seq Scan on hint_t6 t  (cost=0.00..175.00 rows=10000 width=8)
         ->  Hash  (cost=200.00..200.00 rows=1 width=8)
               ->  Seq Scan on hint_t7 t1  (cost=0.00..200.00 rows=1 width=8)
                     Filter: (f1 > 9999)
(7 rows) 
postgres=# explain select /*+use_nl(t,t1) */ * from hint_t6 t,hint_t7 t1 where t.f1=t1.f1 and t.f1>999 ; 
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Remote Subquery Scan on all (dn001)  (cost=0.00..1350547.50 rows=9001 width=16)
   ->  Nested Loop  (cost=0.00..1350547.50 rows=9001 width=16)
         Join Filter: (t.f1 = t1.f1)
         ->  Seq Scan on hint_t7 t1  (cost=0.00..175.00 rows=10000 width=8)
         ->  Materialize  (cost=0.00..245.00 rows=9001 width=8)
               ->  Seq Scan on hint_t6 t  (cost=0.00..200.00 rows=9001 width=8)
                     Filter: (f1 > 999)
(7 rows)
postgres=#