Hint 使用--leading

Oracle hint -- leading 的作用是提示优化器某张表先访问，可以指定一张或多张表，当指定多张表时，表示按指定的顺序访问这几张表。而 Postgresql leading hint的功能与oracle不同，leading 后面必须跟两张或多张表，如果是两张，表示这两张表先进行连接，但两张表的访问顺序不定。如果要严格控制表的访问顺序，还必须使用双括号，具体用法以例子形式进行介绍。

以下的例子在PG 12.3 与 KingbaseES V8R6 进行过验证。 

一、构造数据

create table t1(id1 integer,desc_t1 varchar(400));
create table t2(id2 integer,desc_t2 varchar(400));
create table t3(id3 integer,desc_t3 varchar(400));

insert into t1 select generate_series(1,100000),repeat('a',200);
insert into t2 select generate_series(1,100000),repeat('a',200);
insert into t3 select generate_series(1,100000),repeat('a',200);

analyze t1;
analyze t2;
analyze t3;

二、Oracle hint -- leading 使用

1、可以只指定一张表

SQL> select/*+leading(t3) hashjoin(t1 t2 t3)*/ desc_t1,desc_t2,desc_t3 from t1,t2,t3 where id1=id2 and id2=id3;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3350558109

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |   645 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN          |      |     1 |   645 |     6   (0)| 00:00:01 |
|*  2 |   HASH JOIN         |      |     1 |   430 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| T3   |     1 |   215 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2   |     1 |   215 |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | T1   |     1 |   215 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

2、指定多张表时，表示访问顺序

SQL> select/*+leading(t3,t1,t2) hashjoin(t1 t2 t3)*/ desc_t1,desc_t2,desc_t3 from t1,t2,t3 where id1=id2 and id2=id3;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3204703634

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |   645 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN            |      |     1 |   645 |     6   (0)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|      |     1 |   430 |     4   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | T3   |     1 |   215 |     2   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |      |     1 |   215 |     2   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | T1   |     1 |   215 |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL   | T2   |     1 |   215 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

三、PG hint -- leading 使用

1、必须至少指定两张表

test=# explain analyze select/*+leading(t3) hashjoin(t1 t2 t3)*/ desc_t1,desc_t2,desc_t3 from t1,t2,t3 where id1=id2 and id2=id3;
INFO:  sys_hint_plan: hint syntax error at or near "leading(t3) hashjoin(t1 t2 t3)"
DETAIL:  Leading hint requires at least two relations.

2、leading 只表示连接的顺序  -- 单层括号

以下例子，leading(t3 t1 t2) 表示 t3 t1 先进行连接，结果再与 t2 进行连接。与oracle 不同，这里并没有严格限制访问顺序，实际上还是 t1 最先访问。

test=# explain analyze select/*+leading(t3 t1 t2) hashjoin(t1 t2 t3)*/ desc_t1,desc_t2,desc_t3 from t1,t2,t3 where id1=id2 and id2=id3;
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=16050.00..44818.00 rows=100000 width=612) (actual time=106.935..352.686 rows=100000 loops=1)
   Hash Cond: (t1.id1 = t2.id2)
   ->  Hash Join  (cost=8025.00..21841.00 rows=100000 width=416) (actual time=54.546..165.037 rows=100000 loops=1)
         Hash Cond: (t1.id1 = t3.id3)
         ->  Seq Scan on t1  (cost=0.00..3942.00 rows=100000 width=208) (actual time=0.006..16.309 rows=100000 loops=1)
         ->  Hash  (cost=3942.00..3942.00 rows=100000 width=208) (actual time=54.457..54.457 rows=100000 loops=1)
               Buckets: 32768  Batches: 8  Memory Usage: 3225kB
               ->  Seq Scan on t3  (cost=0.00..3942.00 rows=100000 width=208) (actual time=0.005..17.343 rows=100000 loops=1)
   ->  Hash  (cost=3942.00..3942.00 rows=100000 width=208) (actual time=52.361..52.362 rows=100000 loops=1)
         Buckets: 32768  Batches: 8  Memory Usage: 3225kB
         ->  Seq Scan on t2  (cost=0.00..3942.00 rows=100000 width=208) (actual time=0.011..16.483 rows=100000 loops=1)
 Planning Time: 0.207 ms
 Execution Time: 357.799 ms
(13 rows)

3、双层括号表示访问顺序

以下例子leadint((t3 t1)) 不仅表示 t3 t1先连接，还指示 t3 表先访问。

test=# explain analyze select/*+leading((t3 t1)) hashjoin(t1 t2 t3)*/ desc_t1,desc_t2,desc_t3 from t1,t2,t3 where id1=id2 and id2=id3;
                                                          QUERY PLAN                                                          
------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=16050.00..44818.00 rows=100000 width=612) (actual time=103.223..324.500 rows=100000 loops=1)
   Hash Cond: (t1.id1 = t2.id2)
   ->  Hash Join  (cost=8025.00..21841.00 rows=100000 width=416) (actual time=50.143..157.813 rows=100000 loops=1)
         Hash Cond: (t3.id3 = t1.id1)
         ->  Seq Scan on t3  (cost=0.00..3942.00 rows=100000 width=208) (actual time=0.007..16.505 rows=100000 loops=1)
         ->  Hash  (cost=3942.00..3942.00 rows=100000 width=208) (actual time=49.973..49.974 rows=100000 loops=1)
               Buckets: 32768  Batches: 8  Memory Usage: 3225kB
               ->  Seq Scan on t1  (cost=0.00..3942.00 rows=100000 width=208) (actual time=0.006..16.008 rows=100000 loops=1)
   ->  Hash  (cost=3942.00..3942.00 rows=100000 width=208) (actual time=53.019..53.019 rows=100000 loops=1)
         Buckets: 32768  Batches: 8  Memory Usage: 3225kB
         ->  Seq Scan on t2  (cost=0.00..3942.00 rows=100000 width=208) (actual time=0.012..17.379 rows=100000 loops=1)
 Planning Time: 0.150 ms
 Execution Time: 328.360 ms
(13 rows)