YugabyteDB中的pg_hint_plan不考虑HashJoin (a，b)，仍然支持NL

Question

[用户在YugabyteDB Community Slack上发布的问题]

我有这个模式，但我无法使用pg_hint_plan来强制HashJoin(a，b)：

yugabyte=# create table a(id int);
yugabyte=# create table b(id int);
yugabyte=# create table c(id int);

yugabyte=# explain select * from a natural join b natural join c;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Merge Join  (cost=449.49..909.49 rows=25000 width=4)
   Merge Cond: (c.id = a.id)
   ->  Sort  (cost=149.83..152.33 rows=1000 width=4)
         Sort Key: c.id
         ->  Seq Scan on c  (cost=0.00..100.00 rows=1000 width=4)
   ->  Materialize  (cost=299.66..392.16 rows=5000 width=8)
         ->  Merge Join  (cost=299.66..379.66 rows=5000 width=8)
               Merge Cond: (a.id = b.id)
               ->  Sort  (cost=149.83..152.33 rows=1000 width=4)
                     Sort Key: a.id
                     ->  Seq Scan on a  (cost=0.00..100.00 rows=1000 width=4)
               ->  Sort  (cost=149.83..152.33 rows=1000 width=4)
                     Sort Key: b.id
                     ->  Seq Scan on b  (cost=0.00..100.00 rows=1000 width=4)
(14 rows)

Answer 1

请注意，当您想要提示特定的计划时，您不仅需要定义连接方法(如HashJoin)，还需要定义连接顺序(使用前导)和方向(使用括号)。示例：

yugabyte=# -- probe a, join to hashed b, join the result to hashed c:
yugabyte=# explain /*+ Leading(((a b) c)) HashJoin(a b)  HashJoin(a b c) */
yugabyte-#  select * from a natural join b natural join c;
                               QUERY PLAN                               
------------------------------------------------------------------------
 Hash Join  (cost=225.00..1390.00 rows=25000 width=4)
   Hash Cond: (a.id = c.id)
   ->  Hash Join  (cost=112.50..390.00 rows=5000 width=8)
         Hash Cond: (a.id = b.id)
         ->  Seq Scan on a  (cost=0.00..100.00 rows=1000 width=4)
         ->  Hash  (cost=100.00..100.00 rows=1000 width=4)
               ->  Seq Scan on b  (cost=0.00..100.00 rows=1000 width=4)
   ->  Hash  (cost=100.00..100.00 rows=1000 width=4)
         ->  Seq Scan on c  (cost=0.00..100.00 rows=1000 width=4)
(9 rows)



yugabyte=# -- probe b, join to hashed c, probe a and join to the previous result:
yugabyte=# explain /*+ Leading((a (b c))) HashJoin(b c) HashJoin(a b c) */
yugabyte-#  select * from a natural join b natural join c;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Hash Join  (cost=452.50..1430.00 rows=25000 width=4)
   Hash Cond: (a.id = b.id)
   ->  Seq Scan on a  (cost=0.00..100.00 rows=1000 width=4)
   ->  Hash  (cost=390.00..390.00 rows=5000 width=8)
         ->  Hash Join  (cost=112.50..390.00 rows=5000 width=8)
               Hash Cond: (b.id = c.id)
               ->  Seq Scan on b  (cost=0.00..100.00 rows=1000 width=4)
               ->  Hash  (cost=100.00..100.00 rows=1000 width=4)
                     ->  Seq Scan on c  (cost=0.00..100.00 rows=1000 width=4)
(9 rows)





yugabyte=# -- probe c, join to hashed b, probe a and join to the previous result:
yugabyte=# explain /*+ Leading((a (c b))) HashJoin(c b) HashJoin(a b c) */
yugabyte-#  select * from a natural join b natural join c;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Hash Join  (cost=452.50..1430.00 rows=25000 width=4)
   Hash Cond: (a.id = b.id)
   ->  Seq Scan on a  (cost=0.00..100.00 rows=1000 width=4)
   ->  Hash  (cost=390.00..390.00 rows=5000 width=8)
         ->  Hash Join  (cost=112.50..390.00 rows=5000 width=8)
               Hash Cond: (c.id = b.id)
               ->  Seq Scan on c  (cost=0.00..100.00 rows=1000 width=4)
               ->  Hash  (cost=100.00..100.00 rows=1000 width=4)
                     ->  Seq Scan on b  (cost=0.00..100.00 rows=1000 width=4)
(9 rows)

在YugabyteDB 2.9上，您可以分析表，查询规划器将使用统计信息进行估计。