Hint查询

--创建表。
gaussdb=# CREATE TABLE t1(c1 int, c2 int, c3 int);
gaussdb=# CREATE TABLE t2(c1 int, c2 int, c3 int); 
gaussdb=# CREATE INDEX it1 ON t1(c1,c2); 

--Hint指定扫描方式。
gaussdb=# EXPLAIN SELECT /*+ indexscan(t1 it1)*/* from t1,t2 WHERE t1.c1=t2.c2;
 id |                 operation                  | E-rows | E-width | E-costs 
----+--------------------------------------------+--------+---------+---------
  1 | ->  Streaming (type: GATHER)               |     30 |      24 | 61.51
  2 |    ->  Hash Join (3,4)                     |     30 |      24 | 60.26
  3 |       ->  Index Scan using it1 on t1       |     30 |      12 | 44.46
  4 |       ->  Hash                             |     29 |      12 | 15.49
  5 |          ->  Streaming(type: REDISTRIBUTE) |     30 |      12 | 15.49
  6 |             ->  Seq Scan on t2             |     30 |      12 | 14.14
(6 rows)

 Predicate Information (identified by plan id) 
-----------------------------------------------
   2 --Hash Join (3,4)
         Hash Cond: (t1.c1 = t2.c2)
(2 rows)

--Hint指定行数。
gaussdb=# EXPLAIN SELECT /*+ rows(t1 t2 #5)*/* FROM t1,t2;
 id |              operation               | E-rows | E-width | E-costs 
----+--------------------------------------+--------+---------+---------
  1 | ->  Streaming (type: GATHER)         |      5 |      24 | 37.60
  2 |    ->  Nested Loop (3,5)             |      5 |      24 | 37.35
  3 |       ->  Streaming(type: BROADCAST) |     90 |      12 | 17.93
  4 |          ->  Seq Scan on t1          |     30 |      12 | 14.14
  5 |       ->  Materialize                |     30 |      12 | 14.21
  6 |          ->  Seq Scan on t2          |     30 |      12 | 14.14
(6 rows)

gaussdb=# DROP TABLE t1;
gaussdb=# DROP TABLE t2;