lightdb-no_push_subq/push_subq

LightDB 从 23.4 版本开始支持no_push_subq/push_subq hint，在 LightDB 中下推过滤条件是RBO，会尽量下推过滤条件以过滤尽可能多的数据。通过使用这个 no_push_subq hint 可以强制不下推带子链接的过滤条件（带子链接的过滤条件可能效率较差）。使用push_subq hint 可以强制下推带子链接的过滤条件（LIghtDB的默认操作），一般不需使用。

需要注意的是，对于被pull up 后的子链接不起效（因为已经不是过滤条件了）。

下面是使用示例：

示例

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

在push_subq 与no_push_subq 一起使用时，如果push_subq 与no_push_subq 不是作用于同一个子链接，则不会冲突，但在此情况下，可能是作用于同一个过滤条件， 此时push_subq起效。

如下所示 no_push_subq 不起效：

lightdb@postgres=# EXPLAIN (COSTS false)                                           select * from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype
where ((select/*+no_push_subq*/ relname from test_no_push_subq2 c where c.oid=a.oid), (select/*+push_subq*/ reltype from test_no_push_subq2  c1 where c1.oid=a.oid)) = (select/*+no_push_subq*/ relname, reltype from test_no_push_subq4 c);
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_no_push_subq4 c_1 @"lt#0"
   ->  Seq Scan on test_no_push_subq1 a @"lt#3"
         Filter: ((SubPlan 2) = $3)
         SubPlan 2
           ->  Seq Scan on test_no_push_subq2 c1 @"lt#2"
                 Filter: (oid = a.oid)
   ->  Hash
         ->  Seq Scan on test_no_push_subq3 b @"lt#3"
   SubPlan 1
     ->  Seq Scan on test_no_push_subq2 c @"lt#1"
           Filter: (oid = a.oid)
(15 rows)

lightdb@postgres=#
lightdb@postgres=# EXPLAIN (COSTS false)
select * from test_no_push_subq1 a join test_no_push_subq3 b on a.reltype =b.reltype
where ((select/*+no_push_subq*/ relname from test_no_push_subq2 c where c.oid=a.oid), (select/*+push_subq*/ reltype from test_no_push_subq2  c1 where c1.oid=a.oid)) = (select/*+push_subq*/ relname, reltype from test_no_push_subq4 c);
LOG:  lt_hint_plan:
used hint:
push_subq
push_subq
not used hint:
no_push_subq
duplication hint:
error hint:

                         QUERY PLAN                          
-------------------------------------------------------------
 Nested Loop
   Join Filter: (a.reltype = b.reltype)
   InitPlan 3 (returns $2,$3)
     ->  Seq Scan on test_no_push_subq4 c_1 @"lt#0"
   ->  Seq Scan on test_no_push_subq1 a @"lt#3"
         Filter: (((SubPlan 1) = $2) AND ((SubPlan 2) = $3))
         SubPlan 1
           ->  Seq Scan on test_no_push_subq2 c @"lt#1"
                 Filter: (oid = a.oid)
         SubPlan 2
           ->  Seq Scan on test_no_push_subq2 c1 @"lt#2"
                 Filter: (oid = a.oid)
   ->  Seq Scan on test_no_push_subq3 b @"lt#3"
(13 rows)