This hint "NO_MERGE" instructs the optimizer not to combine the outer query and any inline view queries into a single query.

lightdb@postgres=# EXPLAIN (COSTS false) select * from t1, (select /*+no_merge*/t2.id from t2,t3) tt where t1.id=tt.id;
LOG:  lt_hint_plan:
used hint:
no_merge
not used hint:
duplication hint:
error hint:

                QUERY PLAN                
------------------------------------------
Hash Join
Hash Cond: (t2.id = t1.id)
->  Nested Loop
        ->  Seq Scan on t2 @"lt#0"
        ->  Materialize
            ->  Seq Scan on t3 @"lt#0"
->  Hash
        ->  Seq Scan on t1 @"lt#1"
(8 rows)

lightdb@postgres=# EXPLAIN (COSTS false) select/*+ no_merge(tt)*/ * from t1, (select t2.id from t2,t3) tt where t1.id=tt.id;
LOG:  lt_hint_plan:
used hint:
no_merge(tt@lt#0)
not used hint:
duplication hint:
error hint:

            QUERY PLAN             
------------------------------------
Hash Join
Hash Cond: (t2.id = t1.id)
->  Nested Loop
        ->  Seq Scan on t2
        ->  Materialize
            ->  Seq Scan on t3
->  Hash
        ->  Seq Scan on t1 @"lt#0"
(8 rows)

lightdb@postgres=# 

lightdb@postgres=# EXPLAIN (COSTS false) select/*+ no_merge(@qb)*/ * from t1, (select/*+qb_name(qb)*/ t2.id from t2,t3) tt where t1.id=tt.id;
LOG:  lt_hint_plan:
used hint:
no_merge(@qb)
not used hint:
duplication hint:
error hint:

            QUERY PLAN              
--------------------------------------
Hash Join
Hash Cond: (t2.id = t1.id)
->  Nested Loop
        ->  Seq Scan on t2 @qb
        ->  Materialize
            ->  Seq Scan on t3 @qb
->  Hash
        ->  Seq Scan on t1 @"lt#0"
(8 rows)

lightdb@postgres=# 

The hint "use_hash_aggregation" instructs the optimizer to use hash aggregation when group by. The hint "no_use_hash_aggregation" instructs the optimizer not to use hash aggregation when group by, then use sort.

There are two ways to use these hints. The first one has no parameters, specifying whether the current statement uses the hash algorithm, and the second one is combined with the query block name.

The following example shows the effect of the use_hash_aggregation hint.

lightdb@postgres=# EXPLAIN (COSTS false)  select max(id) from t1 where id>1 group by id order by id;
                QUERY PLAN                 
-------------------------------------------
GroupAggregate
Group Key: id
->  Index Only Scan using t1_pkey on t1
        Index Cond: (id > 1)
(4 rows)

lightdb@postgres=# EXPLAIN (COSTS false)  select/*+ use_hash_aggregation*/ max(id) from t1 where id>1 group by id order by id;
            QUERY PLAN             
------------------------------------
Sort
Sort Key: id
->  HashAggregate
        Group Key: id
        ->  Seq Scan on t1 @"lt#0"
            Filter: (id > 1)
(6 rows)

lightdb@postgres=# 

The following example shows the effect of the no_use_hash_aggregation hint.

lightdb@postgres=# EXPLAIN (COSTS false)  select max(id) from t1 where id>1 group by id;
        QUERY PLAN        
--------------------------
HashAggregate
Group Key: id
->  Seq Scan on t1
        Filter: (id > 1)
(4 rows)

lightdb@postgres=# EXPLAIN (COSTS false)  select/*+ no_use_hash_aggregation*/ max(id) from t1 where id>1 group by id;
                    QUERY PLAN                     
---------------------------------------------------
GroupAggregate
Group Key: id
->  Index Only Scan using t1_pkey on t1 @"lt#0"
        Index Cond: (id > 1)
(4 rows)

lightdb@postgres=# 

Hints for semi-join are "semijoin", "hash_sj", "nl_sj", "merge_sj" and "no_semijoin", which are used to control whether to use semi-join or specify the algorithm for semi-join. Hints for anti-join are "hash_aj", "nl_aj", "merge_aj", which are used to specify the algorithm for anti-join.

There are two ways to use these hints. The first one has no parameter and must be placed in sublink, and the second one is combined with the query block name.

The following example shows the effect of the hints with semi-join.

lightdb@postgres=# create table test1 (key1 int primary key, key2 int);
CREATE TABLE
lightdb@postgres=# create table test2 (key1 int primary key, key2 int);
CREATE TABLE
lightdb@postgres=# 
lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 where exists (select * from test2 where test1.key1=test2.key1);
                QUERY PLAN               
----------------------------------------
Hash Join
Hash Cond: (test1.key1 = test2.key1)
->  Seq Scan on test1
->  Hash
        ->  Seq Scan on test2
(5 rows)

lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 where exists (select /*+semijoin*/* from test2 where test1.key1=test2.key1);
                    QUERY PLAN                        
---------------------------------------------------------
Merge Semi Join
Merge Cond: (test1.key1 = test2.key1)
->  Index Scan using test1_pkey on test1 @"lt#1"
->  Index Only Scan using test2_pkey on test2 @"lt#0"
(4 rows)

lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 where exists (select /*+hash_sj*/* from test2 where test1.key1=test2.key1);
            QUERY PLAN               
----------------------------------------
Hash Semi Join
Hash Cond: (test1.key1 = test2.key1)
->  Seq Scan on test1 @"lt#1"
->  Hash
        ->  Seq Scan on test2 @"lt#0"
(5 rows)

lightdb@postgres=# 

The following example shows the effect of the hints without semi-join.

lightdb@postgres=# create table tt1 (id int, t int, name varchar(255));
CREATE TABLE
lightdb@postgres=# create table tt2 (id int , salary int);
CREATE TABLE
lightdb@postgres=# create index idx_t1_id on tt1(id);
CREATE INDEX
lightdb@postgres=# create index idx_t2_id on tt2(id);
CREATE INDEX
lightdb@postgres=#
lightdb@postgres=# EXPLAIN (COSTS false)  select * from tt1 where exists (select * from tt2 where tt1.id=tt2.id);
                QUERY PLAN                  
----------------------------------------------
Nested Loop Semi Join
->  Seq Scan on tt1
->  Index Only Scan using idx_t2_id on tt2
        Index Cond: (id = tt1.id)
(4 rows)

lightdb@postgres=# EXPLAIN (COSTS false)  select * from tt1 where exists (select /*+no_semijoin*/* from tt2 where tt1.id=tt2.id);
                QUERY PLAN                 
-------------------------------------------
Hash Join
Hash Cond: (tt1.id = tt2.id)
->  Seq Scan on tt1 @"lt#1"
->  Hash
        ->  HashAggregate
            Group Key: tt2.id
            ->  Seq Scan on tt2 @"lt#0"
(7 rows)

lightdb@postgres=# 

The following example shows the effect of the hints with anti-join.

lightdb@postgres=# create table tt1 (id int, t int, name varchar(255));
CREATE TABLE
lightdb@postgres=# create table tt2 (id int , salary int);
CREATE TABLE
lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 where not exists (select 1 from test2 where test1.key1=test2.key1);
            QUERY PLAN               
----------------------------------------
Hash Anti Join
Hash Cond: (test1.key1 = test2.key1)
->  Seq Scan on test1
->  Hash
        ->  Seq Scan on test2
(5 rows)

lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 where not exists (select/*+hash_aj*/ 1 from test2 where test1.key1=test2.key1);
            QUERY PLAN               
----------------------------------------
Hash Anti Join
Hash Cond: (test1.key1 = test2.key1)
->  Seq Scan on test1 @"lt#1"
->  Hash
        ->  Seq Scan on test2 @"lt#0"
(5 rows)
lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 where not exists (select/*+nl_aj*/ 1 from test2 where test1.key1=test2.key1);
                    QUERY PLAN                        
---------------------------------------------------------
Nested Loop Anti Join
->  Seq Scan on test1 @"lt#1"
->  Index Only Scan using test2_pkey on test2 @"lt#0"
        Index Cond: (key1 = test1.key1)
(4 rows)

lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 where not exists (select/*+merge_aj*/ 1 from test2 where test1.key1=test2.key1);
                    QUERY PLAN                        
---------------------------------------------------------
Merge Anti Join
Merge Cond: (test1.key1 = test2.key1)
->  Index Scan using test1_pkey on test1 @"lt#1"
->  Index Only Scan using test2_pkey on test2 @"lt#0"
(4 rows)

lightdb@postgres=#                        

"no_semijoin" doesn't prevent pulling up sublink, instead it dose not chooses semijoin paths when other paths can be chosen.

no_semijoin invalid：

lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 where exists (select /*+no_semijoin*/* from test2 where test1.key1>test2.key2);
LOG:  lt_hint_plan:
used hint:
no_semijoin
not used hint:
duplication hint:
error hint:

                QUERY PLAN                
------------------------------------------
Nested Loop Semi Join
Join Filter: (test1.key1 > test2.key2)
->  Seq Scan on test1 @"lt#1"
->  Materialize
        ->  Seq Scan on test2 @"lt#0"
(5 rows)

lightdb@postgres=#

When used with join method hint, join method hint will be ignored. The following example shows it.

lightdb@postgres=# explain select/*+nestloop(test1 test2@qb)*/ * from test1 where exists (select /*+semijoin qb_name(qb)*/* from test2 where test1.key1=test2.key1);
INFO:  lt_hint_plan: join type hint is conflicted with special join hint,ignore join type hint 
DETAIL:  join type hint: NestLoop, special join hint: semijoin(@qb) 
LOG:  lt_hint_plan:
used hint:
semijoin
not used hint:
duplication hint:
NestLoop(test1@lt#0 test2@qb)
error hint:

                                        QUERY PLAN                                 
        
-----------------------------------------------------------------------------------
--------
Merge Semi Join  (cost=0.31..124.01 rows=2260 width=8)
Merge Cond: (test1.key1 = test2.key1)
->  Index Scan using test1_pkey on test1 @"lt#0"  (cost=0.15..45.06 rows=2260 wi
dth=8)
->  Index Only Scan using test2_pkey on test2 @qb  (cost=0.15..45.06 rows=2260 w
idth=4)
(4 rows)

lightdb@postgres=# 

When used with the leading hint, parts of the leading hint that conflict with these hints will be ignored, but this may still render these hints ineffective. When there are conflicts, the result may not be as you expected, so the best way is resolve the conflict. The following example shows the conflict.

lightdb@postgres=# explain select/*+leading(test2@qb test1 )*/ * from test1 where exists (select /*+semijoin qb_name(qb)*/* from test2 where test1.key1=test2.key1);
INFO:  lt_hint_plan: special join hint conflict with sub of leading hint, ignore sub of leading hint 
DETAIL:  use special join hint: semijoin(@qb), the sub of leading has 2 relation
LOG:  lt_hint_plan:
used hint:
Leading((test2@qb test1@lt#0))
semijoin
not used hint:
duplication hint:
error hint:

                                        QUERY PLAN                                 
        
-----------------------------------------------------------------------------------
--------
Merge Semi Join  (cost=0.31..124.01 rows=2260 width=8)
Merge Cond: (test1.key1 = test2.key1)
->  Index Scan using test1_pkey on test1 @"lt#0"  (cost=0.15..45.06 rows=2260 wi
dth=8)
->  Index Only Scan using test2_pkey on test2 @qb  (cost=0.15..45.06 rows=2260 w
idth=4)
(4 rows)

lightdb@postgres=#

This hint "swap_join_inputs" specifies the outer table of hashjoin.

The following example shows the effect of "swap_join_inputs".

lightdb@postgres=# create table test1 (key1 int primary key, key2 int);
CREATE TABLE
lightdb@postgres=# create table test2 (key1 int primary key, key2 int);
CREATE TABLE
lightdb@postgres=# EXPLAIN (COSTS false) select * from test1, test2 where test1.key1 = test2.key1;
            QUERY PLAN               
----------------------------------------
Hash Join
Hash Cond: (test1.key1 = test2.key1)
->  Seq Scan on test1
->  Hash
        ->  Seq Scan on test2
(5 rows)

lightdb@postgres=# EXPLAIN (COSTS false) select /*+swap_join_inputs(test2)*/* from test1, test2 where test1.key1 = test2.key1;
LOG:  lt_hint_plan:
used hint:
swap_join_inputs(test2@lt#0)
not used hint:
duplication hint:
error hint:

            QUERY PLAN               
----------------------------------------
Hash Join
Hash Cond: (test2.key1 = test1.key1)
->  Seq Scan on test2 @"lt#0"
->  Hash
        ->  Seq Scan on test1 @"lt#0"
(5 rows)

lightdb@postgres=# 

Using this hint will enforce SQL to use hashjoin if possible. The following example shows it.

lightdb@postgres=# create table t_1(key1 int not null);
CREATE TABLE
lightdb@postgres=# create table t_2(key1 int not null);
CREATE TABLE
lightdb@postgres=# explain select /*swap_join_inputs(t_2)*/* from t_1,t_2 where t_1.key1=t_2.key1;
                            QUERY PLAN                             
-------------------------------------------------------------------
Merge Join  (cost=359.57..860.00 rows=32512 width=8)
Merge Cond: (t_1.key1 = t_2.key1)
->  Sort  (cost=179.78..186.16 rows=2550 width=4)
        Sort Key: t_1.key1
        ->  Seq Scan on t_1  (cost=0.00..35.50 rows=2550 width=4)
->  Sort  (cost=179.78..186.16 rows=2550 width=4)
        Sort Key: t_2.key1
        ->  Seq Scan on t_2  (cost=0.00..35.50 rows=2550 width=4)
(8 rows)

lightdb@postgres=# explain select /*+swap_join_inputs(t_2)*/* from t_1,t_2 where t_1.key1=t_2.key1;
LOG:  lt_hint_plan:
used hint:
swap_join_inputs(t_2@lt#0)
not used hint:
duplication hint:
error hint:

                                QUERY PLAN                                 
---------------------------------------------------------------------------
Hash Join  (cost=67.38..1247.18 rows=32512 width=8)
Hash Cond: (t_2.key1 = t_1.key1)
->  Seq Scan on t_2 @"lt#0"  (cost=0.00..35.50 rows=2550 width=4)
->  Hash  (cost=35.50..35.50 rows=2550 width=4)
        ->  Seq Scan on t_1 @"lt#0"  (cost=0.00..35.50 rows=2550 width=4)
(5 rows)

lightdb@postgres=# 

When used with the leading hint, parts of the leading hint that conflict with these hints will be ignored. The following example shows it.

lightdb@postgres=# EXPLAIN (COSTS false) select /*+ leading(test1 test2)  swap_join_inputs(test2)*/* from test1, test2 where test1.key1 = test2.key1;
INFO:  lt_hint_plan: leading hint conflict with swap_join_inputs hint, ignore leading hint 
DETAIL:  use swap_join_hint(test2@lt#0) 
LOG:  lt_hint_plan:
used hint:
Leading((test1@lt#0 test2@lt#0))
swap_join_inputs(test2@lt#0)
not used hint:
duplication hint:
error hint:

            QUERY PLAN               
----------------------------------------
Hash Join
Hash Cond: (test2.key1 = test1.key1)
->  Seq Scan on test2 @"lt#0"
->  Hash
        ->  Seq Scan on test1 @"lt#0"
(5 rows)

lightdb@postgres=# 

When used with the join method hint, this hint can't be used if the join method hint specifies a non-hashjoin method. The following example shows it.

lightdb@postgres=# EXPLAIN (COSTS false) select /*+ nestloop(test1 test2)  swap_join_inputs(test2)*/* from test1, test2 where test1.key1 = test2.key1;
LOG:  lt_hint_plan:
used hint:
NestLoop(test1@lt#0 test2@lt#0)
not used hint:
swap_join_inputs(test2@lt#0)
duplication hint:
error hint:

                    QUERY PLAN                     
----------------------------------------------------
Nested Loop
->  Seq Scan on test1 @"lt#0"
->  Index Scan using test2_pkey on test2 @"lt#0"
        Index Cond: (key1 = test1.key1)
(4 rows)

lightdb@postgres=# 

This hint shouldn't be used for semij-join and anti-join, becase semi-join and anti-join can't change outer table. When used with semi-join and anti-join hint, this hint will be ignored. The following example shows it.

lightdb@postgres=# EXPLAIN (COSTS false) select/*+swap_join_inputs(test2@qb)*/ * from test1 where exists ( select/*+semijoin qb_name(qb)*/ * from test2 where test1.key1 = test2.key1);
INFO:  lt_hint_plan: special join hint conflict with swap_join_inputs hint, ignore swap_join_inputs hint 
DETAIL:  ignore swap_join_hint(test2@qb), use semijoin(@qb) 
LOG:  lt_hint_plan:
used hint:
semijoin
not used hint:
duplication hint:
swap_join_inputs(test2@qb)
error hint:

                    QUERY PLAN                      
-----------------------------------------------------
Merge Semi Join
Merge Cond: (test1.key1 = test2.key1)
->  Index Scan using test1_pkey on test1 @"lt#0"
->  Index Only Scan using test2_pkey on test2 @qb
(4 rows)

lightdb@postgres=# 

This hint "ordered_predicates" forces the optimizer to preserve the order of predicate evaluation, except for predicates used as index keys. Use this hint for the WHERE clause of SELECT statements.

You can override these default evaluation rules using the ordered_predicates hint, and your WHERE clause items will then be evaluated in the order that they appear in the query. The ordered_predicates hint is commonly used in cases where a function is used in the WHERE clause of a query. It is also very useful in cases where you know the most restrictive predicates and you want Lightdb to evaluate these first.

The following example shows the effect of "ordered_predicates".

lightdb@postgres=# create table test1 (key1 int, key2 int);
CREATE TABLE
lightdb@postgres=# create table test2 (key1 int, key2 int);
CREATE TABLE
lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 where mod(key1,10)=2 and key2=2;
                   QUERY PLAN                   
------------------------------------------------
 Seq Scan on test1
   Filter: ((key2 = 2) AND (mod(key1, 10) = 2))
(2 rows)

lightdb@postgres=# EXPLAIN (COSTS false) select /*+ordered_predicates*/* from test1 where mod(key1,10)=2 and key2=2;
                   QUERY PLAN                   
------------------------------------------------
 Seq Scan on test1 @"lt#0"
   Filter: ((mod(key1, 10) = 2) AND (key2 = 2))
(2 rows)

lightdb@postgres=# 

This hint will not affect predicates that transitively generated by the optimizer.

In the following example, "b.key2 = 1" is regenerated by the optimizer, so it's order can't be reserved.

lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 a left join test2 b on a.key1=b.key1 and a.key2=b.key2 where mod(b.key2, 10) = 2 and b.key2 = 1; 
                      QUERY PLAN                      
------------------------------------------------------
 Nested Loop
   Join Filter: (a.key1 = b.key1)
   ->  Seq Scan on test2 b
         Filter: ((key2 = 1) AND (mod(key2, 10) = 2))
   ->  Seq Scan on test1 a
         Filter: (key2 = 1)
(6 rows)

lightdb@postgres=# EXPLAIN (COSTS false) select/*+ordered_predicates*/ * from test1 a left join test2 b on a.key1=b.key1 and a.key2=b.key2 where mod(b.key2, 10) = 2 and b.key2 = 1; 
                      QUERY PLAN                      
------------------------------------------------------
 Nested Loop
   Join Filter: (a.key1 = b.key1)
   ->  Seq Scan on test2 b @"lt#0"
         Filter: ((key2 = 1) AND (mod(key2, 10) = 2))
   ->  Seq Scan on test1 a @"lt#0"
         Filter: (key2 = 1)
(6 rows)

lightdb@postgres=# 

The optimization about pull up sublink is an RBO. it will pull up sublink if it is possible. So there is no need to implement "unnest" hint, we only support syntax here.

The "no_unnest" hint can be used to force to not pull up sublink.

The following example shows the effect of "no_unnest".

lightdb@postgres=# create table test1 (key1 int, key2 int);
CREATE TABLE
lightdb@postgres=# create table test2 (key1 int, key2 int);
CREATE TABLE
lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 where exists(select * from test2 where test1.key1=test2.key1);
               QUERY PLAN               
----------------------------------------
 Hash Join
   Hash Cond: (test1.key1 = test2.key1)
   ->  Seq Scan on test1
   ->  Hash
         ->  HashAggregate
               Group Key: test2.key1
               ->  Seq Scan on test2
(7 rows)

lightdb@postgres=# EXPLAIN (COSTS false) select * from test1 where exists(select/*+no_unnest*/ * from test2 where test1.key1=test2.key1);
                       QUERY PLAN                        
---------------------------------------------------------
 Seq Scan on test1 @"lt#1"
   Filter: (alternatives: SubPlan 1 or hashed SubPlan 2)
   SubPlan 1
     ->  Seq Scan on test2 @"lt#0"
           Filter: (test1.key1 = key1)
   SubPlan 2
     ->  Seq Scan on test2 test2_1 @"lt#0"
(7 rows)

lightdb@postgres=# 

Now 'pq_distribute' hint is only use for parallel hash join, and only this usage ('pq_distribute(inner_table none broadcast)') works.

The usage ('pq_distribute(inner_table hash hash)') is supported, but not work.

When used with 'leading' hint, it will be used only if the specified table is the inner table after using 'leading' hint.

The following example shows the effect of "pq_distribute".

lightdb@postgres=# create table t1 (id int, val text);
CREATE TABLE
lightdb@postgres=# create table t2 (id int, val text);
CREATE TABLE
lightdb@lt_test=# /*+set(parallel_tuple_cost 0) set(parallel_setup_cost 0) set(min_parallel_table_scan_size 0) set(min_parallel_index_scan_size 0) set(max_parallel_workers_per_gather 8)*/
lightdb@lt_test-# EXPLAIN (COSTS false) SELECT /*+ leading(t1 t2) hashjoin(t1 t2)*/ * FROM t1 join t2 on t1.id=t2.id;
                    QUERY PLAN                     
---------------------------------------------------
 Gather
   Workers Planned: 3
   ->  Parallel Hash Join
         Hash Cond: (t1.id = t2.id)
         ->  Parallel Seq Scan on t1 @"lt#0"
         ->  Parallel Hash
               ->  Parallel Seq Scan on t2 @"lt#0"
(7 rows)

lightdb@lt_test=# /*+set(parallel_tuple_cost 0) set(parallel_setup_cost 0) set(min_parallel_table_scan_size 0) set(min_parallel_index_scan_size 0) set(max_parallel_workers_per_gather 8)*/
lightdb@lt_test-# EXPLAIN (COSTS false) select/*+leading(t1 t2) hashjoin(t1 t2) pq_distribute(t2 none broadcast)*/ * from t1 join t2 on t1.id=t2.id;
                 QUERY PLAN                  
---------------------------------------------
 Gather
   Workers Planned: 3
   ->  Hash Join
         Hash Cond: (t1.id = t2.id)
         ->  Parallel Seq Scan on t1 @"lt#0"
         ->  Hash
               ->  Seq Scan on t2 @"lt#0"
(7 rows)

lightdb@lt_test=#  

When used with 'swap_join_inputs' hint, it will always be used if 'swap_join_inputs' hint is used. It is different from oracle, in oracle, 'pq_distribute' will not work if the specified table is the inner table after using 'swap_join_inputs'.

lightdb@lt_test=# /*+set(parallel_tuple_cost 0) set(parallel_setup_cost 0) set(min_parallel_table_scan_size 0) set(min_parallel_index_scan_size 0) set(max_parallel_workers_per_gather 8)*/
EXPLAIN (COSTS false) select/*+leading(t1 t2) hashjoin(t1 t2) pq_distribute(t1 none broadcast)*/ * from t1 join t2 on t1.id=t2.id;
                    QUERY PLAN                     
---------------------------------------------------
 Gather
   Workers Planned: 3
   ->  Parallel Hash Join
         Hash Cond: (t1.id = t2.id)
         ->  Parallel Seq Scan on t1 @"lt#0"
         ->  Parallel Hash
               ->  Parallel Seq Scan on t2 @"lt#0"
(7 rows)

lightdb@lt_test=# /*+set(parallel_tuple_cost 0) set(parallel_setup_cost 0) set(min_parallel_table_scan_size 0) set(min_parallel_index_scan_size 0) set(max_parallel_workers_per_gather 8)*/
EXPLAIN (COSTS false) select/*+leading(t1 t2) hashjoin(t1 t2) pq_distribute(t1 none broadcast) swap_join_inputs(t2)*/ * from t1 join t2 on t1.id=t2.id;
                 QUERY PLAN                  
---------------------------------------------
 Gather
   Workers Planned: 3
   ->  Hash Join
         Hash Cond: (t2.id = t1.id)
         ->  Parallel Seq Scan on t2 @"lt#0"
         ->  Hash
               ->  Seq Scan on t1 @"lt#0"
(7 rows)

lightdb@lt_test=# /*+set(parallel_tuple_cost 0) set(parallel_setup_cost 0) set(min_parallel_table_scan_size 0) set(min_parallel_index_scan_size 0) set(max_parallel_workers_per_gather 8)*/
EXPLAIN (COSTS false) select/*+leading(t1 t2) hashjoin(t1 t2) pq_distribute(t2 none broadcast) swap_join_inputs(t2)*/ * from t1 join t2 on t1.id=t2.id;
                 QUERY PLAN                  
---------------------------------------------
 Gather
   Workers Planned: 3
   ->  Hash Join
         Hash Cond: (t2.id = t1.id)
         ->  Parallel Seq Scan on t2 @"lt#0"
         ->  Hash
               ->  Seq Scan on t1 @"lt#0"
(7 rows)

lightdb@lt_test=#  

Without specifying the order of join, 'pq_distribute' hint might be used but doesn't work, beacuse it can only work when the specified table is the inner table and the join is an parallel hash join. If the cost of another path which the specified table is the outer table is lower, the optimizer will choose another path.

lightdb@lt_test=# /*+set(parallel_tuple_cost 0) set(parallel_setup_cost 0) set(min_parallel_table_scan_size 0) set(min_parallel_index_scan_size 0) set(max_parallel_workers_per_gather 8)*/
EXPLAIN (COSTS false) select/*+hashjoin(t1 t2)pq_distribute(t2 none broadcast)*/ * from t1 join t2 on t1.id=t2.id;
                    QUERY PLAN                     
---------------------------------------------------
 Gather
   Workers Planned: 3
   ->  Parallel Hash Join
         Hash Cond: (t2.id = t1.id)
         ->  Parallel Seq Scan on t2 @"lt#0"
         ->  Parallel Hash
               ->  Parallel Seq Scan on t1 @"lt#0"
(7 rows)

lightdb@lt_test=# /*+set(parallel_tuple_cost 0) set(parallel_setup_cost 0) set(min_parallel_table_scan_size 0) set(min_parallel_index_scan_size 0) set(max_parallel_workers_per_gather 8)*/
EXPLAIN (COSTS false) select/*+hashjoin(t1 t2)pq_distribute(t1 none broadcast)*/ * from t1 join t2 on t1.id=t2.id;
                    QUERY PLAN                     
---------------------------------------------------
 Gather
   Workers Planned: 3
   ->  Parallel Hash Join
         Hash Cond: (t1.id = t2.id)
         ->  Parallel Seq Scan on t1 @"lt#0"
         ->  Parallel Hash
               ->  Parallel Seq Scan on t2 @"lt#0"
(7 rows)

lightdb@lt_test=# /*+set(parallel_tuple_cost 0) set(parallel_setup_cost 0) set(min_parallel_table_scan_size 0) set(min_parallel_index_scan_size 0) set(max_parallel_workers_per_gather 8)*/
EXPLAIN (COSTS false) select/*+hashjoin(t1 t2)pq_distribute(t1 none broadcast) pq_distribute(t2 none broadcast)*/ * from t1 join t2 on t1.id=t2.id;
                 QUERY PLAN                  
---------------------------------------------
 Gather
   Workers Planned: 3
   ->  Hash Join
         Hash Cond: (t1.id = t2.id)
         ->  Parallel Seq Scan on t1 @"lt#0"
         ->  Hash
               ->  Seq Scan on t2 @"lt#0"
(7 rows)

lightdb@lt_test=#   

In LightDB, the optimization about pushing down clauses with sublink is an RBO. it will push down clauses with sublink if it is possible.

The "no_push_subq" hint can be used to force to not push down clauses with sublink.

The "push_subq" hint can be used to force to push down clauses with sublink. But because LightDB will push down clauses with sublink if it is possible, there is no need to use it. if you use it, it will be marked used when it can be used.

The following example shows the effect of "no_push_subq". The "push_subq" hint's usage is similar to "no_push_subq".

create table test_no_push_subq1 as select * from pg_class order by oid limit 100;
create table test_no_push_subq2 as select * from pg_class order by oid limit 100;
create table test_no_push_subq3 as select * from pg_class order by oid limit 100;
create table test_no_push_subq4 as select * from pg_class order by oid limit 100;
lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
lightdb@postgres-# where a.oid = (select max(oid) from test_no_push_subq2);
                  QUERY PLAN                  
----------------------------------------------
 Nested Loop
   Join Filter: (a.reltype = b.reltype)
   InitPlan 1 (returns $0)
     ->  Aggregate
           ->  Seq Scan on test_no_push_subq2
   ->  Seq Scan on test_no_push_subq1 a
         Filter: (oid = $0)
   ->  Materialize
         ->  Seq Scan on test_no_push_subq3 b
               Filter: (oid = $0)
(10 rows)

lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
lightdb@postgres-# where a.oid = (select/*+no_push_subq*/ max(oid) from test_no_push_subq2);
                         QUERY PLAN                         
------------------------------------------------------------
 Hash Join
   Hash Cond: ((a.reltype = b.reltype) AND (a.oid = b.oid))
   Join Filter: (a.oid = $0)
   InitPlan 1 (returns $0)
     ->  Aggregate
           ->  Seq Scan on test_no_push_subq2 @"lt#0"
   ->  Seq Scan on test_no_push_subq1 a @"lt#1"
   ->  Hash
         ->  Seq Scan on test_no_push_subq3 b @"lt#1"
(9 rows)

lightdb@postgres=# 
lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
lightdb@postgres-# where a.oid = (select max(oid) from test_no_push_subq2 c where a.oid = c.oid);
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
 Nested Loop
   Join Filter: ((a.reltype = b.reltype) AND (a.oid = b.oid))
   ->  Seq Scan on test_no_push_subq3 b
   ->  Materialize
         ->  Seq Scan on test_no_push_subq1 a
               Filter: (oid = (SubPlan 2))
               SubPlan 2
                 ->  Result
                       InitPlan 1 (returns $1)
                         ->  Limit
                               ->  Seq Scan on test_no_push_subq2 c
                                     Filter: ((oid IS NOT NULL) AND (a.oid = oid))
(12 rows)

lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
lightdb@postgres-# where a.oid = (select/*+no_push_subq*/  max(oid) from test_no_push_subq2 c where a.oid = c.oid);
                              QUERY PLAN                               
-----------------------------------------------------------------------
 Hash Join
   Hash Cond: ((a.reltype = b.reltype) AND (a.oid = b.oid))
   Join Filter: (a.oid = (SubPlan 2))
   ->  Seq Scan on test_no_push_subq1 a @"lt#1"
   ->  Hash
         ->  Seq Scan on test_no_push_subq3 b @"lt#1"
   SubPlan 2
     ->  Result
           InitPlan 1 (returns $1)
             ->  Limit
                   ->  Seq Scan on test_no_push_subq2 c @"lt#0"
                         Filter: ((oid IS NOT NULL) AND (a.oid = oid))
(12 rows)

lightdb@postgres=# 
lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select * from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype
lightdb@postgres-# where (select oid from test_no_push_subq2 c where c.oid=a.oid) = (select oid from test_no_push_subq3 d where d.oid=a.oid);
                      QUERY PLAN                      
------------------------------------------------------
 Nested Loop
   Join Filter: (a.reltype = b.reltype)
   ->  Seq Scan on test_no_push_subq3 b
   ->  Materialize
         ->  Seq Scan on test_no_push_subq1 a
               Filter: ((SubPlan 1) = (SubPlan 2))
               SubPlan 1
                 ->  Seq Scan on test_no_push_subq2 c
                       Filter: (oid = a.oid)
               SubPlan 2
                 ->  Seq Scan on test_no_push_subq3 d
                       Filter: (oid = a.oid)
(12 rows)

lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select * from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype
lightdb@postgres-# where (select/*+no_push_subq*/ oid from test_no_push_subq2 c where c.oid=a.oid) = (select oid from test_no_push_subq3 d where d.oid=a.oid);
                      QUERY PLAN                      
------------------------------------------------------
 Hash Join
   Hash Cond: (a.reltype = b.reltype)
   Join Filter: ((SubPlan 1) = (SubPlan 2))
   ->  Seq Scan on test_no_push_subq1 a @"lt#2"
   ->  Hash
         ->  Seq Scan on test_no_push_subq3 b @"lt#2"
   SubPlan 1
     ->  Seq Scan on test_no_push_subq2 c @"lt#0"
           Filter: (oid = a.oid)
   SubPlan 2
     ->  Seq Scan on test_no_push_subq3 d @"lt#1"
           Filter: (oid = a.oid)
(12 rows)

lightdb@postgres=# 
lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
lightdb@postgres-# where a.oid > all (select oid from test_no_push_subq2 c where c.oid =a.oid);
                         QUERY PLAN                         
------------------------------------------------------------
 Hash Join
   Hash Cond: ((a.reltype = b.reltype) AND (a.oid = b.oid))
   ->  Seq Scan on test_no_push_subq1 a
         Filter: (SubPlan 1)
         SubPlan 1
           ->  Seq Scan on test_no_push_subq2 c
                 Filter: (oid = a.oid)
   ->  Hash
         ->  Seq Scan on test_no_push_subq3 b
(9 rows)

lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
lightdb@postgres-# where a.oid > all (select/*+ no_push_subq*/ oid from test_no_push_subq2 c where c.oid =a.oid);
                         QUERY PLAN                         
------------------------------------------------------------
 Hash Join
   Hash Cond: ((a.reltype = b.reltype) AND (a.oid = b.oid))
   Join Filter: (SubPlan 1)
   ->  Seq Scan on test_no_push_subq1 a @"lt#1"
   ->  Hash
         ->  Seq Scan on test_no_push_subq3 b @"lt#1"
   SubPlan 1
     ->  Seq Scan on test_no_push_subq2 c @"lt#0"
           Filter: (oid = a.oid)
(9 rows)

lightdb@postgres=# 
lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
lightdb@postgres-# where exists(select/*+no_unnest*/ * from test_no_push_subq2 c where a.oid = c.oid and c.oid=2691);
                          QUERY PLAN                           
---------------------------------------------------------------
 Hash Join
   Hash Cond: ((a.reltype = b.reltype) AND (a.oid = b.oid))
   ->  Seq Scan on test_no_push_subq1 a @"lt#1"
         Filter: (alternatives: SubPlan 1 or hashed SubPlan 2)
         SubPlan 1
           ->  Result
                 One-Time Filter: (a.oid = '2691'::oid)
                 ->  Seq Scan on test_no_push_subq2 c @"lt#0"
                       Filter: (oid = '2691'::oid)
         SubPlan 2
           ->  Seq Scan on test_no_push_subq2 c_1 @"lt#0"
                 Filter: (oid = '2691'::oid)
   ->  Hash
         ->  Seq Scan on test_no_push_subq3 b @"lt#1"
(14 rows)

lightdb@postgres=# EXPLAIN (COSTS false)
lightdb@postgres-# select a.oid from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype and a.oid = b.oid
lightdb@postgres-# where exists(select/*+no_unnest no_push_subq*/ * from test_no_push_subq2 c where a.oid = c.oid and c.oid=2691);
                          QUERY PLAN                          
--------------------------------------------------------------
 Hash Join
   Hash Cond: ((a.reltype = b.reltype) AND (a.oid = b.oid))
   Join Filter: (alternatives: SubPlan 1 or hashed SubPlan 2)
   ->  Seq Scan on test_no_push_subq1 a @"lt#1"
   ->  Hash
         ->  Seq Scan on test_no_push_subq3 b @"lt#1"
   SubPlan 1
     ->  Result
           One-Time Filter: (a.oid = '2691'::oid)
           ->  Seq Scan on test_no_push_subq2 c @"lt#0"
                 Filter: (oid = '2691'::oid)
   SubPlan 2
     ->  Seq Scan on test_no_push_subq2 c_1 @"lt#0"
           Filter: (oid = '2691'::oid)
(14 rows)

lightdb@postgres=# 

Note that "no_push_subq" is not used when "push_subq" and "no_push_subq" are not applied to the same sublink, but to the same filter (same with oracle). for example("no_push_subq" on subplan2 is not used):

EXPLAIN (COSTS false)
select * from test_push_subq1 a join test_push_subq3 b on a.reltype =b.reltype
where ((select/*+no_push_subq*/ relname from test_push_subq2 c where c.oid=a.oid), (select/*+push_subq*/ reltype from test_push_subq2  c where c.oid=a.oid)) = (select/*+no_push_subq*/ relname, reltype from test_push_subq2);
LOG:  lt_hint_plan:
used hint:
no_push_subq
push_subq
not used hint:
no_push_subq
duplication hint:
error hint:

                      QUERY PLAN                       
-------------------------------------------------------
 Hash Join
   Hash Cond: (a.reltype = b.reltype)
   Join Filter: ((SubPlan 1) = $2)
   InitPlan 3 (returns $2,$3)
     ->  Seq Scan on test_push_subq2 @"lt#0"
   ->  Seq Scan on test_push_subq1 a @"lt#3"
         Filter: ((SubPlan 2) = $3)
         SubPlan 2
           ->  Seq Scan on test_push_subq2 c_1 @"lt#2"
                 Filter: (oid = a.oid)
   ->  Hash
         ->  Seq Scan on test_push_subq3 b @"lt#3"
   SubPlan 1
     ->  Seq Scan on test_push_subq2 c @"lt#1"
           Filter: (oid = a.oid)
(15 rows)

The "ignore_row_on_dupkey_index" hint can be used to ignore a unique key violation for a specified set of columns or for a specified index. Currently using this hint is the same as using 'on conflict do nothing' clause. If you specify this hint when inserting data with DML error logging enabled, then the unique key violation is not logged and does not cause statement termination.

The semantic effect of this hint results in error messages if specific rules are violated:

The following example shows the effect of the "ignore_row_on_dupkey_index" hint:

create table test_ignore_dupkey(key1 int, key2 int, key3 int, key4 int);
create unique index test_ignore_dupkey_ui1 on test_ignore_dupkey(key1, key2);
create unique index test_ignore_dupkey_ui2 on test_ignore_dupkey(key3, key4);
create index test_ignore_dupkey_i on test_ignore_dupkey(key3);

lightdb@postgres=# insert into test_ignore_dupkey values(1, 1, 1, 1);
INSERT 0 1
lightdb@postgres=# insert into test_ignore_dupkey values(1, 1, 1, 1);
ERROR:  duplicate key value violates unique constraint "test_ignore_dupkey_ui1"
DETAIL:  Key (key1, key2)=(1, 1) already exists.
lightdb@postgres=# insert/*+IGNORE_ROW_ON_DUPKEY_INDEX(test_ignore_dupkey  test_ignore_dupkey_ui1) */ into test_ignore_dupkey values(1, 1, 1, 2);
INSERT 0 0
lightdb@postgres=# insert/*+IGNORE_ROW_ON_DUPKEY_INDEX(test_ignore_dupkey(key1, key2)) */ into test_ignore_dupkey values(1, 1, 1, 2);
INSERT 0 0
lightdb@postgres=# insert/*+IGNORE_ROW_ON_DUPKEY_INDEX(test_ignore_dupkey) */ into test_ignore_dupkey values(1, 1, 1, 2);
ERROR:  An index must be specified in the index hint
lightdb@postgres=# insert/*+IGNORE_ROW_ON_DUPKEY_INDEX(test_ignore_dupkey test_ignore_dupkey_ui3) */ into test_ignore_dupkey values(1, 1, 1, 2);
ERROR:  Index specified in the index hint is invalid
lightdb@postgres=# insert/*+IGNORE_ROW_ON_DUPKEY_INDEX(test_ignore_dupkey test_ignore_dupkey_i) */ into test_ignore_dupkey values(1, 1, 1, 2);
ERROR:  Index specified in the index hint is invalid
lightdb@postgres=# insert/*+IGNORE_ROW_ON_DUPKEY_INDEX(test_ignore_dupkey test_ignore_dupkey_ui1 test_ignore_dupkey_ui2) */ into test_ignore_dupkey values(1, 1, 1, 2);
ERROR:  Multiple indexes in ignore duplicate key hint
lightdb@postgres=# update/*+IGNORE_ROW_ON_DUPKEY_INDEX(test_ignore_dupkey  test_ignore_dupkey_ui1) */ test_ignore_dupkey set key2 = 2 where key1 = 1;
ERROR:  IGNORE_ROW_ON_DUPKEY_INDEX hint disallowed for this operation
lightdb@postgres=# create unique index test_ignore_dupkey_ui3 on test_ignore_dupkey(key3, key4);
CREATE INDEX
lightdb@postgres=# insert/*+IGNORE_ROW_ON_DUPKEY_INDEX(test_ignore_dupkey(key3, key4)) */ into test_ignore_dupkey values(1, 2, 1, 1);
ERROR:  Multiple indexes in ignore duplicate key hint
DETAIL:  find multiple indexes by fields
lightdb@postgres=# insert/*+IGNORE_ROW_ON_DUPKEY_INDEX(test_ignore_dupkey  test_ignore_dupkey_ui1) */ into test_ignore_dupkey values(1, 1, 1, 2)
lightdb@postgres-# on conflict (key1, key2) do nothing;
ERROR:  IGNORE_ROW_ON_DUPKEY_INDEX hint disallowed for this operation
DETAIL:  insert operation with on confict clause is not supported
lightdb@postgres=# 

The "ignore_row_on_dupkey_index" hint can work in canopy, but only for insert select statement, it is not supported for insert values statement.

create table test_ignore_dupkey(key1 int, key2 int, key3 int, key4 int);
SELECT create_distributed_table('test_ignore_dupkey', 'key1');
create unique index test_ignore_dupkey_ui1 on test_ignore_dupkey(key1, key2);
create unique index test_ignore_dupkey_ui2 on test_ignore_dupkey(key1, key3);
create index test_ignore_dupkey_i on test_ignore_dupkey(key3);

lightdb@postgres=# insert into test_ignore_dupkey values(1, 1, 1, 1);
INSERT 0 1
lightdb@postgres=# insert into test_ignore_dupkey values(1, 1, 1, 1);
ERROR:  duplicate key value violates unique constraint "test_ignore_dupkey_ui1_102045"
DETAIL:  Key (key1, key2)=(1, 1) already exists.
lightdb@postgres=# insert/*+IGNORE_ROW_ON_DUPKEY_INDEX(test_ignore_dupkey  test_ignore_dupkey_ui1) */ into test_ignore_dupkey values(1, 1, 2, 1);
ERROR:  duplicate key value violates unique constraint "test_ignore_dupkey_ui1_102045"
DETAIL:  Key (key1, key2)=(1, 1) already exists.

create table test_ignore_dupkey1(key1 int, key2 int, key3 int, key4 int);
insert into test_ignore_dupkey1 values(1, 1, 1, 1);
insert into test_ignore_dupkey1 values(1, 1, 1, 1);
insert into test_ignore_dupkey1 values(1, 2, 1, 1);
insert into test_ignore_dupkey1 values(1, 2, 1, 1);
insert into test_ignore_dupkey1 values(1, 1, 1, 2);
insert into test_ignore_dupkey1 values(1, 1, 1, 2);
insert into test_ignore_dupkey1 values(1, 3, 1, 3);

lightdb@postgres=# insert into test_ignore_dupkey select * from test_ignore_dupkey1;
ERROR:  duplicate key value violates unique constraint "test_ignore_dupkey_ui1_102045"
DETAIL:  Key (key1, key2)=(1, 1) already exists.
CONTEXT:  COPY test_ignore_dupkey_102045, line 2
lightdb@postgres=# insert/*+IGNORE_ROW_ON_DUPKEY_INDEX(test_ignore_dupkey(key1, key3)) */ into test_ignore_dupkey select * from test_ignore_dupkey1;
ERROR:  duplicate key value violates unique constraint "test_ignore_dupkey_ui1_102045"
DETAIL:  Key (key1, key2)=(1, 1) already exists.
CONTEXT:  COPY test_ignore_dupkey_102045, line 2
lightdb@postgres=# SELECT create_distributed_table('test_ignore_dupkey1', 'key1');
NOTICE:  Copying data from local table...
NOTICE:  copying the data has completed
DETAIL:  The local data in the table is no longer visible, but is still on disk.
HINT:  To remove the local data, run: SELECT truncate_local_data_after_distributing_table($$public.test_ignore_dupkey1$$)
 create_distributed_table 
--------------------------
 
(1 row)

lightdb@postgres=# insert/*+IGNORE_ROW_ON_DUPKEY_INDEX(test_ignore_dupkey(key1, key3)) */ into test_ignore_dupkey select * from test_ignore_dupkey1;
INSERT 0 1
lightdb@postgres=# select * from test_ignore_dupkey;
 key1 | key2 | key3 | key4 
------+------+------+------
    1 |    1 |    1 |    1
(1 row)

If you have a non-mergeable view or subquery(possible because of a no_merge hint) in your query, how should you operate the join from other tables? should you create one large view result and join it once (no_push_pred) or should you push the join predicate down into the view definition and recreate the view result set for every driving row from another table (push_pred and must be nestloop).

The "no_push_pred" hint instructs the optimizer not to push a join predicate into the view.

LightDB does not support to push a join predicate into the view yet. Now this is only supported for Oracle compatibility. So if you use "no_push_pred" hint, it will always work.

The following example shows the usage of the "no_push_pred" hint:

create table test_no_push_pred1 (key1 int primary key, key2 int);
create table test_no_push_pred2 (key1 int primary key, key2 int);
create table test_no_push_pred3 (key1 int primary key, key2 int);

set enable_hashjoin to off;
set enable_mergejoin to off;

lightdb@test_o=# EXPLAIN (COSTS false)
lightdb@test_o-# select /*+leading(a)*/* from test_no_push_pred1 a join (select * from test_no_push_pred2 order by key1 limit 1) b on a.key1 =b.key2;
                                    QUERY PLAN                              
      
----------------------------------------------------------------------------
------
 Nested Loop
   Join Filter: (a.key1 = test_no_push_pred2.key2)
   ->  Seq Scan on test_no_push_pred1 a @"lt#1"
   ->  Materialize
         ->  Limit
               ->  Index Scan using test_no_push_pred2_pkey on test_no_push_pred2
(6 rows)

lightdb@test_o=# EXPLAIN (COSTS false)
lightdb@test_o-# select /*+leading(a) no_push_pred(b)*/* from test_no_push_pred1 a join (select * from test_no_push_pred2 order by key1 limit 1) b on a.key1 =b.key2;
                                    QUERY PLAN                              
      
----------------------------------------------------------------------------
------
 Nested Loop
   Join Filter: (a.key1 = test_no_push_pred2.key2)
   ->  Seq Scan on test_no_push_pred1 a @"lt#1"
   ->  Materialize
         ->  Limit
               ->  Index Scan using test_no_push_pred2_pkey on test_no_push_pred2
(6 rows)

lightdb@test_o=# EXPLAIN (COSTS false)
lightdb@test_o-# select /*+leading(a) no_push_pred(@qb)*/* from test_no_push_pred1 a join (select/*+ qb_name(qb)*/ * from test_no_push_pred2 order by key1 limit 1) b on a.key1 =b.key2;
                                      QUERY PLAN                            
          
----------------------------------------------------------------------------
----------
 Nested Loop
   Join Filter: (a.key1 = test_no_push_pred2.key2)
   ->  Seq Scan on test_no_push_pred1 a @"lt#0"
   ->  Materialize
         ->  Limit
               ->  Index Scan using test_no_push_pred2_pkey on test_no_push_pred2 @qb
(6 rows)

lightdb@test_o=# EXPLAIN (COSTS false)
lightdb@test_o-# select /*+leading(a) */* from test_no_push_pred1 a join (select/*+ no_push_pred*/ * from test_no_push_pred2 order by key1 limit 1) b on a.key1 =b.key2;
                                        QUERY PLAN                          
              
----------------------------------------------------------------------------
--------------
 Nested Loop
   Join Filter: (a.key1 = test_no_push_pred2.key2)
   ->  Seq Scan on test_no_push_pred1 a @"lt#1"
   ->  Materialize
         ->  Limit
               ->  Index Scan using test_no_push_pred2_pkey on test_no_push_pred2 @"lt#0"
(6 rows)

Star transformation is an optimizer transformation that avoids full table scans of fact tables in a star schema(star schema divides data into facts and dimensions). In joins of fact and dimension tables, a star transformation can avoid a full scan of a fact table. The star transformation improves performance by fetching only relevant fact rows that join to the constraint dimension rows. In some cases, queries have restrictive filters on other columns of the dimension tables. The combination of filters can dramatically reduce the data set that the database processes from the fact table.

The "no_star_transformation" hint instructs the optimizer not to perform star query transformation.

LightDB does not support star transformation yet. Now "no_star_transformation" hint is only supported for Oracle compatibility. So if you use "no_star_transformation" hint, it will always work.

The following example shows the usage of the "no_star_transformation" hint:

CREATE TABLE times (

  time_id NUMBER PRIMARY KEY,

  calendar_quarter_desc VARCHAR2(20)

);


CREATE TABLE customers (

  cust_id NUMBER PRIMARY KEY,

  cust_city VARCHAR2(20),

  cust_state_province VARCHAR2(20)

);


CREATE TABLE channels (

  channel_id NUMBER PRIMARY KEY,

  channel_class VARCHAR2(20),

  channel_desc VARCHAR2(20)

);


CREATE TABLE sales (

  time_id NUMBER REFERENCES times(time_id),

  cust_id NUMBER REFERENCES customers(cust_id),

  channel_id NUMBER REFERENCES channels(channel_id),

  amount_sold NUMBER

);

lightdb@postgres=# EXPLAIN (COSTS false) SELECT/*+no_star_transformation*/ ch.channel_class, c.cust_city, t.calendar_quarter_desc,
       SUM(s.amount_sold) sales_amount
    FROM sales s, times t, customers c, channels ch
    WHERE s.time_id = t.time_id
    AND s.cust_id = c.cust_id
    AND s.channel_id = ch.channel_id
    AND c.cust_state_province = 'CA'
    AND ch.channel_desc in ('Internet','Catalog')
    AND t.calendar_quarter_desc IN ('1999-Q1','1999-Q2')
    GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc;
LOG:  lt_hint_plan:
used hint:
no_star_transformation
not used hint:
duplication hint:
error hint:

                                              QUERY PLAN                    
                           
----------------------------------------------------------------------------
---------------------------
 GroupAggregate
   Group Key: ch.channel_class, c.cust_city, t.calendar_quarter_desc
   ->  Sort
         Sort Key: ch.channel_class, c.cust_city, t.calendar_quarter_desc
         ->  Nested Loop
               ->  Nested Loop
                     ->  Hash Join
                           Hash Cond: (s.cust_id = c.cust_id)
                           ->  Seq Scan on sales s @"lt#0"
                           ->  Hash
                                 ->  Seq Scan on customers c @"lt#0"
                                       Filter: ((cust_state_province)::text 
= 'CA'::text)
                     ->  Index Scan using times_pkey on times t @"lt#0"
                           Index Cond: (time_id = s.time_id)
                           Filter: ((calendar_quarter_desc)::text = ANY ('{1
999-Q1,1999-Q2}'::text[]))
               ->  Index Scan using channels_pkey on channels ch @"lt#0"
                     Index Cond: (channel_id = s.channel_id)
                     Filter: ((channel_desc)::text = ANY ('{Internet,Catalog
}'::text[]))
(18 rows)

lightdb@postgres=# EXPLAIN (COSTS false) 
select/*+no_star_transformation(@qb)*/ * from (SELECT/*+qb_name(qb)*/ ch.channel_class, c.cust_city, t.calendar_quarter_desc,
       SUM(s.amount_sold) sales_amount
    FROM sales s, times t, customers c, channels ch
    WHERE s.time_id = t.time_id
    AND s.cust_id = c.cust_id
    AND s.channel_id = ch.channel_id
    AND c.cust_state_province = 'CA'
    AND ch.channel_desc in ('Internet','Catalog')
    AND t.calendar_quarter_desc IN ('1999-Q1','1999-Q2')
    GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc);
LOG:  lt_hint_plan:
used hint:
no_star_transformation(@qb)
not used hint:
duplication hint:
error hint:

                                              QUERY PLAN                    
                           
----------------------------------------------------------------------------
---------------------------
 GroupAggregate
   Group Key: ch.channel_class, c.cust_city, t.calendar_quarter_desc
   ->  Sort
         Sort Key: ch.channel_class, c.cust_city, t.calendar_quarter_desc
         ->  Nested Loop
               ->  Nested Loop
                     ->  Hash Join
                           Hash Cond: (s.cust_id = c.cust_id)
                           ->  Seq Scan on sales s @qb
                           ->  Hash
                                 ->  Seq Scan on customers c @qb
                                       Filter: ((cust_state_province)::text 
= 'CA'::text)
                     ->  Index Scan using times_pkey on times t @qb
                           Index Cond: (time_id = s.time_id)
                           Filter: ((calendar_quarter_desc)::text = ANY ('{1
999-Q1,1999-Q2}'::text[]))
               ->  Index Scan using channels_pkey on channels ch @qb
                     Index Cond: (channel_id = s.channel_id)
                     Filter: ((channel_desc)::text = ANY ('{Internet,Catalog
}'::text[]))
(18 rows)

During direct-path INSERT operations, the database appends the inserted data after existing data in the table. Data is written directly into data files, bypassing the buffer cache. Free space in the table is not reused, and referential integrity constraints are ignored. Direct-path INSERT can perform significantly better than conventional insert.

The "append" hint instructs the optimizer to use direct-path INSERT with the subquery syntax of the INSERT statement.

LightDB does not support direct-path INSERT yet. Now "append" hint is only supported for Oracle compatibility. So if you use "append" hint, it will always be unused.

The following example shows the usage of the "append" hint:

create table test_append(key1 int, key2 int, key3 int, key4 int);
create table test_append1(key1 int, key2 int, key3 int, key4 int);

insert into test_append1 values(1,2,3,4);
insert into test_append1 values(11,21,31,41);
insert into test_append1 values(111,211,311,411);

lightdb@test_oracle_lt_hint_plan=# EXPLAIN (COSTS FALSE) insert/*+append */ into test_append select 1, 1, 1, 2 from dual;
LOG:  lt_hint_plan:
used hint:
not used hint:
append
duplication hint:
error hint:

          QUERY PLAN           
-------------------------------
 Insert on test_append @"lt#1"
   ->  Result
(2 rows)

lightdb@test_oracle_lt_hint_plan=# EXPLAIN (COSTS FALSE) insert/*+append */ into test_append select * from test_append1;
LOG:  lt_hint_plan:
used hint:
not used hint:
append
duplication hint:
error hint:

           QUERY PLAN           
--------------------------------
 Insert on test_append @"lt#1"
   ->  Seq Scan on test_append1
(2 rows)

In Oracle, OR-expansion's key feature is that the optimizer can take a single query block having OR conditions or IN-lists in the WHERE clause and transform it into a UNION ALL of 2 or more query blocks which can then be optimized and run separately.

The "no_expand" hint instructs the optimizer not to consider OR-expansion for queries having OR conditions or IN-lists in the WHERE clause.

In LightDB, OR-expansion is not supported yet. Therefore, the effect of "no_expand" hint is the default behavior. If you use "no_expand" hint, it will always work.

The following example shows the usage of the "no_expand" hint:

create table t_no_expand1(key1 int, key2 int);
create table t_no_expand2(key1 int, key2 int);

lightdb@postgres=# EXPLAIN (COSTS false) select * from t_no_expand1 where exists (select * from t_no_expand2 where key1= 1 or key2=1) or key1 =10;
                  QUERY PLAN                  
----------------------------------------------
 Seq Scan on t_no_expand1
   Filter: ($0 OR (key1 = 10))
   InitPlan 1 (returns $0)
     ->  Seq Scan on t_no_expand2
           Filter: ((key1 = 1) OR (key2 = 1))
(5 rows)

lightdb@postgres=# EXPLAIN (COSTS false) select /*+no_expand*/ * from t_no_expand1 where exists (select * from t_no_expand2 where key1= 1 or key2=1) or key1 =10;
LOG:  lt_hint_plan:
used hint:
no_expand
not used hint:
duplication hint:
error hint:

                  QUERY PLAN                  
----------------------------------------------
 Seq Scan on t_no_expand1 @"lt#0"
   Filter: ($0 OR (key1 = 10))
   InitPlan 1 (returns $0)
     ->  Seq Scan on t_no_expand2
           Filter: ((key1 = 1) OR (key2 = 1))
(5 rows)

lightdb@postgres=# EXPLAIN (COSTS false) select * from t_no_expand1 where exists (select /*+no_expand*/ * from t_no_expand2 where key1= 1 or key2=1) or key1 =10;
LOG:  lt_hint_plan:
used hint:
no_expand
not used hint:
duplication hint:
error hint:

                  QUERY PLAN                  
----------------------------------------------
 Seq Scan on t_no_expand1 @"lt#1"
   Filter: ($0 OR (key1 = 10))
   InitPlan 1 (returns $0)
     ->  Seq Scan on t_no_expand2 @"lt#0"
           Filter: ((key1 = 1) OR (key2 = 1))
(5 rows)

lightdb@postgres=# EXPLAIN (COSTS false) select /*+no_expand(@qb)*/ * from t_no_expand1 where exists (select/*+qb_name(qb)*/ * from t_no_expand2 where key1= 1 or key2=1) or key1 =10;
LOG:  lt_hint_plan:
used hint:
no_expand(@qb)
not used hint:
duplication hint:
error hint:

                  QUERY PLAN                  
----------------------------------------------
 Seq Scan on t_no_expand1 @"lt#0"
   Filter: ($0 OR (key1 = 10))
   InitPlan 1 (returns $0)
     ->  Seq Scan on t_no_expand2 @qb
           Filter: ((key1 = 1) OR (key2 = 1))
(5 rows)

The "driving_site" hint instructs the optimizer pushdown the whole query to remote server asap. especially for query involving aggregation and group by. the performance improvement will be obviously.

Limitations: 1. cannot pushdown anti join to remote server. 2. the result field must be int or varchar/text datatype, query involving other data type will degrade to traditional mechanism. 3. not support dml 4. not support with cte 5. sql must can be run on remote server, or will execute failed. 6. pushdown on statement level, not query block. set such as union/intersect/minus are pushdown as whole unit. 7. query involves unsupport feature will be stopped with error or degrade to traditional oracle_fdw behavior. the bottom is query failed, not effect the lightdb instance.

The following example shows the usage of the "driving_site" hint:

-- oracle test table
CREATE TABLE test_dblink(id int,v1 varchar2(100),v2 varchar2(100));
ALTER TABLE test_dblink ADD date1 DATE;
ALTER TABLE test_dblink ADD timestamp1 timestamp;
ALTER TABLE test_dblink ADD number1 NUMBER(10,2);
ALTER TABLE test_dblink ADD number0 NUMBER(10,0);
ALTER TABLE test_dblink ADD number_null NUMBER;
TRUNCATE TABLE test_dblink;
INSERT INTO test_dblink(id,v1,v2) SELECT LEVEL,sys_guid(),sys_guid() FROM dual CONNECT BY LEVEL < 1000001;
UPDATE test_dblink SET date1=sysdate,timestamp1=CURRENT_TIMESTAMP,number1=10.1,number0=10,number_null=0;
COMMIT;

CREATE SERVER oracle_server_fdw FOREIGN DATA WRAPPER oracle_fdw 
OPTIONS (dbserver '//10.20.30.193:1521/test');

CREATE USER MAPPING FOR lightdb server oracle_server_fdw 
OPTIONS(user 'SYSTEM', password 'oracle');

GRANT USAGE ON FOREIGN DATA WRAPPER oracle_fdw TO CURRENT_USER;
GRANT USAGE ON FOREIGN SERVER oracle_server_fdw TO CURRENT_USER;

CREATE FOREIGN TABLE test_dblink(
  id INT NULL,
  v1 varchar(100) NULL,
  v2 varchar(100) NULL,
  date1 date NULL,
  timestamp1 timestamp NULL,
  number1 number(10,2) NULL,
  number0 number(10,0) NULL,
  number_null number NULL
  ) 
  SERVER oracle_server_fdw 
  OPTIONS(SCHEMA 'SYSTEM', TABLE 'TEST_DBLINK');

-- 3 tables, oracle_fdw not support
lightdb@oradb=# select /*+ driving_site */to_char(a.date1),to_char(a.number1),to_char(count(1)) from test_dblink a, test_dblink b,test_dblink c where a.id=b.id and a.id=c.id and a.v1=b.v1 group by a.date1,a.number1
ORDER BY 1;
        to_char         | to_char | to_char 
------------------------+---------+---------
 2024-08-29 15:46:26 AD | 10.1    | 1000000
(1 row)

Time: 1645.442 ms (00:01.645)
-- 3 table not driving site
lightdb@oradb=# select /*+ driving_sit */to_char(a.date1),to_char(a.number1),to_char(count(1)) from test_dblink a, test_dblink b,test_dblink c where a.id=b.id and a.id=c.id and a.v1=b.v1 group by a.date1,a.number1
ORDER BY 1;

Time: 39174.644 ms (00:39.175)

lightdb@oradb=# explain analyze with cte as (select id,v1 from test_dblink a, dual b group by a.id,v1 order by 2,1) select /*+ driving_site */a.id,count(2)/3 || '        1' from cte a where v1 <> 'abc' group by a.id;
INFO:  lt_hint_plan: hint syntax error at or near " "
DETAIL:  driving_site hint can only be used in select without cte.


lightdb@oradb=# explain with cte as (select id,v1 from test_dblink a, dual b group by a.id,v1 order by 2,1) select /*+ driving_site */a.id,count(2)/3 || '        1' from cte a where v1 <> 'abc' group by a.id;
INFO:  lt_hint_plan: hint syntax error at or near " "
DETAIL:  driving_site hint can only be used in select without cte.
                                                                             QUERY PLAN                                                                             
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=20066.97..20072.97 rows=200 width=48)
   Group Key: a.id
   ->  Sort  (cost=20066.97..20067.47 rows=200 width=16)
         Sort Key: a.id
         ->  Subquery Scan on a @"lt#1"  (cost=20049.83..20059.33 rows=200 width=16)
               ->  Group  (cost=20049.83..20057.33 rows=200 width=48)
                     Group Key: a_1.v1, a_1.id
                     ->  Sort  (cost=20049.83..20052.33 rows=1000 width=48)
                           Sort Key: a_1.v1, a_1.id
                           ->  Foreign Scan on test_dblink a_1  (cost=10000.00..20000.00 rows=1000 width=48)
                                 Oracle query: SELECT /*b3fb4b42537fa9e0ecc6d8f132642068*/ r1."ID", r1."V1" FROM "SYSTEM"."TEST_DBLINK" r1 WHERE (r1."V1" <> 'abc')
(11 rows)


lightdb@oradb=# explain select /*+ driving_site */a.id,'2',to_char(count(1)) from test_dblink a, test_dblink b where a.id=b.id and a.v1=b.v1 group by a.id,a.v1,a.v2;
INFO:  lt_hint_plan: hint syntax error at or near ""
DETAIL:  Unrecognized hint keyword "", ignored
                                                                    QUERY PLAN                                                                     
---------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=0.00..0.00 rows=0 width=0)
   Oracle query: select /*+ driving_site */a.id,'2',to_char(count(1)) from test_dblink a, test_dblink b where a.id=b.id and a.v1=b.v1 group by a.id,a.v1,a.v2
(2 rows)

lightdb@oradb=# select /*+ driving_sie */a.id,to_char(rank() over(order by v1 desc)) from test_dblink a where a.id in (1,2,3) and exists(select 1 from test_dblink b where v1=a.v2) order by 2;
 id | to_char 
----+---------
(0 rows)

Time: 277.320 ms
lightdb@oradb=# select /*+ driving_sie */a.id,to_char(rank() over(order by v1 desc)) from test_dblink a where a.id in (1,2,3) and exists(select 1 from test_dblink b where v1=a.v1) order by 2;
 id | to_char 
----+---------
  3 | 1
  2 | 2
  1 | 3
(3 rows)

-- not pushdown
lightdb@oradb=# select a.id,to_char(rank() over(order by v1 desc)) from test_dblink a where a.id in (1,2,3) and exists(select 1 from test_dblink b where v1=a.v2) order by 2;
 id | to_char 
----+---------
(0 rows)

Time: 269.802 ms

-- group by pushdown 
lightdb@oradb=# select /*+ driving_site */to_char(2),to_char(count(1)) from test_dblink a, test_dblink b where a.id=b.id and a.v1=b.v1 group by substr(a.v1,1,2),substr(a.v2,2,2);
 to_char | to_char 
---------+---------
 2       | 1000000
(1 row)

Time: 443.039 ms

-- group by not pushdown
lightdb@oradb=# select /*+ driving_sit */to_char(2),to_char(count(1)) from test_dblink a, test_dblink b where a.id=b.id and a.v1=b.v1 group by substr(a.v1,1,2),substr(a.v2,2,2);
 to_char | to_char 
---------+---------
 2       | 1000000
(1 row)

Time: 17352.163 ms (00:17.352)

-- join with remote table
lightdb@oradb=# explain select /*+ driving_site */a.id,to_char(rank() over(order by v1 desc)) from test_dblink a where a.id in (1,2,3) and exists(select 1 from unique_t b where v1=a.v1) order by 2;
INFO:  lt_hint_plan: hint syntax error at or near ""
DETAIL:  Unrecognized hint keyword "", ignored
                                                                                          QUERY PLAN                                                                                          
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan  (cost=0.00..0.00 rows=0 width=0)
   Oracle query: select /*+ driving_site */a.id,to_char(rank() over(order by v1 desc)) from test_dblink a where a.id in (1,2,3) and exists(select 1 from unique_t b where v1=a.v1) order by 2
(2 rows)

-- join with remote table not exists
lightdb@oradb=# select /*+ driving_site */a.id,to_char(rank() over(order by v1 desc)) from test_dblink a where a.id in (1,2,3) and exists(select 1 from unique_t b where v1=a.v1) order by 2;
ERROR:  error executing query: OCIStmtExecute failed to execute remote query. file: oracle_utils.c, line: 2197
DETAIL:  ORA-00942: table or view does not exist

-- oracle has table t
lightdb@oradb=# select /*+ driving_site */to_char(a.int1),to_char(rank() over(order by v1 desc)) from test_dblink a where a.id in (1,2,3) and not exists(select 1 from t b where v1=a.v1) order by 2;
 to_char | to_char 
---------+---------
(0 rows)

-- lightdb has table t
lightdb@oradb=# select /*+ driving_sit */to_char(a.int1),to_char(rank() over(order by v1 desc)) from test_dblink a where a.id in (1,2,3) and not exists(select 1 from t b where v1=a.v1) order by 2;
 to_char | to_char 
---------+---------
         | 1
         | 2
         | 3
(3 rows)